lieka
asked on
"Real" data as primary key.
I would very much like to hear someones opinon on the use primary keys. Is it common (considered good practice) to include a column of type "identity" as a primary key even though there is "real" data in the table that could serve as primary key, ie. an invoice number ?
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
hi lieka,
It depends on the design in my case i do to join the two tables.
It depends on the design in my case i do to join the two tables.
Generally no. In the event I am pulling an invoice item and I need the invoice number, I join to the invoice table to get it. I only want my invoice number stored 1 time in the database, and that is in the invoice table. The invoice item table only has a reference to the invoice_id. The problems with also storing the invoice number in the invoice items table are:
1. Redundant Data (think big! If you have an invoice with 10,000 items on it, you are storeing the invoice number 10,001 times.)
2. Data consistancy. Always rely on the primary key. What if someone changes the invoice number, but forgets to update the invoice number on all the invoice items? You now have incosistant data. Also, if they do remember to change it in both places and the invoice has 10,000 items, 1 update is now 10,001 updates!
1. Redundant Data (think big! If you have an invoice with 10,000 items on it, you are storeing the invoice number 10,001 times.)
2. Data consistancy. Always rely on the primary key. What if someone changes the invoice number, but forgets to update the invoice number on all the invoice items? You now have incosistant data. Also, if they do remember to change it in both places and the invoice has 10,000 items, 1 update is now 10,001 updates!
One other thing, if you really want the invoice number accessible from the invoice_item level, create a view called invoice_full_view that would be:
select a.*, b.* from invoice a
join invoice_item b on a.invoice_id = b.invoice_id
Then you app can read data from this view and get your data in one shot.
select a.*, b.* from invoice a
join invoice_item b on a.invoice_id = b.invoice_id
Then you app can read data from this view and get your data in one shot.
ASKER
kpresstage: Do you include the invoice# in the "InvoiceItemsTable" for readability/safety reasons?