CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
Daryl Behrens
Daryl Behrens

Reputation: 643

Linking Database Tables Standard Practice

I am looking for the standard way to handle the following Database Situation.

Two Database Tables - One called Part, one called Return. In Part we have information about Part Number, Cost, Received Date, etc.

Return is for if that part is being returned to the vendor. It will have Return Tracking Number, Shipped Date, and If Credited.

A Part can only have one Return but may have none if Part is not returned to vendor.

The 3 options I see are:

  1. Put both Part and Return in the same Table but I do not like this idea, table will get too large.

  2. Create a field in the 'Part' Table to reference the Id of the Return record that it is related to. My Concern here is there could possibly be free floating Return records not attached to a Part

  3. Create a field in the Return Table to reference the Id of the Part record it is related to, making the PartId field unique so I cannot duplicate Part Id.

Is there any advantage or disadvantage to using #2 or #3 (or I guess #1 if that is a viable option)?

UPDATE:

I should have mentioned in reality these tables will be much bigger, and in the application I will be viewing Returns and Parts information in seperate views.

Upvotes: 0

Views: 58

Answers (2)

Gervs
Gervs

Reputation: 1397

solution 3: with the exception that you do not need a unique constraint on part_id, just make it the PK (which is almost the same)

Upvotes: 2

Cratylus
Cratylus

Reputation: 54094

Basically you have 2 entities, Part and Number with 1-1 relationship where one entity is optional.
In this case you should create a table for each entity (i.e. 2 tables) and use the PK of Part as a reference in the Return table. That is the standard way to represent relationships of this kind.

Upvotes: 3

Related Questions