Mike Yearwood
Toronto, Ontario, Canada
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Conception bases de données
Versions des environnements
SQL Server:
SQL Server 2000
There are a number of different opionions and that's all you'll find. I think it becomes quite easy if you think about it a certain way.
Every product in a store has a bar code. The barcode is meaningless to the average person, yet it represents the product to the computer system.
If you do likewise, there will be a barcode on every record in your table. Relating records in one table to a particular record becomes quite easy.
If you have
Invoice_ID i(4) in the invoice table and Invoice Number c(10) and Line_ID i(4), Invoice_ID i(4) in the line items table.
You can change the invoice number and there's no cascade.
If you want to add a third table, you need only a 4 byte relating key in the third table.
If you have Invoice_Number c(10) in the invoice table and Invoice_Number c(10) + Line_Number c(10) as the relating field in the line items, you now need to cascade the invoice number down into the line items table, if/when the invoice number changes. This involves potentially difficult substring operations.
To relate a third table to the line item, you have to carry a 20 byte key to that third table. The keys get longer the further down you go. I also see this as producing redundant data which is supposed to be avoided as a data modelling rule of thumb.
I also use GUID instead of integer since I don't have to coordinate getting the ID from the backend or some central table. It's sixteen bytes, but it still beats the 20 byte key I mentioned above and it never goes beyond 16 bytes.
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement