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 |