Database Reference
In-Depth Information
As you can see when you examine the type of sales information needed for the report, there are a
few repeating items (fields) — for example, Product Purchased, Quantity Purchased, and Price of
Item. Each invoice can have multiple items, and each of these items needs the same type of informa-
tion — number ordered and price per item. Many sales have more than one purchased item. Also,
each invoice may include partial payments, and it's possible that this payment information will have
multiple lines of payment information, so these repeating items can be put into their own grouping.
This type of report leads you to create two tables: one table to hold the top-level invoice data such as
invoice number, invoice data, and sales person; and another table to hold line item details such as the
products purchased, quantity purchased, and purchase price.
Step 4: Table design
After determining the tables needed, you evaluate the fields and calculations that are needed to ful-
fill the reporting requirements. Initially, only the fields included in the reports are added to the tables.
Other fields may be added later (for various reasons), although certain fields won't appear in any
table.
It's important to understand that not every little bit of data must be added into the database's tables.
For example, clients may want to add vacation and other out-of-office days to the database to deter-
mine which employees are available on a particular day. However, it's easy to burden a database's ini-
tial design by incorporating too many ideas during the initial development phases. In general, you
can accommodate client requests after the database development project is underway.
After all the tables and fields are determined, database designers consolidate the data by purpose
(for example, grouped into logical groups) and then compare the data across those functions. For
example, customer information is combined into a single set of data items. The same action is taken
for sales information and line-item information. Table 1-3 compares data items from these three
groups of information.
Table 1-3:
Comparing the Data Items
Customer Data
Invoice Data
Line Items
Customer Company Name
Invoice Number
Product Purchased
Street
Sales Date
Quantity Purchased
City
Invoice Date
Description of Item Purchased
State
Payment Method
Price of Item
Zip Code
Discount for Each Item
Phone Numbers (two fields)
Discount (overall for this sale)
Taxable?
E-Mail Address
Tax Rate
Web Site
Payment Type (multiple lines)
Payment Date (multiple lines)
Discount Rate
Payment Amount (multiple lines)
Customer Since
Credit Card Number (multiple lines)
continued
Search WWH ::
Custom Search