The most basic object in a database is the table in
which you store your data. You might be tempted to jam every type of
data you want to store into a single table, but that’s hardly ever the
right way to design tables in a database. The following guidelines will
help you create efficient tables.
1. One Table per Object
The first rule in creating
database tables is to ensure that every table stores data about one type
of object, whether that object is a person, a product, or an order. As
an example, consider the Suppliers table from the Northwind sample
database.
This table has a field for
everything you want to know about a supplier, with nothing extra.
Consider this alternative design, which adds fields to describe the
supplier’s products.
Aside from repeated data,
deleting the record representing the last product from a supplier
removes all information about that supplier from your database. Rather
than risk losing that information, it is much more efficient to create
one table for the suppliers and another for the products.
2. Give Every Table a Primary Key
Another important consideration in creating a table is to assign a primary key.
This field contains a value that sets a record apart from all other
records in the table. In the Northwind database’s Shippers table, that
role is filled by the ID field.
It’s also possible to create a
primary key made up of more than one field, as in the table shown in
the following illustration. (Please note that this table is just a
sample; it isn’t in the Northwind database.)
The ProductID field
identifies the product and the SupplierID field identifies the product’s
supplier. Because you can order the same product from more than one
supplier, both the ProductID and SupplierID fields are needed to
distinguish a record from all other records in the table.
3. Include Foreign Keys
A final thing you can do to make
your tables more efficient is to include primary key fields from other
tables, as with the SupplierIDs field in the Products table.
When a primary key from one table is stored in another table, it is called a foreign key. You can use foreign keys to create relationships between tables.
