Specifications

The relational database term for this relationship is foreign key. CustomerID is the primary
key in Customers, but when it appears in another table, such as Orders, it is referred to as a
foreign key.
You might wonder why we chose to have two separate tables—why not just store Julie’s
address in the Orders table? We’ll explore this in more detail in the next section.
Schemas
The complete set of the table designs for a database is called the database schema. It is akin to
a blueprint for the database. A schema should show the tables along with their columns, the
data types of the columns and indicate the primary key of each table and any foreign keys. A
schema does not include any data, but you might want to show sample data with your schema
to explain what it is for. The schema can be shown as it is in the diagrams we are using, in
entity relationship diagrams (which are not covered in this book), or in a text form, such as
Customers(CustomerID, Name, Address, City)
Orders(OrderID, CustomerID, Amount, Date)
Underlined terms in the schema are primary keys in the relation in which they are underlined.
Dotted underlined terms are foreign keys in the relation in which they appear with a dotted
underline.
Relationships
Foreign keys represent a relationship between data in two tables. For example, the link from
Orders to Customers represents a relationship between a row in the Orders table and a row in
the Customers table.
Three basic kinds of relationships exist in a relational database. They are classified according
to the number of things on each side of the relationship. Relationships can be either one-to-
one, one-to-many, or many-to-many.
A one-to-one relationship means that there is one of each thing in the relationship. For exam-
ple, if we had put addresses in a separate table from Customers, there would be a one-to-one
relationship between them. You could have a foreign key from Addresses to Customer or the
other way around (both are not required).
In a one-to-many relationship, one row in one table is linked to many rows in another table. In
this example, one Customer might place many Orders. In these relationships, the table that
contains the many rows will have a foreign key to the table with the one row. Here, we have
put the CustomerID into the Order table to show the relationship.
In a many-to-many relationship, many rows in one table are linked to many rows in another table.
For example, if we had two tables,
Books and Authors, you might find that one book had been
Designing Your Web Database
C
HAPTER 7
7
DESIGNING YOUR
WEB DATABASE
175
10 7842 CH07 3/6/01 3:34 PM Page 175