33

According to a popular SO post is it considered a bad practice to prefix table names. At my company every column is prefixed by a table name. This is difficult for me to read. I'm not sure the reason, but this naming is actually the company standard. I can't stand the naming convention, but I have no documentation to back up my reasoning.

All I know is that reading AdventureWorks is much simpler. In this our company DB you will see a table, Person and it might have column name:

Person_First_Name
or maybe even
Person_Person_First_Name (don't ask me why you see person 2x)

Why is it considered a bad practice to pre-fix column names? Are underscores considered evil in SQL as well?

Note: I own Pro SQL Server 2008 - Relation Database design and implementation. References to that book are welcome.

Improve this question
13
  • 4
    Looks like who made these rules wasn't aware of the aliasing function.
    – ba__friend
    Commented Jun 20, 2011 at 19:16
  • @Daniel Pryden - I used poor wording. Question updated.
    – P.Brian.Mackey
    Commented Jun 20, 2011 at 19:16
  • A similar kind of post here stackoverflow.com/questions/465136/…
    – Rahul
    Commented Jun 20, 2011 at 19:26
  • @ba__friend - Can you give me some detail on that comment?
    – P.Brian.Mackey
    Commented Jun 20, 2011 at 19:29
  • 1
    The essential word you have used is Standard. If you change a standard practice, you have inconsistency. Do you honestly feel that any change from this standard practice, is worth that resulting inconsistency? Is the status-quo really worse than that?
    – Dems
    Commented Jun 20, 2011 at 19:33

11 Answers 11

Reset to default
49

Underscores are not evil just harder to type. What is bad is changing standards midstream without fixing all the existing objects. Now you have personId, Person_id, etc. and can't remember which table uses the underscores or not. Consistency in naming (even if you personally don't like the names) helps make it easier to code.

