Posted 24 November 2012 - 06:15 AM
Need advice on setting up a relationship between two tables:
I have a database consignments, that consists of:-
Table 1 = Containers
Table 2 = Deliveries plus several lookup tables
Containers can only be delivered once, so this is a one to one relationship, but both tables need to have a field for notes/comments.
At the moment the comments are in both tables in access db ie container_notes, & delivery_notes, to port this db to mysql I want to split the comments to a notes table.
The consignment consists of a container table and a delivery table, but the container notes do not always have a matching delivery note, for example the container may go on customs hold, but there are no delivery issues, or the truck may breakdown during delivery, but the container has no issues and is therefore blank.
We can therefore have for example 1000 container/dlvy notes and 800 dlvy/container notes.
If this were 1 to 1 then that is a lot of wasted space because up to now we have over 3,000 records.
So what would the most appropriate relationship be 1 to many or junction table (many to many) between both tables or put the actual notes in one table and make it 1 to 1 and live with the waste of space( neither efficient or desirable).
If any one can point me in the right direction it would be much appreciated, so thanks in advance.