Is prefixing each field name in a table with abbreviated table name a good practice?

Question

Do you prefix each field in a table with abbreviated table name?

Example:

Table: User

Fields:
user_id
user_name
user_password

Or do you do you name your fields minimally?

Fields:
id
name
password

If you have used both, then which format you feel helped you most in the long run?

Edit: This question doesn't seem to have a definite answer and both side presents good points. But I have kept the question open for too long time and maybe it's time to mark one answer as accepted. I am therefore marking the highest voted one as accepted.

Answer 1

Don't do that. It's redundant and leads to frustration in the long run.

The only field, where you could apply this might be id, because user_id would obviously be the id of the user and it would simplify writing joins in SQL. But I wouldn't even do that.

Answer 2

if you do it you will end up writing queries like:

SELECT user.user_name, user.user_password, user.user_firstname ...

instead of

SELECT user.name, user.password, user.firstname

so IMO the answer to your question is quite clear.

Answer 3

You do not need to do it anymore, and you really should not. The only exception as saua pointed out could be the ID field for the seek of clarity of joins.

The concept of prefixing field names with the table name comes from the old time of legacy systems when each field across the whole database needed to be unique.

So unless you are dealing with legacy systems which require that each field across the whole database has unique name; do not do it.

Answer 4

I wouldn't do it. If you want the information which table a field belongs to, you can always write your queries as

select user.id, user.name from user where ...

But imagine you decide for whatever reason to rename one of your tables (maybe from 'user' to 'customer'). You would have to rename all fields as well, to remain consistent.

My opinion: There is no good reason why you should do it, and several good reasons not to do it.

Answer 5

Putting the prefix on column names can be a good practice. If you're working on a formal (and probably large) database and you're paying any attention to ISO 11179 (particularly the concept of data element names), then it's good to put the full three (or four) part name in: Object - Property - Representation Term. (The fourth optional part is a qualifier.) For example, "user_first_name". That way you have consistency between your data dictionary and the database schema. I wouldn't do this for smaller databases for the reasons already commented on, but in a complex schema this reduces some risk for error.

Answer 6

I'd recommend sticking with table aliases, like:

SELECT 
    user.id,
    user.email, 
    user.firstname, 
    user.secondname,
    avatar.filename
FROM
    pain_in_the_butt_table_name user
LEFT JOIN
    table_with_the_avatars avatar
ON avatar.user_id = user.id

The advantages:

• maintaining an easily comprehendable list of fields you select, and which tables you take them from

• avoid typing long or hard to comprehend table names and replace them with a short and understandable name (which you should have done when creating the table)

• perform joins that are readable (your example would read:

LEFT JOIN table_with_the_avatars.user_id ON user.user_id = table_with_the_avatars.avatars_user_i

• create even shorter aliases, in my example that would mean u instead of user, and a instead of avatar, shortening your query

Answer 7

We also don't use abbreviated table prefixes normally and I wouldn't advice it either.

There's however one situation where we do: reserve fields.

 e.g. OH_Reserve_Field_Alpha3 in table ORDER_HEADER

Short background: Our database has 250+ tables and we put in most of them reserve columns to use them for future feature implementations. As you can imagine, without prefixing you would end up having 50 Reserve_Field_Alpha3's with totally different meaning but same name throughout your code. It's already hard as it's now, but without prefixes it would be worse.

Answer 8

When I add the field "ordinal" to a table I like to add in a prefix so I don't have to alias ordinal fields from other tables in JOINS. It's handy for JOINS sometimes... not sure I have seen other benefits.

MediaWiki (the Wikipiedia software) uses that convention. Download the source. They limit themselves to a two character prefix.

I don't recommend the practice though. For most databases its not necessary.

Answer 9

It's ok to name fields that way(minimally), but for primary key and captions/name. If you consistently name all your primary key as ID, and name as Name, constructing query will degenerate into superfluous aliases:

select i.id as invoice_id

v.id as vendor_id, p.id as product_id, 
v.name as vendor, p.name as product, b.name as branch, c.name as parcel,

i.total_amount,
i.discount,
i.invoice_date

from invoice i
join product p on i.product_id = p.id
join vendor v on i.vendor_id = v.id
join branch b on i.branch_id = b.id
join parcel c on i.parcel_id = c.id

As joining tables and displaying the entity's caption/name is the norm rather than exception, I name my primary key in full form, and for caption/name field, the same name as table name.

create table product
(
product_id uuid not null, -- primary key
product text not null,
bar_code text not null default '',
rfid_code text  not null default '',
current_qty int default 0
);

create table vendor
(
vendor_id uuid not null, -- primary key
vendor text not null,
is_active boolean not null default true
);

create table branch
(
branch_id uuid not null, -- primary key
branch text not null,
sub_branch_of_id uuid,
current_sales money not null default 0,        
);

create table user
(
user_id uuid not null, -- primary key
user text not null,
password text not null default ''
);

So your query won't have superfluous aliases:

select i.invoice_id, p.product_id, v.vendor, p.product, b.branch, c.parcel,

i.total_amount,
i.discount,
i.invoice_date

from invoice i
join product p on o.product_code = p.product_code
join vendor v on o.vendor_code = v.vendor_code
join branch b on o.branch_code = b.branch_code
join parcel c on o.parcel_code = c.parcel_code

Answer 10

Personally, on the 'user' table, my column would just be 'id'.

But any foriegn key columns on different tables pointing to that column, I'd call the column 'user_id'.

so you might end up with something like this :

select  *
from    order
        inner join user
            on user.id=order.user_id

Answer 11

For all the reasons given, I don't think this is a good idea. Besides, you don't prefix all the methods in your classes with the class names, do you? So why do it for database objects?

Answer 12

If you are using a UNIQUE PREFIX for each table, then

• No need to use an alias for joins (except self join)
• All the columns in the database should be unique in name
• You can easily identity the table from column name itself(from a output or select query)

Answer 13

The prefix variant just takes longer to write and makes it harder to read sql statements with many fields.

Even when you are selecting from several tables, this gives you only the benefit of not having to prefix ambiguous fields with the table name. But

SELECT user.name, image.name FROM user, image

is not very different from

SELECT user_name, image_name FROM user, image

The benefit of havong no ambiguous fields in your queries is quickly eaten up by the overhead of having to type the table name each time you are using a column name.

Answer 14

It's an awesome practise:

1. You see what every field means, at least at what domain it is. You can't figure out what amount means (transactions, incomes) — unless they are xac_amount and inc_amount. Most query tools do not output alias along with the field name.
2. You may use tables aliases, for sure. But SQL does not require them, and by Murphy's law, if it's not required, it won't be used. There is no standard, so one developer will use x as an alias for transaction, another one will use tran and so on.

In fact, prefixes are just forced tables aliases, so you can easily see what field belongs to what table.