Personally the only place I feel the need to use the tablename in a column is on the ID column (the use of just ID is an antipattern in database design as anyone who has done extensive reporting queries can tell you. It's so much fun to rename 12 columns in your query every time you write a report.) That also makes it easier to immediately know the FKs in other tables as they have the same name.

However, in a mature database, it is more work than it is worth to change an existing standard. Just accept that is the standard and move on, there are far more critical things that need to be fixed first.

Improve this answer
9
  • 4
    True, but a database with no consistent naming standard is far harder to query than one with a poor standard that is consistently applied.
    – HLGEM
    Commented Jun 20, 2011 at 19:36
  • 2
    I partially agree. If the database is HUGE then keep the standard. But I spend my time reading code more than writing it and the table name prefixes seems like more noise to sift through. If it were me and knew I could refactor it within a few days or a week I sure would. But a lot of time you don't have that luxury and have to keep coding prodution, prodution, prodution.
    – programmer
    Commented May 21, 2012 at 17:37
  • 3
    @jason, you may break more than you think doing that. Databases are often accessed by many things the application programmer is not aware of. Things like data imports from clients or other dbs and exports to clients and or datawarehouses, other applications, reports, etc. You can get used to reading any standard in a matter of hours, and refactoring away from an approved company standard without an agreement to move to a new standard would get you fired most places. Honestly, unless the database is in its infancy, it is better to use the existing standard whether you like it or not.
    – HLGEM
    Commented May 21, 2012 at 17:45
  • 7
    @jason, I'm a database person, we are widely known to have no sense of adventure!
    – HLGEM
    Commented May 21, 2012 at 20:29
  • 2
    Completely agree with TableName.Id being an antipattern. Have worked within and without that pattern long enough that I can confidentelly say mistakes/confusion occurs with TableName.Id that don't occur with TableName.TableNameId
    – AaronLS
    Commented Oct 9, 2013 at 19:12
19

An argument for the column name prefixing would be preventing name "collisions" when joining multiple tables AND when the query creator doesn't use aliases.

SELECT person.name, company.name FROM person JOIN company ON ...

SELECT * FROM person JOIN company ON ...

Both queries would have two "name" columns (name_1, name_2) without "telling" to which entity it belongs. And you can never be sure of the generated column names (will it be name_2 or name_3 or ...). If you use the table name prefixing, the column names would be person_name, company_name so you know each name to which entity it belongs, plus you know that the column names will remain constant (if you're getting them in Java using JDBC for example).

Both arguments can be ignored if you use aliasing, but I think most coding conventions enforced in companies come as a consequence of many (junior) programmers not following good practices. In this case, for example, using the wildcard on a SELECT statement can cause problems without the name prefixing.

As for the underscore in table and column names, I use it extensively because I use only lowercase in names plus underscore as a separator. Using only lowercase helps distinguish identifiers from SQL keywords (which I type in all uppercase):

SELECT person_name, COUNT(bought_product) FROM bought_products WHERE person_name LIKE 'A%'
Improve this answer
2
  • 6
    I would have loved if all columns with the same information in different tables would have had exactly the same name and that whenever you use more then 1 table aliasing would be the enforced standard. Getting pretty tired of remembering to which table patid, patientid, pat_id, id_pat or ptatient_id maps.
    – Pieter B
    Commented Sep 26, 2012 at 14:37
  • 2
    I never write a production query without aliasing all the columns. It makes maintenace so much easier. Of course I write complicated reporting/export type queries with up to 10-20 joins and 30-50 columns and six months later it is hard to remember which table that column came from.
    – HLGEM
    Commented Jan 6, 2015 at 20:11
9

Adding those kind of prefixes to column names will make a table more difficult to evolve. As an example: if eventually you realize that you want/need to change the table name, you will have to modify your entire table structure (i.e., not only the name of the table, but the name of all its columns). This would also make more difficult updating the table's indexes and the code of clients querying it.

Improve this answer
5

There are exceptions if used judiciously (as per Patrick Karcher's answer on your link) for common column names (normally only ID, sometimes Name) that would be ambiguous too often.

Another best practice is to always qualify columns and objects in your queries. So column prefixes become moot and clutter your code.

Compare these: which is easiest on the eye?

SELECT P.name, P.Salary FROM dbo.Person P

SELECT Person.Name, Person.Salary FROM dbo.Person Person

SELECT dbo.Person.name, dbo.Person.Salary FROM dbo.Person

SELECT Person.Person_name, Person.Person_Salary FROM dbo.Person Person
Improve this answer
6
  • 3
    Definitely the first one... :)
    – ErikE
    Commented Jun 20, 2011 at 19:48
  • 3
    What about SELECT name, salary FROM dbo.Person? :P
    – cHao
    Commented Jun 21, 2011 at 3:13
  • 1
    @cHao: try creating a view WITH SCHEMABINDING on that...
    – gbn
    Commented Jun 21, 2011 at 4:11
  • @gbn: Works fine for me. CREATE VIEW [dbo].[vwMeritPercentage] with schemabinding AS SELECT [Performance Score] as dblMinScore, ISNULL(( SELECT TOP 1 [Performance Score] FROM dbo.Budget b WHERE b.[Performance Score] > budget.[Performance Score] ORDER BY [Performance Score] ), 100.00) as dblMaxScore, [Merit Minimum] as dblMinPercentage, [Merit Midpoint] as dblBudgetPercentage, [Merit Maximum] as dblMaxPercentage FROM dbo.Budget;
    – cHao
    Commented Jun 21, 2011 at 4:39
  • 1
    Relevant documentation (msdn.microsoft.com/en-us/library/ms187956(v=SQL.105).aspx): "When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced." Note that columns don't require dotted names (unless they are otherwise required to resolve ambiguities in the query)...only tables, views, and functions.
    – cHao
    Commented Jun 21, 2011 at 5:10
4

In general, there really don't seem to be any ubiquitous standards. The question you linked to has several high-voted answers with totally different conventions. Of course, everybody is going to defend their own standards, and it is far more important to keep consistent conventions in a project.

That said, prefixing column names seems to be overkill. You already know what table you're working with, and a situation with two columns from different tables having the same name can be easily resolved using table or column aliases.

Improve this answer
2

In TSQL you can refer to fields in the form TableName.FieldName if you want to avoid ambiguity so adding table names to field names actually takes away from readability making it TableName.TableName_FieldName or similar. I think using underscores or not is more of a personal choice. I prefer CamelCase and i use _ when i want to add a suffix or similar e.g. TableName_Temp, but that's just me.

Improve this answer
2

I once worked on a system where we decided to use short codes to prefix the columns. The PK fields used the "full table name" as a prefix, and all other columns used 2-4 characters consistently as their prefix. Each column also used a data domain as its suffix. If done consistently, it can be very nice and clean. It's nonsense that a naming standard of one type or another implicates sloppy coding. The presence of a consistent standard is what's important. I've seen a number of databases that are inconsistent because there is no clear standard, and that more than anything else would indicate to me that there may be trouble in the data structures. If the designer of a database can't even consistently name objects and their children, why would that lead me to believe there is anything consistent or thoughtful about the underlying data model, relationships, constraints, integrity, etc?

Improve this answer
1

Prefixing is bad practice because it causes the problem its designed to prevent. As you stated, its very hard to read prefixed columns. If you end up with duplicated column names in a query where you join two tables, you can resolve it their, or is a view, stored procedure or tabular user defined function that does it for you if you find yourself constantly joining particular tables.

As far as using underscored in a table name, that's a religious argument. In the end if increases visibility and makes things easier go for it. I generally would never have spaces or tables in a column or table name. However, I might make an exception for tables or views that were only used by a reporting package, or exported to a CSV file.

Improve this answer
1

Many databases have strict limits on the number of characters in a column name (i.e. Oracle).

If you are working with a database that allows long column names, but you later decide that you want to migrate that structure on to another database system, the prefixes will increase the chances that your column names will be invalid.

Although you are working with SQL Server now, nobody can predict the future, and it's possible that your software may have to work on multiple databases in the future.

Improve this answer
0

Consider writing a data dictionary (or "registry"). By that I mean a document containing all the data elements in your model: name, description, etc. It should be independent of the implementation of the model e.g. should not mention table names. How would you disambiguate names such as 'ID', 'Type' and 'Code'?

One approach is to follow the guidelines of the international standard ISO/IEC 11179 -- after all, why reinvent the wheel? The basic structure is:

[Object] [Qualifier] Property RepresentationTerm

with delimiters between the elements: SQL doesn't play nice with spaces in column names and underscore works well visually.

I have a feeling that someone in your organization was trying to follow these guidelines when they came up with element names such as Person_Person_First_Name.

The example I like to show is the UK Nation Health Service (NHS) data dictionary.

So I don't think the naming convention you have to work with sounds too bad.

On implementation e.g. in SQL, some folk like to omit the Object, Qualifier and Property sub-elements when the table name provides context e.g. person_first_name becomes first_name in the Person table etc. However, the rule of thumb that a data element shouldn't change its name simply due to it's location in the physical model seems a good one. Anyone who if thinks it isn't a good idea to follow this rule should be given the task of documenting all the name variations they've used :)

You will find a nice summary of ISO 11179 in the book Joe Celko's Programming Style, including evidence for preferring underscores as delimiters in column names.

Improve this answer
0

Some find it easier in code to know what table the data came from, however, if you are talking about an object oriented system you should use the context of the name to know where it came from, in this case the table name.

Personally, table name prefixing is an indication that the developers are not very skilled and as you dig deeper you will find many other bad coding conventions and if I had to guess the app has too many tables, is buggy, etc.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.