Compare these two versions of the INVOICEITEM table
| INVOICEITEM | INVOICEITEM | |
|---|---|---|
| LineNo | InvoiceNo | |
| InvoiceNo | ItemNo | |
| ItemNo | Quantity | |
| Quantity |
1. What is the difference between them?
The difference between the two versions of the tables is that they have different Primary Keys.
2. What business rules does each of the two versions follow?
The first version of the INVOICEITEM table follows the business rule: An item can be added to an invoice as many times as needed. For example, 1200-2252 CoolRoom can appear on the one invoice 5 times.
The second version of the INVOICEITEM table tells us that an item can only appear once per invoice. Hence the composite primary key.
These are some of the issues are something a database designer must keep in mind all the time.
Which rule and version should be used will depend upon the system being modelled. What is important, is to recognise that the two versions represent different systems and the impact your design decisions will have.
These points will be answered in Gathering Information About the System.