Why do they insist on repeating the name of the table in the column name?
-
With regard to database design: Is it just me or are there others out there who are driven nuts by repeating the table name in the column name. E.g., I see things like Widget_Attribute_Type.Widget_Attribute_Type_Id all the time when all that is needed is Widget_Attribute_Type.Id. Seems when I debate this with the DBA types and architects they use the same [similar] tired arguments.
Making a database consistent is not only about normalization. One should also have the same name on the fields everywhere you use them. So CustomerID is called CustomerID in both the Customers table and the Orders table. There must never be any uncertainties. It's also ISO-11179 compliant.
People say nothing is impossible, but I do nothing every day.
-
You're talking about something different. The second query would be:
SELECT *
FROM Employee e
JOIN Department d ON d.DepartmentId = e.DepartmentIdI'm leaving the asterisk for the sake of brevity.
How is that more clear than without the table-name?
SELECT *
FROM Employee e
JOIN Department d ON d.DepartmentId = e.DepartmentIdSELECT *
FROM Employee e
JOIN Department d ON d.Id = e.fk_DepartmentOne does not repeat the name of the table where the fk originates from; it's very confusing to have a foreign key that always consists of a table-name and id if you have multiple references to the same table;
SELECT *
FROM Humans h
JOIN Human hf ON h.fk_father = h.Id
JOIN Human hm ON h.fk_mother = h.IdIt's also kinda easy to have each primary key named "Id", and it keeps it readable, even for large structures. The foreign key should have a descriptive name - not just a concatenation of the originating table with the constant "Id". Below is your version;
SELECT *
FROM Humans h
JOIN Human hf ON h.HumanId1 = h.HumanId
JOIN Human hm ON h.HumandId2 = h.HumanIdEnjoy :)
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]
-
Heck I have tables with "TABLE" as part of the table name. "Widget Attribute Table" Notice the spaces. Makes live interesting. The person who setup the original ACCESS database even used column names like "Widget Attribute Table Query #Last-Name", notice the pound sign and dash. However, I cannot complain too much as he is married to the company founder. :sigh:
To paraphrase Joe Celko: There are a lot of furniture makers out there.
People say nothing is impossible, but I do nothing every day.
-
Heck I have tables with "TABLE" as part of the table name. "Widget Attribute Table" Notice the spaces. Makes live interesting. The person who setup the original ACCESS database even used column names like "Widget Attribute Table Query #Last-Name", notice the pound sign and dash. However, I cannot complain too much as he is married to the company founder. :sigh:
I'm sure there's a joke in there somewhere about breaking Cod's laws and ending up in an entity relationship with someone at the same table but perhaps not. Sorry too much :java: not enough :zzz:
"The secret of happiness is freedom, and the secret of freedom, courage." Thucydides (B.C. 460-400)
-
Eddy Vluggen wrote:
..worse are the people who add the text "table" to a tablename
Sometimes coupled with each field starting fld_ I'm fairly certain I had to work with something like tbl_Customer.fld_CustomerId in Access back in the nineties. Regards, Stewart
Yeah, Access gets confused if you don't tell it which is the table and which the field. I imagine it would try to read the table "Customer" from the field "Customer" if it's not told using a nice prefix which kind of object were talking about :suss:
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]
-
With regard to database design: Is it just me or are there others out there who are driven nuts by repeating the table name in the column name. E.g., I see things like Widget_Attribute_Type.Widget_Attribute_Type_Id all the time when all that is needed is Widget_Attribute_Type.Id. Seems when I debate this with the DBA types and architects they use the same [similar] tired arguments.
Because they are clueless retards.
-
You're talking about something different. The second query would be:
SELECT *
FROM Employee e
JOIN Department d ON d.DepartmentId = e.DepartmentIdI'm leaving the asterisk for the sake of brevity.
As opposed to the obscure:
SELECT *
FROM Employee
JOIN Department ON Department.Id = DepartmentIdAssuming Employee has an ID field (if not, the
department.is redundant) or even the following, which is totally unambiguous:SELECT *
FROM Employee
JOIN Department ON Department.Id = Employee.DepartmentIdSort of a cross between Lawrence of Arabia and Dilbert.[^]
-Or-
A Dead ringer for Kate Winslett[^] -
Eddy Vluggen wrote:
..worse are the people who add the text "table" to a tablename
Sometimes coupled with each field starting fld_ I'm fairly certain I had to work with something like tbl_Customer.fld_CustomerId in Access back in the nineties. Regards, Stewart
I think you are making the basic error of confusing Access with a database :)
Sort of a cross between Lawrence of Arabia and Dilbert.[^]
-Or-
A Dead ringer for Kate Winslett[^] -
With regard to database design: Is it just me or are there others out there who are driven nuts by repeating the table name in the column name. E.g., I see things like Widget_Attribute_Type.Widget_Attribute_Type_Id all the time when all that is needed is Widget_Attribute_Type.Id. Seems when I debate this with the DBA types and architects they use the same [similar] tired arguments.
Two things I hate in column names: 1: Underscores 2: Abbreviations #1: I hate underscores because Account_Number isn't any easier to read than AccountNumber. Also, they are used inconsistently and add unneccessary length to column names. It really is a habit that needs to stop immediately. #2: I hate abbreviations in column names because it only causes confusion and doesn't really save any time/effort. In fact, it adds to the time it takes to maintain a database. Clarity should win out over saving the time it takes to type a couple of letters. The beauty is when you combine #1 & #2 to create mass confusion that saves nothing. For example: Acct_No Accout_Num Acc_Nbr Acct_Num I've seen several variations on AccountNumber within the same database because each and every administrator has his/her own clever take on using underscores and abbreviations. They all know they are expressing the phrase 'AccountNumber' but each of them uses a variation on a ridiculous naming convention. Of course, one day I'll be an administrator so I'll add my own variations a few days before I retire: A_cct_No_mber Ac____nt_NUMber Ac_WTF?_Number -MehGerbil
-
My personal preference:
create table customer
id,
name,
dateOfBirth, etcI have a real preference for 4th normal because I don't like null checks in code, The down side is a less natural object model. For foreign keys:
create table order
id,
customerId,
etcIt is actually, kind of funny, my rationalization for the Id. Code commonality. As far as the DB is concerned consistent trumps any rationalization but when it comes to writing code, writing less code is better. If Id is always the key value there are a lot of interfaces and base classes that can be written to support that. (No, I don't use code generators) [Yes, I know they can save a lot of time; yet I have never missed a dead-line because of DAL code--I am just that good] My real and true db pet peeve, however, is people that Alias all table names. There are cases for aliasing, sub-query joins, multiple joins on the same table, name too long, but to alias just to save typing significantly reduces the readability of the query. Consider:
select o.id,c.id, /*notice here one of the reasons some people use table name?*/,
l.id,c.name, op.method from order o,customer c, lineItem l, orderPayemnt op
where o.customterId=c.id and l.orderId=o.id and op.orderId=o.idvs:
select
order.id orderId,
customer.id customerId,
lineItem.id lineItemId,
customer.name,
orderPayment.method
FROM
customer
JOIN order ON
order.customerId = customer.Id
JOIN lineItem ON
lineItem.orderId = order.id
JOIN orderPayment ON
orderPayment.orderId = order.idWith the expense of a few extra key strokes, every one and their mother can read and modify the query.
Need custom software developed? I do custom programming based primarily on MS tools with an emphasis on C# development and consulting. "And they, since they Were not the one dead, turned to their affairs" -- Robert Frost "All users always want Excel" --Ennis Lynch
Why would you want people's mothers to modify the query?? You just spoiled a good argument! ;P
Phil
The opinions expressed in this post are not necessarily those of the author, especially if you find them impolite, inaccurate or inflammatory.
-
I hate it when they do that on objects: Customer.CustomerCatagory Customer.CustomerType Why not just Customer.Catagory? Why not just Customer.Type?
MehGerbil wrote:
Why not just Customer.Catagory?
Because misspelled column names are even worse than overly long ones! ;P
Phil
The opinions expressed in this post are not necessarily those of the author, especially if you find them impolite, inaccurate or inflammatory.
-
Because they are clueless retards.
-
How is that more clear than without the table-name?
SELECT *
FROM Employee e
JOIN Department d ON d.DepartmentId = e.DepartmentIdSELECT *
FROM Employee e
JOIN Department d ON d.Id = e.fk_DepartmentOne does not repeat the name of the table where the fk originates from; it's very confusing to have a foreign key that always consists of a table-name and id if you have multiple references to the same table;
SELECT *
FROM Humans h
JOIN Human hf ON h.fk_father = h.Id
JOIN Human hm ON h.fk_mother = h.IdIt's also kinda easy to have each primary key named "Id", and it keeps it readable, even for large structures. The foreign key should have a descriptive name - not just a concatenation of the originating table with the constant "Id". Below is your version;
SELECT *
FROM Humans h
JOIN Human hf ON h.HumanId1 = h.HumanId
JOIN Human hm ON h.HumandId2 = h.HumanIdEnjoy :)
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]
I wouldn't necessarily know from fk_father or fk_mother that the key related to the Human(s) table. I would probably call the fields HumanIdFather and HumanIdMother for clarity. And why do you have a "Humans" table and a "Human" table? I also object to using plurals for table names.
-
MehGerbil wrote:
Why not just Customer.Catagory?
Because misspelled column names are even worse than overly long ones! ;P
Phil
The opinions expressed in this post are not necessarily those of the author, especially if you find them impolite, inaccurate or inflammatory.
-
I hate it when they do that on objects: Customer.CustomerCatagory Customer.CustomerType Why not just Customer.Catagory? Why not just Customer.Type?
How about Customer.Category? I try not to be a spell checking nuisance, but I really object to incorrectly spelled identifiers in code that gets replicated all over an application that may be maintained by multiple people. It can lead to problems when someone searches for "category" in an application and doesn't find any references to it.
-
I wouldn't necessarily know from fk_father or fk_mother that the key related to the Human(s) table. I would probably call the fields HumanIdFather and HumanIdMother for clarity. And why do you have a "Humans" table and a "Human" table? I also object to using plurals for table names.
Hey, Oedipus, Kid Sister rule, please : )
Bruce Patin wrote:
fk_father or fk_mother
Need custom software developed? I do custom programming based primarily on MS tools with an emphasis on C# development and consulting. "And they, since they Were not the one dead, turned to their affairs" -- Robert Frost "All users always want Excel" --Ennis Lynch
-
How about Customer.Category? I try not to be a spell checking nuisance, but I really object to incorrectly spelled identifiers in code that gets replicated all over an application that may be maintained by multiple people. It can lead to problems when someone searches for "category" in an application and doesn't find any references to it.
Like I said in my reply to Phil, you guys win. For those who actually read posts, instead of proof-reading them: Anyone know a tech site where I can hang out, one where the population isn't composed primarily of retired high school English teachers waiting to work through their ennui by targetting people who ignored their lessons and went on in life to be successful anyways? I'm sure I spelled something in there wrong - or perhaps confused a verb tense or something. Why don't you guys discuss it?
-
How is that more clear than without the table-name?
SELECT *
FROM Employee e
JOIN Department d ON d.DepartmentId = e.DepartmentIdSELECT *
FROM Employee e
JOIN Department d ON d.Id = e.fk_DepartmentOne does not repeat the name of the table where the fk originates from; it's very confusing to have a foreign key that always consists of a table-name and id if you have multiple references to the same table;
SELECT *
FROM Humans h
JOIN Human hf ON h.fk_father = h.Id
JOIN Human hm ON h.fk_mother = h.IdIt's also kinda easy to have each primary key named "Id", and it keeps it readable, even for large structures. The foreign key should have a descriptive name - not just a concatenation of the originating table with the constant "Id". Below is your version;
SELECT *
FROM Humans h
JOIN Human hf ON h.HumanId1 = h.HumanId
JOIN Human hm ON h.HumandId2 = h.HumanIdEnjoy :)
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]
Again you're talking about two different things. A self-reference could require clarification (e.g. FatherHumanId). In my opinion using a fk_ prefix is horrible! The binding is clear when the names match. And that's especially true in a complex enterprise system. Tables and objects are not the same thing. Let's take a slightly more complex example. Student can take many Courses; and a Course can have many Students. You would model that with a simple bridge table. Here's the table contents: Student(StudentId, LastName, FirstName, ...) Course(CourseId, Name, ...) StudentCourse(StudentCourseId, StudentId, CourseId, ...)
select s.LastName, s.FirstName, c.Name
from Student s
join StudentCourse sc on s.StudentId = sc.StudentId
join Course c on sc.CourseId = c.CourseIdYour method would read like:
select s.LastName, s.FirstName, c.Name
from Student s
join StudentCourse sc on s.Id = sc.fk_Student
join Course c on sc.fk_Course = c.IdThe second is not nearly as clear and much more prone to error. The first requires no guessing on the naming and the only time it would be different is in special circumstances such as a self-reference (e.g. t2.HumanId = t1.FatherHumanId; a weird example but I'll stick with it since it was your example).
-
As people have said it makes joins easier and intuitive to figure out what goes where in multi-table joins. Also most reporting tools automatically figure out the related fields if you follow this pattern.
Rama Krishna Vavilala wrote:
As people have said it makes joins easier and intuitive to figure out what goes where in multi-table joins.
Came here to say this! Knew you'd have said this already when I saw you'd posted. :-) If you use something like EF, the auto-generated properties read better too.
Regards, Nish
My technology blog: voidnish.wordpress.com
-
Like I said in my reply to Phil, you guys win. For those who actually read posts, instead of proof-reading them: Anyone know a tech site where I can hang out, one where the population isn't composed primarily of retired high school English teachers waiting to work through their ennui by targetting people who ignored their lessons and went on in life to be successful anyways? I'm sure I spelled something in there wrong - or perhaps confused a verb tense or something. Why don't you guys discuss it?
Deep, cleansing breaths. :-D
BDF I often make very large prints from unexposed film, and every one of them turns out to be a picture of myself as I once dreamed I would be. -- BillWoodruff