Why is prefixing column names considered bad practice?

Question

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.

Looks like who made these rules wasn't aware of the aliasing function. — ba__friend, Jun 20 '11 at 19:16
A similar kind of post here http://stackoverflow.com/questions/465136/is-prefixing-each-field-name-in-a-table-with-abbreviated-table-name-a-good-practi — , Jun 20 '11 at 19:26
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? — , Jun 20 '11 at 19:33
Lets say we have two tables t1 and t2 both have columns named Name. One scenario where these needs to be joined. SELECT t1.Name AS NameFromT1, t2.Name AS NameFromT2 FROM t1 JOIN t2 ON t1.Id = t2.ParentId or something like that. — ba__friend, Jun 20 '11 at 19:35
Underscores (compared to PascalCase) are one extra keystroke on a key far from the home row with the weakest finger. That's why I hate them. As for prefixes, how about the database I worked with where every foreign key had to be prefixed with frnkey? — ErikE, Jun 20 '11 at 20:00
It's curious that every time I see this kind of question asked the answers assume that the data never leaves the database. Am I the only person who has to maintain code which binds data to e.g. an ASP.Net DataGrid using an ORM which translates column C of table T to property C of class T? — Peter Taylor, Jun 21 '11 at 12:20
Be glad they don't prefix it with the type of field (i.e. Hungarian Notation for Databases). I once interviewed at a place that insisted they did things this way so you knew at a glance what type of DB field you were dealing with; I almost facepalmed. — Wayne Molina, Jun 21 '11 at 12:26
@Peter Taylor - I use entity framework. Where are you going with that statement? — P.Brian.Mackey, Jun 21 '11 at 13:13
@P.Brian.Mackey, all these comments about "you can use T.C in your SELECTs" are completely irrelevant when you're looking at <asp:DataGridColumn DataField='C' ... />. — Peter Taylor, Jun 21 '11 at 13:41
The CRM the company I work for uses has a database that uses this convention. It has its upsides and downsides, but overall, I think it is a poor design because of things people have already said: lots of extra typing, the ability to alias column names, hard to read and interpret columns. I think well-named columns are good enough...of course, just my opinion. — Tim Claason, Jun 21 '11 at 14:19
@WayneM the database and code I'm dealing with uses hungarian notation everywhere. It's not even standard, she decided to use hungarian notation 3/4th of the time, and only used variant variables in her code which proceeded to prefix with the keyword "vnt". She's like the Lex Luthor of programmers. — Liso22, Dec 11 '15 at 17:24

score 47 · Accepted Answer · answered Jun 20 '11 at 19:24

47

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.

answered Jun 20 '11 at 19:24

HLGEM

28,759

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 Jun 20 '11 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 May 21 '12 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 May 21 '12 at 17:45
@HLGEM, where's your sense of adventure? :) Good points. – programmer May 21 '12 at 19:07
7

@jason, I'm a database person, we are widely known to have no sense of adventure! – HLGEM May 21 '12 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 Oct 09 '13 at 19:12
2

In a very slightly different flavor, I always name the primary key of my tables "ID". Since my queries are invariably aliased, this doesn't cause any confusion. In fact I find it to be more concise and I always know when I'm working with a PK vs an FK column. EX: INNER JOIN Customer c ON c.ID = o.CustomerID. I'd like to hear more on the mistakes you've seen. Will be googling in the meantime.
For reporting, 99% of reports involve joined tables. I select the FK column rather than ID column. No clutter of aliasing columns in the select.
– Taylor Dec 17 '15 at 00:13
@Taylor, the most common mistakes have to do with bad joins because it is really easy to accidentally join to TableC.id when you meant to join to TableB.id. Then you have a data integrity mess. – HLGEM Jan 23 '18 at 20:49
1

@HLGEM If you have any two tables being joined, just typing in ID would ALWAYS be ambiguous so it should just be a harmless syntax error every time. I don't see the quandary. Of course, I've no experience with this since the DB's I work on prefix EVERY column with table name and ... well that's worse than you can possibly imagine which is why I think even prefixing ID would be bad if I had the option. – ebyrob May 21 '18 at 16:52

score 19 · Answer 2 · edited Apr 07 '14 at 11:56

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%'

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, Sep 26 '12 at 14:37
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, Jan 06 '15 at 20:11

score 9 · Answer 3 · answered Jun 20 '11 at 19:23

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.

score 5 · Answer 4 · answered Jun 20 '11 at 19:27

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

answered Jun 20 '11 at 19:27

gbn

2,467

3

Definitely the first one... :) – ErikE Jun 20 '11 at 19:48
3

What about SELECT name, salary FROM dbo.Person? :P – cHao Jun 21 '11 at 03:13
1

@cHao: try creating a view WITH SCHEMABINDING on that... – gbn Jun 21 '11 at 04: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 Jun 21 '11 at 04:39
1

Relevant documentation (http://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 Jun 21 '11 at 05:10
@cHao: this is what I was referring to. It's still best practice generally, and very few SELECTs are on a single table – gbn Jun 21 '11 at 05:18

score 4 · Answer 5 · answered Jun 20 '11 at 19:26

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.

score 2 · Answer 6 · answered May 21 '12 at 15:21

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?

score 2 · Answer 7 · answered Jun 20 '11 at 19:31

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.

score 1 · Answer 8 · answered Jun 20 '11 at 19:16

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.

score 1 · Answer 9 · answered Jun 21 '11 at 14:09

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.

score 0 · Answer 10 · answered Jun 21 '11 at 09:47

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.

score 0 · Answer 11 · answered Jun 21 '11 at 13:57

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.

Why is prefixing column names considered bad practice?

11 Answers11