|
|
There are a lot of primary key, primary key/foreign key pair, and duplicate data examples in this set of tables. It shows how quickly these can get complex and interrelated.
Primary Keys
Table: | INVENTORY |
Key: | PART_NUMBER |
Rules: | Unique, not null, and not blank |
Table: | INVENTORY |
Natural Key: | DESCRIPTION, TYPE |
Rules: | Unique, not null, and not blank |
Table: | INVENTORY_HISTORY |
Key: | PART_NUMBER, YEAR, QUARTER |
Rules: | Unique, not null, and not blank |
Table: | SUPPLIER |
Key: | SUPPLIER_ID |
Rules: | Unique, not null, and not blank |
Table: | SUPPLIER |
Natural Key: | COMPANY_NAME, COMPANY_ADDRESS, CITY, STATE |
Rules: | Unique, not null, and not blank |
Table: | SOURCE |
Key: | PART_NUMBER, SUPPLIER_ID |
Rules: | Unique, not null, and not blank |
Table: | PURCHASE_ORDER |
Key: | PO_NUMBER |
Rules: | Unique, not null, and not blank |
Primary Key/Foreign Key Pairs
Primary Table: | INVENTORY |
Column: | PART_NUMBER |
Secondary Table: | INVENTORY_HISTORY |
Column: | PART_NUMBER |
Characteristic: | ONE-TO-MANY NOT INCLUSIVE |
Note: | There may be no history records if the part is new in the current quarter. |
Primary Table: | ENGINEERING_DRAWINGS |
Column: | ENG_DRAWING_NUMBER |
Secondary Table: | INVENTORY |
Column: | ENG_DRAWING_NUMBER |
Characteristic: | ONE-TO-ONE NOT INCLUSIVE |
Note: | This value may be blank in the INVENTORY table. It is used only if the part has a drawing number. All inventory rows of type I for internal must have a drawing number. |
Primary Table: | INVENTORY |
Column: | PART_NUMBER |
Secondary Table: | SOURCE |
Column: | PART_NUMBER |
Characteristic: | ONE-TO-MANY INCLUSIVE |
Note: | All inventory parts must have at least one source. They may have many supplier sources. |
Primary Table: | INVENTORY |
Column: | PART_NUMBER |
Secondary Table: | PURCHASE_ORDER |
Column: | PART_NUMBER |
Characteristic: | ONE-TO-MANY NOT INCLUSIVE |
Note: | A part may not have any orders for it. This is not unusual for a part that is new in inventory. |
Primary Table: | SUPPLIER |
Column: | SUPPLIER_ID |
Secondary Table: | SOURCE |
Column: | SUPPLIER_ID |
Characteristic: | ONE-TO-MANY INCLUSIVE |
Note: | All suppliers must supply at least one part. They may supply many parts. |
Primary Table: | SUPPLIER |
Column: | SUPPLIER_ID |
Secondary Table: | PURCHASE_ORDER |
Column: | SUPPLIER_ID |
Characteristic: | ONE-TO-MANY NOT INCLUSIVE |
Note: | A supplier may have no orders. This would be true for a newly established supplier or one who is a low-priority source for a part. |
Primary Table: | PURCHASE_ORDER |
Column: | PO_NUMBER |
Secondary Table: | SUPPLIER |
Column: | LAST_ORDER_NUMBER |
Characteristic: | ONE-TO-MANY NOT INCLUSIVE |
Note: | A supplier may have no orders. |
Duplicate Data
There are three duplicate columns in the set of tables. All of them occur in the PURCHASE_ORDER table. Duplication is done for the purpose of making access to the purchase order information of higher performance without requiring joining information from the other tables.
Primary Table: | INVENTORY |
Column: | DESCRIPTION |
Secondary Table: | PURCHASE_ORDER |
Column: | DESCRIPTION |
Primary Table: | INVENTORY |
Column: | UNIT_OF_MEASURE |
Secondary Table: | PURCHASE_ORDER |
Column: | UNIT_OF_MEASURE |
Primary Table: | SOURCE |
Column: | SUPPLIER_PART_NUMBER |
Secondary Table: | PURCHASE_ORDER |
Column: | SUPPLIER_PART_NUMBER |
|
|