Database Reference
In-Depth Information
■
When data is repeated (that is, the vendor information is used with
each product), there is a high likelihood that the data will not be
entered exactly the same way each time. Inconsistent data is extremely
difficult to use in reporting.
The key here is that having multiple occurrences of the same data is never
a good thing, and that principle is the basis for relational database design.
Relational tables are designed so information is split into multiple tables, one
for each data type. The tables are related to each other through common values
(and thus the
relational
in relational design).
In our example, you can create two tables, one for vendor information and one
for product information. The
vendors
table contains all the vendor informa-
tion, one table row per vendor, along with a unique identifier for each vendor.
This value, called a
primary key
, can be a vendor ID, or any other unique value.
(Primary keys were first mentioned in Chapter 1, “Understanding SQL”).
The
products
table stores only product information, and no vendor specific
information other than the vendor ID (the
vendors
table's primary key). This
key, called a
foreign key
, relates the
vendors
table to the
products
table, and
using this vendor ID enables you to use the
vendors
table to find the details
about the appropriate vendor.
New Term
Foreign key A column in one table that contains the primary key values from another
table, thus defining the relationships between tables.
What does this do for you? Well, consider the following:
■
Vendor information is never repeated, and so time and space are not
wasted.
■
If vendor information changes, you can update a single record in the
vendors
table. Data in related tables does not change.
■
As no data is repeated, the data used is obviously consistent, making
data reporting and manipulation much simpler.
The bottom line is that relational data can be stored efficiently and manipulated
easily. Because of this, relational databases scale far better than nonrelational
databases.
New Term
Scale Able to handle an increasing load without failing. A well-designed database or
application is said to
scale well.
