Why do they insist on repeating the name of the table in the column name?
-
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.
-
Why use an alias to begin with? select student.LastName, studend.FirstName, student.Id...
Paulo Zemek wrote:
Why use an alias to begin with?
select student.LastName, studend.FirstName, student.Id...Your post is the answer to your question. Notice the misspelling of your second field. :) That may not be as big of a deal nowadays as we're finally getting intellisense for some products.
-
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.
Bruce Patin wrote:
I wouldn't necessarily know from fk_father or fk_mother that the key related to the Human(s) table.
Would be logical if you have no other animals in your database.
Bruce Patin wrote:
I would probably call the fields HumanIdFather and HumanIdMother for clarity.
You forgot to prefix the databasename and the schemaname. Point is that I rather see a short and descriptive name, something that makes sense. ..or, in my usual tone, "ffs, if you can simply create the names by deducing them from the structure, then stop doing it manually and automate it". Call it Access+.
Bruce Patin wrote:
And why do you have a "Humans" table and a "Human" table?
Only one, I was too quick with typing.
Bruce Patin wrote:
I also object to using plurals for table names.
I object against personal preferences. A table is a collection of records, and hence, plural.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]
-
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!
The aliases are a different subject. I adopted them to mimic the previous examples; although I will use aliases like that if prudent. Nothing is wrong with either option. I believe matching the field names provides clear intent. I can look at a large query and not have to guess if the join is right. I also stylistically abhor underscores in table names. Does that make it wrong? No. The way I look at it, is that I should be able to do the following (although I wouldn't) and have the resulting resultset meaningful: select * from ... And it wouldn't if all the key fields in the joined tables were named "Id".
-
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).
tgrt wrote:
Again you're talking about two different things
Nope, the naming is consistent, and has no "exceptions".
tgrt wrote:
A self-reference could require clarification
Yours has.
tgrt wrote:
In my opinion using a fk_ prefix is horrible!
..you don't mind a repetition of the table-name in a fieldname, but do mind an extra tag indicating that it's a key? There's an argumentation and a justification for the MO; just like in .NET, I'm using descriptive and full names. Foreign keys are prefixed, since they're pointers, not data. They do not hold "information" in the users' view, just "data". It's also bloody obvious how many relations one has in a single table in the blink of an eye, it's cute with intellisense when typing a join (fk_ and there's a list), and it makes documentation a bit easier. I'll bug you a bit more; The auto-increment is NEVER* my PK, but a simple unique-constraint on a field called "Id".
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]
-
Paulo Zemek wrote:
Why use an alias to begin with?
select student.LastName, studend.FirstName, student.Id...Your post is the answer to your question. Notice the misspelling of your second field. :) That may not be as big of a deal nowadays as we're finally getting intellisense for some products.
And having a field named: TableId I can also write TabelId...
-
I'm guilty of using CustomerType, but only because Type is a reserved word in all the languages I work with.
:thumbsup: I do the same for reserved words. Sure, VB/C# have ways around that (escaping the reserved words so they can be used as variables/properties), but I find it less confusing to just prefix something.
-
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.
Simple answer: FK. A table with 14 "id" columns might be somewhat hard to work with.
I wanna be a eunuchs developer! Pass me a bread knife!
-
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.
But that can't always work, as when a table is self-referential or several fields refer to the same table. CustomerID is just not a good choice of 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
Well actually you're not alone, that's my preference of naming too. Actually if a table doesn't have a natural ID column I feel almost olbiged to add one (either bigint or more recently uniqueidentifier). For other columns I prefer to use something closer to the real use of the column e.g.: FirstName LastName etc... and oh I really hate the habits developped by some developper or database designers: TBL_ for a table name COL_ for a column name _NU for a numeric column _CHR for a string column _DT for a datetime column etc.... (I think you can see the picture here) I really don't see the point in prefixing a table or column name with and indicator of the type of object, frankly when we start typing "SELECT * FROM " dont we know the next word will be a table name ? or that the list of words after the SELECT represents columns ? Now having a meaningfull prefix for a column name can help understand queries with joins and can save aliasing even if it means longer queries let's say than instead of having ID everywhere we have CUS_ID, ORD_ID, etc... then we don't need aliasing since we know for sure that CUS_ID comes from the Customer table and ORD_ID from the Order table.... As for the postfix representing the type of the column I'm completely clueless to its advantages, if you code against a database then you must known its structure, that means knowing the table names, the column names and their types !
-
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 cannot stand that. :mad: It disgusts me even more than prefixing each table with 'tbl'.
-
But that can't always work, as when a table is self-referential or several fields refer to the same table. CustomerID is just not a good choice of name.
PIEBALDconsult wrote:
But that can't always work, as when a table is self-referential
That's true, But it doesn't make it any worse than using just ID as a fieldname.
PIEBALDconsult wrote:
or several fields refer to the same table.
Don't get the problem here, would you mind to elaborate? Should probably mention that the standard to name the key, TableName + ID, applies to surrogate keys, not natural keys. For Compound keys or Composite keys I don't see the problem.
People say nothing is impossible, but I do nothing every day.
-
tgrt wrote:
Again you're talking about two different things
Nope, the naming is consistent, and has no "exceptions".
tgrt wrote:
A self-reference could require clarification
Yours has.
tgrt wrote:
In my opinion using a fk_ prefix is horrible!
..you don't mind a repetition of the table-name in a fieldname, but do mind an extra tag indicating that it's a key? There's an argumentation and a justification for the MO; just like in .NET, I'm using descriptive and full names. Foreign keys are prefixed, since they're pointers, not data. They do not hold "information" in the users' view, just "data". It's also bloody obvious how many relations one has in a single table in the blink of an eye, it's cute with intellisense when typing a join (fk_ and there's a list), and it makes documentation a bit easier. I'll bug you a bit more; The auto-increment is NEVER* my PK, but a simple unique-constraint on a field called "Id".
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]
Eddy Vluggen wrote:
The auto-increment is NEVER* my PK, but a simple unique-constraint on a field called "Id".
Hi Eddy Do you mind me asking why? It's something I think about from time to time & can't find a killer argument either way - you seem to have found a way. I can see advantages & disadvantages in auto-increment as PK & unique identifying info as PK, but neither really 'wins' consistently. Regards, Stewart