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.