[Henrys dad]

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.

[heybales]

Create a notes table with three columns.

• FK ContainerID
  
• FK DeliveryID
  
• Note

A given record can reference a container, a delivery, or both.

[Henrys dad]

heybales, on 25 November 2012 - 01:02 PM, said:

Create a notes table with three columns.

• FK ContainerID
  
• FK DeliveryID
  
• Note

A given record can reference a container, a delivery, or both.

[Henrys dad]

Sorry not been on for ages, so thanks for your reply.
The notes table has an ID, Note and Description (either shipment or devan) field.
At the moment they are still in the containers and deliveries tables as the data model has not been changed yet (it's going to be ported over from Access),
Will give it a whirl using two PK's and see how it goes.
Thanks again for taking the time to reply.