Naming table columns with a table name prefix good Idea or bad?

Question

Below are two identical tables with different column naming conventions, which are better and why?

<<User>> id name age 

VS

 <<User>> user_id user_name user_age 

The obvious advantage that I see is when it comes to joining tables, then the same column name of two different tables can be overridden if not properly superimposed on "AS". But with the latter there is no such need, but still I find many projects using the first structure? Why is this?

Answer 1

This is not only the answer, but this is how I created the database structure:

• table names are uppercase and plural: Users , Documents , Products
• lowercase column names
• primary keys contain the singular prefix of the table: user_id , document_id
• foreign keys have the same name as the primary keys that they reference

So, for example, in the case of users and documents:

 Users Documents -------- ---------- user_id PK ----. document_id PK nickname `----- user_id FK hash title content 

In such a table, to select all document names for a user with an alias = "wintermute", you should write:

 SELECT Documents.title AS title FROM Documents LEFT JOIN Users USING(user_id) WHERE nickname = 'wintermute' 

Answer 2

I think I personally prefer

 <<User>> id name age 

I think, because SELECT user.id, user.name FROM user is quite readable and understandable. I do not see much benefit from overly detailed user.user_id . In addition, when writing object-oriented code, we would not call the user identifier user_id, so why do this in the database?

 A clear advantage I see is when it comes to joining tables same column name of two different tables can get overlap if not aliased properly with 'AS' 

I see your point, but I write it

 SELECT user_id FROM user INNER JOIN account ON account.account_id = user.account_id 

better than

 SELECT user.id FROM user INNER JOIN account ON account.id = user.account_id 

In the first example, if the account table also has a user_id field, we still have to prefix SELECT user_id FROM... with SELECT user.user_id FROM...

As far as I can see, the second example is much better.

Answer 3

I personally do not use prefixes. Not for columns, not for tables ( tblSomething is just a funny name).

One exception (there is always one, isn’t it?) For ID columns.

While all other column names are usually strongly tied to the table itself and can easily be seen in the SQL statement without a prefix, the identifier columns are different.

So I often find myself using something like: user_id, firstname, lastname

Answer 4

This style came from Oracle hackers in the old days when dinas ruled the earth. DO NOT USE IT . It is painful and ugly. I categorically reject this practice.

Answer 5

In a recent database design class, my lecturer encouraged prefix convention. He just looked rude.

Answer 6

We use a similar agreement as your first example at work, on our Oracle boxes, and frankly, I hate it.

For me, I think this is massively redundant; but I agree that in large requests it can help with debugging, it is very visually obvious.

Which one is better? There is no good answer; which makes you feel less bad! This is the only correct answer.

Answer 7

Complex systems often use a metadata repository. The metadata repository requires data items to be unique. So, having, say, 147 tables with the same data item name (id), but different data definitions (user ID, account ID, etc.) will not work.

From this point of view, the "user" part of "user_id" is not the name of the table in which "user_id" is located. Instead, "user" is an object class class, and "id" is one property of this object class. I use the object, class and property not in the sense of OO programming, but in the ISO 11179 Standard.

You can find ISO 11179 projects online. I think it is worth reading.

If you use multiple columns, all named "id", in one query, you should at least partially use an alias. What are the odds that 40 different programmers working for 10 or 15 years will use the same alias every time? On large systems, it is important to know that the user ID number will be called "user_id" wherever it appears. (With the possible exception of self-join.) I worked on systems where the user ID was "user_id", "usr_id", "u_id", "u_no", "u_uno", "u_n", "u", "uid " etc. Takes time. Time that could be better used.