The databases described and used so far are relational because they are based on relations among the tables. The foundation of a relational database system is to break the data into multiple tables that are related by common information (keys).
Suppose you are designing a database for storing invoices. This database should have a table with customer names, product names, and prices and, of course, a table for the invoices. Each invoice that is issued to a customer contains a number of products. Instead of storing product names and customer names in the invoice, you store numbers that uniquely identify the customers and the products. These numbers are the keys that connect rows in one table to rows in another table. The keys establish relationships among the tables and make it possible to break the information into
separate tables and avoid duplicating information.
Lets look at a few basic concepts of the,relational model, and then we’ll develop some applications to demonstrate these principles.
The Primary Key
In a relational database system, each record has a unique identifier that is used to indicate the record and to relate it to other records in other. tables. Often, a dose inspection of your data will reveal an existing characteristic that makes each record unique; frequently, this can become the primary key. This type of primary key is called composite. For example, in an employees database, an employee’s Social Security number is a composite primary key.
When there is not an apparent field or set of fields that can be used to form a composite primary key, Visual Basic’s database management system (the JET engine, as it’s known) can automatically generate a unique numeric key for each record. This type of key is made by adding a field to your table and setting it to the AutoNumber field type. An AutoNumber field automatically increases by increments of one whenever a new record is added. This ensures that the key is unique, although the key may not necessarily mean anything. As long as all tables refer to the same record with the same key, the key need not be meaningful. Sometimes an auto-numbered primary key can be made to fit a business purpose, for example, using an auto-numbered field for an invoice number.
Regardless of the type of primary key, it is wise to always make the primary key. of a table a field of the type Long (a standard Visual Basic data type, as explained in Chapter 3) By doing so, you greatly simplify design of other tables.
Foreign Key
A field (column) in a table that stores a value and relates the value to another table is called a foreign key. For example, a field in an Invoices table that stores the customer number is a foreign key. The same value in the Customers’ table is the primary key. A foreign key should be of the same type as the primary key of the table to which it is relating.
Indices
Indices are structures that determine the order of the records in a table. Normally, data aren’t maintained in any special order in the table. In most practical situations, though, you want to access them in a specific order. When you print a list of customers’s names, you want them in alphabetic order. If you print mailing labels, you probably want the labels in ZIP code order. In general, the type of processing you want to perform determines the order in which a table’s rows should be furnished, and it is common for a table to be furnished in different orders for different operations.
Rearranging the rows of a table each time the application needs them in a different order is out of the question. This would take too much time. The solution is to maintain small tables, Called indices, that dictate the order in which records will be read . from the table. The index file doesn’t contain any of the information that appears in the table itself. It contains only the numbers that determine the order of the records.
Suppose that the fifth record in a table should appear first when the table’s rows are requested in alphabetic order. The first element of the index file contains the value 5, so when the database supplies the rows of the specific table, it retrieves the fifth element first.
A table can have more than a single index, The indices of all table,s in a database are maintained by the JET engine, and you need not do anything more-than specify the fields on which the index will be based.
