Why do they insist on repeating the name of the table in the column name?
-
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.
-
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.
-
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.
It's not just you. One of our main tables, Data_Entry_Sub_Group looks like this: Identity field: Data_Entry_Sub_Group_ID Descriptor field: Data_Entry_Sub_Group_Description FK field: Data_Entry_Group_ID which references a table laid out the same way ... I inherited this structure 14 years ago and have built tons of code around it. Thank God for Copy/Paste and aliases! I've got quite a few tables that were designed the same way. It's hard to justify changing names/structures for the sake of convention. I have managed to phase out a lot of the offenders, with a planned rewrite in the near future which will take care of the rest. I don't mind verbose table names and have actually gotten more descriptive with them over the years. My biggest peeve when having to deal with other vendor's databases are table/column names with spaces or column names that are reserved...an actual example is a column named 'datetime'. What were they thinking? :confused:
"Go forth into the source" - Neal Morse
-
I hate it when they do that on objects: Customer.CustomerCatagory Customer.CustomerType Why not just Customer.Catagory? Why not just Customer.Type?
-
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.
Our phone system has CallIDKey AKA CallID AKA I3_Identity.
-
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
Account number is one of our all time records for different spellings. Do not forget CustomerAcct#
-
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).
Why use an alias to begin with? select student.LastName, studend.FirstName, student.Id...
-
Our phone system has CallIDKey AKA CallID AKA I3_Identity.
Tough for someone new or a consultant to know which fields to join then.
People say nothing is impossible, but I do nothing every day.