Re: 3NF question

"Paul" <paul_at_test.com> wrote in message news:41e58c41$0$16974$ed2e19e4_at_ptn-nntp-reader04.plus.net...

> The problem I have with this is that it seems somehow redundant to name
> everything in the employee table as employee_id, employee_name,
> employee_salary, etc. I know it's in the employee table already, so why
> append the word "employee" to all the column names?

First off, you don't label every column in the employees table. Let's say you have a column named "CITY" in the employee table. It's part of the employee's home address. You don't call it "EMPLOYEE_CITY". You call it "CITY". Or if you want to be more precise. you call it "CITY_NAME".

Second, I question two things. I question whether it's redundancy at all. And even if it is redundancy, I question whether it's harmful. It seems to me that "EMPLOYEE_ID" does not refer to the table named "EMPLOYEES". Rather both the table name and the column name refer to the same entity in the subject matter, namely an employee. Even if you don't buy that, what's the harm in adding in the extra letters in the coulmn name. Surely it's not the extra typing!

Third, and this is the main point. Regardless of your naming convention , you will have to document the relationship between the column "ID" and the thing it identitifes. And, "the same as the thing the table identifies" isn't going to work once you have tables where all the candidate keys are compound. Joe Celko asked whether you do not have a data dictionary.

Most people who build databases with less than a dozen tables don't feel the need for a data dictionary. By the time they have a database consisting of over a hundred tables and maybe 500 columns, the need for a data dictionary becomes apparent, but the warning comes too late. If you doubt me on this matter talk to a lot of people who have been there and done that.

>
> I guess the problem is when you have foreign keys to id columns in other
> tables. Well maybe then you could have id_dept or something. Though it
> might mess up "natural joins", but then maybe they aren't that good an
> idea anyway.

Another way to identify "natural joins" is to declare referential integrity in your database, and use that as a clue to which joins are "natural". Unfortunately, the people who like to call columns with names like "ID" also like to omit referential integrity constraints in the schema.

> I'm not sure what the answer is to the whole naming question, but can't
> the column name "id" be interpreted in the context of the name of the
> table in which it appears?

There's another problem here. Sooner or later, data values get ripped out of context.

Let's say you use an "export data" utility to create a CSV file, and you take the option to include the coulmn names. MS Access, for instance, has this option. If you called your column "ID", it's now pretty cryptic in the CSV file, because the table name doesn't appear there. Received on Thu Jan 13 2005 - 16:24:01 CET