Is it a good practice to use table prefix while designing database?

Question

Is it a good practice to use table prefix while designing database?

For table like, "user_detail"
I use column names like ud_name, ud_email etc.

I do this because I want to keep unique name for each column (in every table) so that later there wont be any problem of conflicting column name while joining.

Although some of my coleagues are having problem reading column names (I dont know why), and they suggest that Name should be as simple and readable as possible so I shouldnt use any prefix.

What is the standard nomenclature which is followed for database design? is it bad to have prefixes? Please support your answer with some link.

Thank you
Enjoy Made :)

Answer 1

Several posters, including the orignial poster, have touted the benefit of unique column names as if it is a natural and obvious innate good. I ask: why would uniqueness in column names be good? Forty years ago there were mainframe data storage technologies that required unique column names. This polluted the design of dBase on PCs twenty years ago and more. Since the dominant data storage model has become relational DBMS there is no requirement for, and I would argue, no good reason for unique column names.

There is a concept known as "domain name integrity" which simply means that a column name is meaningless without the context of its containing table, just as the table name is meaningless without the context of its owning schema and database etc.

You can't reference a column without referencing its containing table in SQL. If you think the table name is too long and you don't want to type it out all the time you can alias it for your select list.

If you use unique column names that are made by jamming the table name or an abbreviation of the table name into the column name, how is this different than using the table name (or an abbreviated alias) with a dot instead of your prefix with an underscore? I argue that it isn't. The difference is that forcing an indicator of the parent into the name of the child just makes that child name less readable and inevitably results in much more redundant and less readable SQL.

I discussed my preferred naming conventions in this post on SQL Mag forums.

The best names are the shortest natural names that clearly describe the content or meaning of the data they contain. These names can and must be taken in the context of the objects that contain them. Trying to impose uniqueness or using other conventions like Hungarianizing tables and columns just adds clutter which makes your database harder to read and understand, which in turn makes your system less supportable.

Answer 2

The question is rather subjective and argumentative...

Personally, I lean on the side of not using prefixes and sticking to reasonable names.

Prefixes tend to quickly become a pain to write. And when they'd seem useful, I find it's better to alias the table, the columns, or both accordingly, i.e.:

select ud.name as ud_name,
       ud.email as ud_email
from user_detail as ud;

Also, I've never had to deal with/configure DB prefixes in the context of an app that uses an ORM but, if anything, I'd guess it's not very pretty.

Answer 3

This is a similar conversation to the whole "Hungarian notation" debate - http://en.wikipedia.org/wiki/Hungarian_notation.

Whatever you do, I'd recommend your entire team use the same conventions for naming your database entities - even with a standard you don't agree with.

I like http://www.interaktonline.com/support/articles/details/Design+Your+Database-Database+Naming+Convention.html?id_art=24&id_asc=221 as a standard.

Answer 4

Its better to go with the thing you did

column name like tableprefix_column -- reason

1. it makes column name unique and you do not have conflict with the other table column name.

2. if you go for the just name like columnname -- name than you need to be carefull and you need to write tablename.columnname when you write query and also need to assign the alias for that column in query like tablename.columnname as xyzname

Answer 5

Table prefix is not the only solution , but you are right when saying that column names should be unique in your database! Our standard slightly differs than yours, where we use the table name as a suffix (see infra), but the result is the same: each column holds a unique name. And the most valuable argument for this unicity principle is with views and reporting: when joining multiple tables, you'll surely manipulate columns that have a similar signification, like 'description', 'observation', 'code', or 'number'. If, at this stage, your columns have the same name, you will have to rename them with aliases, making your views messy and difficult to understand and maintain.

Here are a few of our examples, where field name is built out of the nature of data (date, time, code, id) + the table description, making de facto each column's denomination unique:

Tbl_Attendance        contains attendance data
    id_Attendance     is the identifier
    id_Person         is the foreign key to Tbl_person.id_Person
    id_Zone           is the foreign key to Tbl_Zone.id_Zone
    dateAttendance    is the attendance date
    timeInAttendance  is the check in time
    timeOutAttendance is the check out time

Tbl_PaySlip           contains informations about Payslips
    id_Payslip        is the identifier
    id_Person         is the foreign key to Tbl_person.id_Person
    id_Company        is the foreign key to Tbl_Company.id_Company
    dateStartPayslip  is the starting date of the payslip
    dateEndPayslip    is the ending date of the payslip
    codePayslip       is the unique code of the payslip, built out from company code, person code, period code