Why do they insist on repeating the name of the table in the column name?
-
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
-
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?
Sorry. I said I don't like to be a spell-checking nuisance, and wouldn't mention it if it wasn't code, but this matter really has bitten me a few times, after taking over code from someone who couldn't spell.
-
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.
Me: Mrs. Smith, little Jimmy is a an idiot; he says he can't read this SQL statement. Mrs. Smith: Even I can read that; Jimmy you're an idiot.
-
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
Another reason not to prefix column names with fk. ;)
-
I think I'm all done with this site. The grammar Nazi bot to actual contributor ratio is much too high.
People who come here are expected to have some sense of humour. It's always been that way.
Phil
The opinions expressed in this post are not necessarily those of the author, especially if you find them impolite, inaccurate or inflammatory.
-
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).
What's wrong with
select student.LastName, student.FirstName, course.Name
from student
join student_course on student.id = student_course.student
join course on student_course.course = course.idI don't understand why you'd use tiny aliases and then say you need to spam up column names because you just took away the context!
-
Sometimes people do that to avoid the "Ambiguos column name error". I have really seen that. The problem is, once the column name has been there for some time, it's hard to change it.
-
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.
I agree. But you can't change horses mid-stream so just go along with the prevailing standard. When you start a new project (perhaps a personal project) you can do it the right way. On another hand, I also somewhat disagree with a foreign key being something like UserID -- saying ID is (or should be) redundant and it should probably be a more descriptive name, not simply the name of the table it references. Bear in mind that some tables will have more than one reference to some other table, or to itself. Another situation we have here is a many-to-many relationship between tables so there is no foreign key in the actual table anyway. Basically, there is no rule that always works in every situation.
-
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.
If a join isn't clear then you can use 'customer.id' instead of 'c.customer_id'. And that way you don't lumber simple queries on customer with the unneeded context.
-
People who come here are expected to have some sense of humour. It's always been that way.
Phil
The opinions expressed in this post are not necessarily those of the author, especially if you find them impolite, inaccurate or inflammatory.
Yes, I lack a sense of humor.... because you'd have to be humor challenged not to see the chuckles inherent in reading yet another grammar correction on a casual shoot-from-the-hip forum. I'll be honest, there was a time I laughed at grammar corrections. They were funny for a short time in the early '90s.