Why do they insist on repeating the name of the table in the column name?
-
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
-
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
How about this bloody crap? tblAcct_No tblAccout_Num tblAcc_Nbr tblAcct_Num
-
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.
PIEBALDconsult wrote:
Basically, there is no rule that always works in every situation.
Phew! At last, after ploughing through all of these responses, I have finally hit one that agrees with what my first thought was. If you are inheritting a database or building a database for a company that has its own naming standards, you have to roll with the punches and accept the existing naming standards even if they are awful. If you are creating a new database and there are no naming standards, set an example by creating a simple, consistent, flexible naming standard that others will be pleased to follow. One other respondent mentioned that they have to compromise based on the environment. That is something else that one has to factor in. 'It works' trumps 'It looks pretty, even though it is unusable'. I am currently working on a No SQL type of environment and I have had to create a naming standard that even I don't like so that it works pragmatically.
-
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.
Obligatory T.S. Eliot reference: The Naming of Cats[^] (being married to a woman with an M.A. in English has its hazards)
Software Zen:
delete this; -
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
-
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![^]
OK, I'll give on the father/mother thing for a simple database. Concerning plural table names, I call it a preference, due to fact that the subject seems to be controversial. While a table is a collection of multiple records, a table is named after the definition of one record of the table, and carrying that definition forward into an object-oriented program that uses it, things work out much better if the name is kept singular, as it will correspond with the name of the class you would use to describe one record. Not to mention the problem with irregular plurals. If you then want to identify a collection in the program, you can use a plural, or better, use an appropriate modifier, such as HumanList or HumanArray. The English language is not a good and proper programming language, and trying to make database logic conform to English is a mistake. If a table was allowed to have a different name than that of the type of record that it contains, you could give the table a plural name, so that you could have a Humans table containing multiple Human records. But the designers of SQL did not allow separate names, so using a plural gives you the situation of extracting one (1) Humans record from the table and mapping it to one Human object in a program. That is not logical.
-
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.
As far as I'm concerned, simplicity is the order of the day. IMHO, columns should be named meaningfully but not verbosely and not with redundant crap in them. In our database here we have nonsense like "STM_Employee.STM_EmployeeID". That's patently ridiculous. The reference should have been Employee.ID. If a schema is involved then it would be Schema.Table.Column ... I.E. JoesMachineShop.Employee.ID. Anything more involved (as far as I'm concerned) is just fluff that's put there by a DBA that's trying to preserve his job. -CB
-
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.
You will understand this when one day you analyse the system, say, trying to find out how come "AccountId" is null in "Audit" table. You will find that half of 200 tables have "AccountId" column, so this string is used 5900 times in 400 stored procedures. And of cause, if your SQL is formed in the code, you will find the "AccountId" is very popular as ... a class member! You will spend the rest of the month seifing through the code. And all that could save you the trouble was simply name that column "Audit_AccountId" :) It's just experience.
-
PIEBALDconsult wrote:
Basically, there is no rule that always works in every situation.
Phew! At last, after ploughing through all of these responses, I have finally hit one that agrees with what my first thought was. If you are inheritting a database or building a database for a company that has its own naming standards, you have to roll with the punches and accept the existing naming standards even if they are awful. If you are creating a new database and there are no naming standards, set an example by creating a simple, consistent, flexible naming standard that others will be pleased to follow. One other respondent mentioned that they have to compromise based on the environment. That is something else that one has to factor in. 'It works' trumps 'It looks pretty, even though it is unusable'. I am currently working on a No SQL type of environment and I have had to create a naming standard that even I don't like so that it works pragmatically.
So true about "I do not like it but it works". Just on a simple topic of table names, a few times (on new projects) I started with "CustomerAccount" tables, but ended up re-factoring all work to use "t_customer_account", etc. Some software could not cope with mixed-case strings. And absolutely true about having common standard - worth than anything is mixing different styles, but I am sure no-one goes that far :)
-
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
MehGerbil wrote: It really is a habit that needs to stop immediately Habit, Stop! :thumbsup:
-
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.
Jörgen Andersson wrote:
elaborate
For example a table with two references to a User table, like Sender and Recipient -- in neither case is UserID an appropriate name for the field.
-
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 a nuisance, the only permitted use for this in my systems is when, first, it's a foreign key, and second you can't come up with a better and more descriptive name.
CEO at: - Rafaga Systems - Para Facturas - Modern Components for the moment...
-
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
Stewart Judson wrote:
Do you mind me asking why?
Of course not, been planning an article on the subject for some time. Short answer; if an identity would suffice, then the database-software would have abstracted it away, adding it automagic to each table-definition. Ages ago, we had non-normalized lists on our computers. Very soon we noticed things like double (or triple) entries, describing the same entity. You've probably seen a database where a customer was entered twice. Normalization (among other things) state that any record in the table should have a key to uniquely identify it. Follow the normalization-rules, and you end up with some tables having a very large compound key. Still, I consider that my primary key, that's the collection of fields that I can tell the end-user to look at to differentiate between the records he sees. Each record describes a "thing" in the real world. A table is a collection of those thingies, and a user simply needs to be able to see which record describes which thingy. I need to uniquely identify them too, hence an additional surrogate-key. This can be an auto-increment, a GUID, or a varchar. I don't care, the surrogate doesn't hold information, it holds a pointer. What I do care about is that I too have a unique field to work with in code. I never need to print it's ID on screen, as the user will never, ever, ever use it to identify a record. Still, it pays to have a simple one-field alternate key, as it keeps our source-code clean.
Stewart Judson wrote:
I can see advantages & disadvantages in auto-increment as PK & unique identifying info as PK, but neither really 'wins' consistently.
Any arguments against using them both?
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]
-
Jörgen Andersson wrote:
elaborate
For example a table with two references to a User table, like Sender and Recipient -- in neither case is UserID an appropriate name for the field.
I get your point. I could of course argue that it wouldn't be fully normalized. But that would be just silly in this case. That an entity should have the same name everywhere is a good rule to follow, but not at any cost. But also in this case I fail to see how calling the field just ID makes it any better. In my old job I had a customer whose database had more than ten thousand tables. They also had too many consultants. After bugfixing some of the queries they had, I got quite convinced that the naming needs to be as consistent as possible.
People say nothing is impossible, but I do nothing every day.
-
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.
http://www.tonymarston.net/php-mysql/database-design-ru-novice-ninja-or-nincompoop.html I read this article a while back and agree with some (but not all) that it states. IMO, he does a pretty good job of defending why you would use table_id instead of simply "id" for your technical key name.
-
I get your point. I could of course argue that it wouldn't be fully normalized. But that would be just silly in this case. That an entity should have the same name everywhere is a good rule to follow, but not at any cost. But also in this case I fail to see how calling the field just ID makes it any better. In my old job I had a customer whose database had more than ten thousand tables. They also had too many consultants. After bugfixing some of the queries they had, I got quite convinced that the naming needs to be as consistent as possible.
People say nothing is impossible, but I do nothing every day.
Jörgen Andersson wrote:
an entity should have the same name everywhere is a good rule to follow, but not at any cost
Correct. The name should give some context not just datatype information; e.g. why is the particular User associated with the current record? What part does he play in this little drama?
-
Stewart Judson wrote:
Do you mind me asking why?
Of course not, been planning an article on the subject for some time. Short answer; if an identity would suffice, then the database-software would have abstracted it away, adding it automagic to each table-definition. Ages ago, we had non-normalized lists on our computers. Very soon we noticed things like double (or triple) entries, describing the same entity. You've probably seen a database where a customer was entered twice. Normalization (among other things) state that any record in the table should have a key to uniquely identify it. Follow the normalization-rules, and you end up with some tables having a very large compound key. Still, I consider that my primary key, that's the collection of fields that I can tell the end-user to look at to differentiate between the records he sees. Each record describes a "thing" in the real world. A table is a collection of those thingies, and a user simply needs to be able to see which record describes which thingy. I need to uniquely identify them too, hence an additional surrogate-key. This can be an auto-increment, a GUID, or a varchar. I don't care, the surrogate doesn't hold information, it holds a pointer. What I do care about is that I too have a unique field to work with in code. I never need to print it's ID on screen, as the user will never, ever, ever use it to identify a record. Still, it pays to have a simple one-field alternate key, as it keeps our source-code clean.
Stewart Judson wrote:
I can see advantages & disadvantages in auto-increment as PK & unique identifying info as PK, but neither really 'wins' consistently.
Any arguments against using them both?
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]
Cheers Eddy It's the trade-off between the long compound key and the simplicity of the id (but then, as you have said, it can be uniquely indexed against the id without being the primary key). A proper compound key does avoid the problem of duplicates amongst things that should be unique (I think this will get more weight in my mind in future) Which leaves me with only look-ups that are basically a description, but you don't want to have the description as the foreign-key, or you are saving no storage space, so you put an Id column on there to use as foreign key. Now, the above argument about uniqueness for the description can be used. I think you have convinced me - just need to stop thinking 'We need a thing, better have a thing table; first field thing.id, a primary key! (not thing.thingid, but this is where we started ;) ) Regards, Stewart
-
I must say that I hate that too. And it is even worse when the fields get too long and so they start to abbreviate parts of the name (be it of the table name or the column name)... it makes it impossible to create C# code that automatically generates queries without using alternative methods to say: Hey... Id becomes "SOME_ABBV_TB_ID".
I can think of something worse ... I'm having to deal with a database (designed by someone else I hasten to add) where table names and column names have spaces in them e.g. [Current Sterling Rate]. :( Drives me nuts. But on a positive side - at least I don't have to hit the Shift key again to get the []
-
Cheers Eddy It's the trade-off between the long compound key and the simplicity of the id (but then, as you have said, it can be uniquely indexed against the id without being the primary key). A proper compound key does avoid the problem of duplicates amongst things that should be unique (I think this will get more weight in my mind in future) Which leaves me with only look-ups that are basically a description, but you don't want to have the description as the foreign-key, or you are saving no storage space, so you put an Id column on there to use as foreign key. Now, the above argument about uniqueness for the description can be used. I think you have convinced me - just need to stop thinking 'We need a thing, better have a thing table; first field thing.id, a primary key! (not thing.thingid, but this is where we started ;) ) Regards, Stewart
Stewart Judson wrote:
Which leaves me with only look-ups that are basically a description
There's no sweet way of preventing duplicates there; the only alternative that has been given to me, was to replace them with a varchar-field in the table that originally referenced them. Sounded weird at first, until I saw the implementation - it did a
SELECT DISTINCTon that column, showing the results in a drop-down. That's not always a usefull alternative, but it's nice to have options.Stewart Judson wrote:
I think you have convinced me - just need to stop thinking 'We need a thing, better have a thing table; first field thing.id, a primary key! (not thing.thingid, but this is where we started ;) )
Start thinking "how will the user differentiate between his real-life thingies, and can we use those properties to uniquely identify a thingy-record on screen" :)
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]
-
Jörgen Andersson wrote:
an entity should have the same name everywhere is a good rule to follow, but not at any cost
Correct. The name should give some context not just datatype information; e.g. why is the particular User associated with the current record? What part does he play in this little drama?
I just remembered, if your database is ISO SQL-92 compliant there is yet another point in using the same name of your ID fields. Check this out:
SELECT *
FROM CUSTOMERS
JOIN ORDERS
USING CustomerIDThis doesn't work on SQL Server or Sybase.
"The ones who care enough to do it right care too much to compromise." Matthew Faithfull