I'm not about to get into a religous debate over the merits of
natural keys versus meaningless numeric keys; that is not germaine
to this discussion. In the case of the ShipVia key, it is a
numeric foreign key. (I'll agree with Jamie that Northwind is
certainly not a shining example of database design). The issue was
your statement "In my opinion, fields that contain the same data
should have the same name." I've never seen this written in stone
for primary key/foreign key fields in any authoratative database
design books. If you can find this, please cite a reference.
For non-key fields, take for example UnitPrice in the Northwind
Products table versus UnitPrice in the Order Details table, the
fields could be considered to contain the same data.
No. They do not contain the same data. It is only PK/FK fields that
contain the same data, and should be named the same.
Would you always name these types of
fields the same name (ie. where one field is duplicated to store
historical information, whether it be UnitPrice, Customer Name,
Customer Address, etc.)?
Is it the same data? No, of course not. One is the unit price for a
particular line item of a particular invoice. One is the unit price
for an inventory item at a particular point in time. Those are not
at all the same piece of data, so one need not use the same field
name (though one might do so, but in that case it could cause
problems because it's perfectly plausible that you'd join the two
tables that had the same field name in them).
The key point: if the fields are for establishing the relationship
between tables, they should have the same name, because they contain
exactly the same data (if they didn't, they wouldn't be serving
their purpose).
Consider the following quote taken from page 22 of SQL Queries for
Mere Mortals (page referenced for the edition published in 2000):
"The main thing to remember: Make sure that each field in your
database has a unique name and that it appears only once in the
entire database structure.
I don't subscribe to that kind of dogma. I think it's a ridiculously
rigid rule.
The only exception to this rule is when a field is being used to
establish a relationship between two tables."
Well, I guess I should read on. I'm not sure that this is the only
exception. I certainly have Created, Updated and UpdatedBy fields in
all my tables, and it would be a real pain to give them different
names. On the other hand, when I am using subclassing, where I have
a 1:1 structure, each with its own Created, Updated and UpdatedBy
fields, then it's a problem, and I would likely give the three
fields individual names (in the past I've aliased them to give them
unique names for the join that served as a recordsource, but it was
a real pain, though less because of the names than in figuring out
which fields had been updated and which Created/Updated/UpdatedBy
fields needed to be changed).
So, Michael Hernandez and/or John Viescas have definately
indicated where they stand on non-key fields. Key fields are the
only exception.
I think I'd broaden that to metadata fields, such as
Created/Updated/UpdatedBy, or fields that have to do with the
structure of the database and not the content of the entities
described by the tables.
But, I just
don't see anything that states that one must (or should) always
name their key fields the same.
I don't give a rat's ass if any books anywhere agree with me.
Experience tells me that my approach is much more efficient than
either set of rules you've put forth. Foolish consistency is the
hobgoblin of small minds, and that's why any "rule" has to have
exceptions.