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

Question

Are you prefixing each field in a table with an abbreviated table name?

Example:

Table: User Fields: user_id user_name user_password 

Or do you name your fields minimally?

 Fields: id name password 

If you used both options, then which format do you find most useful in the long run?

Edit: This question does not seem to have a definite answer, and both sides present good points. But for a long time I left the question open and perhaps it was time to mark one answer, as is customary. Therefore, I mark the highest voted adopted.

Answer 1

Do not do this. It is redundant and frustrating in the long run.

The only field you could apply this to could be id , because user_id will obviously be the identifier of the user, and this will make it easier to write joins in SQL. But I wouldn’t even do that.

Answer 2

if you do this, you will end up writing queries, for example:

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

instead

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

so the IMO answer to your question is clear enough.

Answer 3

You no longer need to do this, and you really should not do this. The only exception that saua pointed out could be an identifier field to search for clarity of joins.

The concept of prefix field names with a table name comes from the old days of obsolete systems, when each field of the entire database must be unique.

So, if you are not dealing with legacy systems that require each field in the entire database to have a unique name; do not do this.

Answer 4

I would not do that. If you need information to which the table belongs, you can always write your queries as

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

But imagine that you decided for some reason to rename one of your tables (possibly from "user" to "client"). You will also need to rename all fields to stay consistent.

My opinion: there is no good reason why you should do this, and a few good reasons not to.

Answer 5

We also do not use prefixes with an abbreviated table, and I do not recommend it either.

However, there is one situation in which we do: reserve fields .

  eg OH_Reserve_Field_Alpha3 in table ORDER_HEADER 

A brief background: our database contains 250+ tables, and we insert most of them into the backup columns in order to use them for future implementations of functions. As you can imagine, without a prefix, you will have 50 Reserve_Field_Alpha3 with a completely different value, but with the same name in the whole code. It is already difficult, as it is now, but without prefixes it would be worse.

Answer 6

Binding a prefix to column names can be good practice. If you work with an official (and probably large) database, and you pay attention to ISO 11179 (in particular, the concept of data element names), then it is useful to place the full three (or four) part names: Object - Property - Term representation. (The fourth optional part is the qualifier.) For example, "user_first_name". So you have consistency between your data dictionary and database schema. I would not do this for small databases for reasons already commented out, but in a complex scheme this reduces the risk of error.

Answer 7

When I add the "serial number" field to the table that I like to add to the prefix, so I do not need to assign the ordinal fields from other tables in JOINS. This is convenient for JOINS sometimes ... not sure if I saw other benefits.

MediaWiki (Wikipiedia software) uses this agreement. Download source. They are limited to a two-character prefix.

I do not recommend this practice. For most databases, this is not necessary.

Answer 8

I would recommend sticking with table aliases, for example:

 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 

Benefits:

• maintaining an easily displayed list of selected fields and which tables you take them from

• avoid typing long or hard-to-understand table names and replacing them with a short and friendly name (which you should have done when creating the table)

• make connections that are read (your example will 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 this will mean u instead of the user, and instead of the avatar, it will shorten your request

Answer 9

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

Answer 10

It is good to specify the fields in this way (minimally), but for the primary key and signature / name. If you consistently name your entire primary key as ID and name as Name, the construction of the request degenerates into redundant 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 title / name of an entity is the norm, and not an exception, I call my primary key in full form, and for the caption / name field the same name as the 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 '' ); 

Thus, your request will not have extra 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 11

Personally, in the "user" table, my column will simply be "id".

But any foriegn key columns in different tables pointing to this column, I would call the column user_id.

so you can get something like this:

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

Answer 12

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

Even if you select from several tables, this gives you only the advantage of non-prefix ambiguous fields with the table name. But

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

not much different from

 SELECT user_name, image_name FROM user, image 

The advantage of havong in the ambiguous fields of your queries is quickly consumed due to the overhead of having to enter a table name every time you use a column name.

Answer 13

This is an amazing practice:

• You see what each field means, with the least in what area. You cannot understand what amount ( transactions , incomes ) means - unless they are xac_amount and inc_amount . Most query tools do not display an alias along with the field name.
• You can use table aliases, of course. But SQL does not require them, and Murphy’s Law, if it is not required, will not be used. There is no standard there, so one developer use x as an alias for transaction , another will use tran , etc.

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

Answer 14

If you use UNIQUE PREFIX for each table, then

• No need to use an alias for joins (except self-join)
• All columns in the database must be unique by name
• You can easily identify the table from the column name itself (from output or query selection)