Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
VThou
Starting Member
4 Posts |
 Posted - 2009-11-21 : 03:07:19
|
| All, Apologies for the super-basic Q, but am still learning and hopefully I have asked this in the right area!Using a dummy example in support of my question, I have two tables:InvoiceHolds standard information about an Invoice, however also needs to include the license plate number of a car used in delivering the goods the invoice is for.CarRegistration, Make, Model, license plate number etc.Now the two tables are related through the cars license plate number.The Car table is used as a reference table as doesnt change much (primarily read), however the Invoice table is updated constantly.Now in linking the two table so that the Invoice table records the license plate number of the car used, do I use a column in the Invoice table holding the cars license plate number or do I instead use the Primary Key (Index auto increment feild)of the Car table? The reason I am questioning is that:1. If I don't want the history of the Invoice to change, then I should use the actual registration of the car as if I use the Car tables Primary Key then a change to the Cars details in the Car table (say the car was registered in another state and therefore recieved a new license plate) will result in the Invoice data reflecting a different license plate number. So any DB stored invoices would now not match an invoice that was delivered to the customer before the change.2. If I don't need any other details of the car, then why use the Primary Key and just use the car license plates value instead?Hopefully my question makes sense, just wondering if the answer is to still include the Primary Key of the car table in the Invoice table, but also include the license plate number value from the Car table as well so i have its history (license plate number at time of creation) and current (through the Primary Key) information? |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-21 : 10:11:59
|
| You should use the primary key of car in the invoice table.Either way, you shouldn't allow updates to the license plate field of the car table. Instead, add a new record if the lic. plate changes. Add a bit column to indicate active/inactive records in car.This way you won't break your foreign key and you won't risk changing history.Another way to do it would be to add a new table with the InvoiceID and all of the car information. This table would take inserts but never updates/deletes. |
 |
|
|
|
|
|
|
|
Table Relationship Q (basic)