Search code examples
databasedatabase-designrelational-databasedatabase-schema

Database Modelling, Structures, and Relationships

Currently I'm designing a database model that would serve as a database for an inventory system. However, I'm facing difficulties in the database structures particularly on whether I would add an extra column for the parent table for it's child ID.

Presented in the figure below is a part of the database that I'm currently working on. I'm working on the Normalization forms that's why it already has multiple tables. However, in the Shipment Table (highlighted in blue) in this case the parent table of Shipment Details (highlighted in red) I don't know if I should include an extra column for the shipment table for it to identify or connect to the Shipment Detail table.

I already included a foreign key for the Shipment Detail for it to identify which Shipment it relates to I just don't know if I should add another ID column for the details in the Shipment table. To be more specific, should I add a "Shipment Detail ID" column for the Shipment table? I worry it might result to redundancies if I include it.

enter image description here

Solution

  • If each shipment has exactly one shipment detail

    shipment {SHIPMENT_ID, ...}
      PK {SHIPMENT_ID}


shipment_detail {SHIPMENT_ID, ...}
             PK {SHIPMENT_ID}

             FK {SHIPMENT_ID} REFERENCES
       shipment {SHIPMENT_ID}

    If each shipment can have more than one shipment detail

    shipment {SHIPMENT_ID, ...}
      PK {SHIPMENT_ID}


shipment_detail {SHIPMENT_ID, SHP_DET_NO, ... }
             PK {SHIPMENT_ID, SHP_DET_NO}

             FK {SHIPMENT_ID} REFERENCES
       shipment {SHIPMENT_ID}

    Notes:

    All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key (Unique)
FK = Foreign Key