CODEWITHSUNDEEP
databasedatabase-designrelational-databasedatabase-schema
Czar Luc
Czar Luc

Reputation: 95

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

Upvotes: 0

Views: 205

Answers (2)

Emiliano Javier González
Emiliano Javier González

Reputation: 190

It depends. If you want a Shipment to have many ShipmentDetails then no, because you're going to repeat ShipmentDetailId in the Shipment table for every child it has, an thus violating 1FN.

Simple Select to see the potential results

If you have a "1 to 1" relationship, having two tables might not be necessary because you can have everything in just the Shipment table.

I've seen tables that became too large in columns amount let's say, but I've never seen a performance boost for separating the columns in different tables.

Hope this helps!

Upvotes: 1

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

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

Upvotes: 1

Related Questions