Database Reference
In-Depth Information
Table 1-3: Comparing the Data Items (continued)
Customer Data
Invoice Data
Line Items
Last Sales Date
Expiration Date (multiple lines)
Sales Tax Rate
Credit Information (four fields)
Consolidating and comparing data is a good way to start creating the individual table, but the cus-
tomer data must be split into two groups. Some of these items are used only once for each customer,
while other items have multiple entries. For example, in the Sales column, the payment information
can have multiple lines of information.
For example, one customer can have multiple contacts with the company. Another customer may
make multiple payments toward a single sale. Of course, for this example, the data goes into three
categories: customers, invoices, and sales line items.
Keep in mind that one customer may have multiple invoices, and each invoice may have multiple line
items on it. The invoice category contains information about individual sales and the line items cate-
gory contains information about each invoice. Notice that these three columns are all related; for
example, one customer can have multiple invoices and each invoice may require multiple detail lines
(line items).
Why multiple tables?
The prospect of creating multiple tables almost always intimidates beginning database users. Most
often, beginners want to create one huge table that contains all the information they need — for
example, a customer table with all the sales placed by the customer and the customer's name,
address, and other information. After all, if you've been using Excel to store data so far, it may seem
quite reasonable to take the same approach when building tables in a database.
A single large table for all customer information quickly becomes difficult to maintain, however. You
have to input the customer information for every sale a customer makes (repeating the name and
address information in every row). The same is true for the items purchased for each sale when the
customer has purchased multiple items as part of a single purchase. This makes the system more
inefficient and prone to data-entry mistakes. The information in the table is inefficiently stored — 
certain fields may not be needed for each sales record — and the table ends up with a lot of empty
fields.
You want to create tables that hold the minimum of information while still making the system easy
to use and flexible enough to grow. To accomplish this, you need to consider making more than one
table, with each table containing fields that are only related to the focus of that table. Then, after you
create the tables, you link them so that you're able to glean useful information from them. Although
this process sounds complex, the actual implementation is relatively easy.
 
Search WWH ::




Custom Search