Duplicates in a table-Help needed

[NaveenNandigam]

Hi Everyone, I need help in selection of primary key and designing of a tables. I am having a table called Current trailers with Trailer number as a primary key. I have similar table to current trailers called history table in which the trailers that left the yard are stored. As a trailer can enter and exit the yard couple of times in a week, its not letting me to have duplicates in the history table.So can anyone help in selection of primary key for the history table. Or can anyone tell me how I can have duplicates in a table.

Thanks in Advance,
Naveen

 

[FoFa]

I would use an autonumber for your primary key in your History table. You could setup a complexe key, made up of trailer, date, in/out (but you may have to track time as well), but usually for a history table you don't need to go this length. You could still use trailed as an index with duplicates allowed, but I would not recommend it in this case.

 

[neileg]

I would rethink the design. You need a table for all trailers and a table for history. You can either use a flag in the trailers table to define if a trailer is in or out or derive this from information in the history table.

As FoFa suggests, the history table should not use the trailer ID as a PK, use an autonumber. You will hold the trailer ID as a field in the history table to link the tables but as you have a one to many relationship this must contain duplicates.