Database Reference
In-Depth Information
The relationships between tables can be different. For example, each sales invoice has one and only
one customer, while each customer may have multiple sales. A similar relationship exists between
the sales invoice and the line items of the invoice.
Database table relationships require a unique field in both tables involved in a relationship. A unique
identifier in each table helps the database engine to properly join and extract related data.
Only the sales table has a unique identifier (InvoiceNumber), which means at least one field must be
added to each of the other tables to serve as the link to other tables; for example, adding a CustomerID
field to tblCustomers, adding the same field to the invoice table, and establishing a relationship between
the tables through CustomerID in each table. The database engine uses the relationship between cus-
tomers and invoices to connect customers with their invoices. Relationships between tables is done
through key fields. Chapter 3 shows you how to create relationships between key fields in tables.
When you understand the need for linking one group of fields to another group, you can add the
required key fields to each group. Table 1-4 shows two new groups and link fields created for each
group of fields. These linking fields, known as
primary
and
foreign
keys, are used to link these tables.
Table 1-4:
Tables with Keys
Customer Data
Invoice Data
Line Items Data
Sales Payment Data
CustomerID
InvoiceID
InvoiceID
InvoiceID
Customer Name
CustomerID
Line Number
Payment Type
Street
Invoice Number
Product Purchased
Payment Date
City
Sales Date
Quantity Purchased
Payment Amount
State
Invoice Date
Description of Item Purchased
Credit Card Number
ZIP Code
Payment Method
Price of Item
Expiration Date
Phone Numbers (two fields)
Salesperson
Discount for Each Item
E-Mail Address
Web Site
Discount Rate
Customer Since
Last Sales Date
Sales Tax Rate
Tax Rate
The field that uniquely identifies each row in a table is the primary key. The corresponding field in a
related table is the foreign key. In this example, CustomerID in tblCustomers is a primary key, while
CustomerID in tblInvoices is a foreign key.
Assume a certain record in tblCustomers has 12 in its CustomerID field. Any records in Invoices with
12 as its CustomerID is “owned” by customer 12.
Search WWH ::
Custom Search