Why do they insist on repeating the name of the table in the column name?

Gary Huck

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.

Paulo Zemek

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".

_beauw_

I use the verbose name for foreign keys in other tables. Every table's own key ought to be simply id. Excess verbosity in naming is one of my pet peeves. .NET programmers in particular seem incapable of making up variable names that don't require me to hit the "Shift" key at least 3+ times.

Ennis Ray Lynch Jr

My personal preference:

create table customer
id,
name,
dateOfBirth, etc

I 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,
etc

It 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.id

vs:

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.id

With 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

tgrt

It makes joins clearer in regards to key fields, and I assume that's what you're talking about. Although I wouldn't use such an unwieldly name in the first place. For example, I'd likely use AttributeTypeId. That would then match nicely as a foreign key which you'd name similarly.

Lost User

I hate it when they do that on objects: Customer.CustomerCatagory Customer.CustomerType Why not just Customer.Catagory? Why not just Customer.Type?

BobJanova

No, it is not just you. That annoys me too (along with the same thing in code, e.g. myCustomer.getCustomerId()). If you need to qualify it at any point with its context (for example within a join), you can say table.field anyway. For data in this table I just use the obvious name (e.g. 'name', 'address', etc), and for foreign key lookups I use the name of the target table (e.g. a column in user called 'role' which looks up into a table called 'role'), unless it's not a lookup to the primary key of the other table.

Lost User

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.

Corporal Agarn

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:

Lost User

tgrt wrote:

It makes joins clearer in regards to key fields

It doesn't; it clutters up the statement with redundant text.

SELECT *
FROM Employee e
JOIN Department d ON d.Id = e.fk_Department

versus

SELECT *
FROM Employee e
JOIN Department d ON d.DepartmentId = e.Employee_fk_Department

..worse are the people who add the text "table" to a tablename. ..worse than that, tools that make it easy to model data; any idiot can make a list, and any idiot does. Solving the same kind of errors, over and over. "The same customer is recorded three times in the database, and someone needs to correct that." Bring me an MS-Access database, without any relationships defined, without primary keys, and with every field being a varchar (memo!).

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]

tgrt

You're talking about something different. The second query would be:

SELECT *
FROM Employee e
JOIN Department d ON d.DepartmentId = e.DepartmentId

I'm leaving the asterisk for the sake of brevity.

Gary Huck

Ow. My all time favorite, was back in the days of VMS (DEC). Their RMS system was a great file system (data files all ended with ".DAT"). A youngster created a file named "DATA.DAT" ... you can guess the directory name :)

Rama Krishna Vavilala

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.

Jorgen Andersson

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 Customertable and the Ordertable. There must never be any uncertainties. It's also ISO-11179 compliant.

People say nothing is impossible, but I do nothing every day.

Jorgen Andersson

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.

Worried Brown Eyes

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

Lost User

How is that more clear than without the table-name?

SELECT *
FROM Employee e
JOIN Department d ON d.DepartmentId = e.DepartmentId

SELECT *
FROM Employee e
JOIN Department d ON d.Id = e.fk_Department

One 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.Id

It'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.HumanId

Enjoy :)

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]

Jorgen Andersson

To paraphrase Joe Celko: There are a lot of furniture makers out there.

People say nothing is impossible, but I do nothing every day.

Matthew Faithfull

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)

Lost User

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![^]