Just cant get my head around queries

[David W. Fenton]

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.

What part of "in my opinion" is unclear?

 

[David W. Fenton]

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.

 

[David W. Fenton]

For all fields in a table other than foreign keys, we prefix the
field name with the table name, like tblContact has
ContactFirstName and ContactLastName. This ensures that no two
fields (other that FKs) are named the same. It keeps things clear
when the table name isn't readily visible.

I don't understand what value there is to this. When would you ever
have more than one table with "FirstName" and "LastName" as field
names? Aren't all people the same entity type, and, thus, belong in
the same table?

The occurence of variations of the same field name in multiple
tables when the fields are not metadata (like Created or UpdatedBy)
is a signal to me that there's a schema problem.

 

[Guest]

You've certainly made declarations in your follow-on reply that did not
include the disclaimer "In my opinion". Take, for example, the statement:

"It is only PK/FK fields that contain the same data, and should be named the
same."

or this paragraph:

"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)."

These words sound quite authoritative, without any disclaimer that includes,
"In my opinion". So, my challenge remains to you: Show me an authoritative
reference that supports your two assertions shown above.

Regarding your last paragraph
http://www.microsoft.com/office/com...cess&mid=7b3ec245-c625-485e-93d6-3ef8dc01662c

I'm not going to even dignify that response with an answer. If you choose to
take that approach in your reply, which basically states that if one doesn't
agree with you, then they are a fool or small minded, then I'll have nothing
more to do with you. I've only plonked one other person in the past, however,
that response comes very close to earning such a response. Not that I'd
expect you to care one way or another.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[David W. Fenton]

So, my challenge remains to you: Show me an authoritative
reference that supports your two assertions shown above.

My case stands on its own logic or it doesn't. I don't need to point
to any outside authorities for the argument to be persuasive.

 

[Jamie Collins]

fields should generally be consistent throughout the schema.
Sometimes they drop the class name (e.g. customer_last_name becomes
last_name in the Customer table) and occasionally they gain a
representation term (e.g. employee_ID becomes subordinate_employee_ID
and manager_employee_ID respectively).

Ahem, I meant to say, "occasionally they gain a qualifying term".

I also forgot to mention that I try to follow the ISO 11179-5
convention for column names e.g. see
http://en.wikipedia.org/wiki/Data_element_name (and of course
http://en.wikipedia.org/wiki/Not_invented_here <g>).

Jamie.

--

 

[Guest]

I don't need to point to any outside authorities for the argument

to be persuasive.

If your goal is to only persuade yourself, then you are correct, you don't
need to site outside authorities.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[David W. Fenton]

Tom Wickerath <AOS168b AT comcast DOT net> wrote in

[quoting me:]

If your goal is to only persuade yourself, then you are correct,
you don't need to site outside authorities.

Had I made an assertion without offering any justification or
explanation, you'd be correct. But I didn't do that. Thus, my
assertions stand on their own merit, based on whether or not you
agree with the reasoning I gave for my point of view.

 

[Guest]

Hi David,

Fair enough.
And end of thread, as far as I am concerned.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________