Do you prefer verbose naming when it comes to database columns?

Question

Which is your preference?

Let's say we have a generic Product table that has an ID, a name, and a foreign key reference to a category. Would you prefer to name your table like:

CREATE TABLE Products
(
    ProductID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    CategoryID int NOT NULL FOREIGN KEY REFERENCES Categories(CategoryID),
    ProductName varchar(200) NOT NULL
)

using explicit naming for the columns (e.g. ProductName, ProductID), or something like:

CREATE TABLE Products
(
    ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    CategoryID int NOT NULL FOREIGN KEY REFERENCES Categories(ID),
    Name varchar(200) NOT NULL
)

From what I've seen, the convention in the .NET world is to be explicit -- the samples tend to use the first example, while the open source and RoR world favor the second. Personally I find the first easier to read and comprehend at first glance: select p.ProductID, p.ProductName, c.CategoryName from Categories c inner join Products p on c.CategoryID = p.CategoryID seems a lot more natural to me than select p.ID AS ProductID, p.Name AS ProductName, c.Name AS CategoryName from Categories c inner join Products p on c.ID = p.CategoryID

I suppose that given the rudimentary example I provided it's not a big deal, but how about when you are dealing with lots of data and tables? I would still find the first example to be better than the second, although possibly some combination of the two might be worth looking into (<Table>ID for the ID, but just Name for the name?). Obviously on an existing project you should follow the conventions already established, but what about for new development?

What's your preference?

Answer 1

The table name already gives the context. No need to prefix columns name with it. When joining tables use table.column syntax.

Answer 2

I'm a fan of option 3:

CREATE TABLE Products
(
    ProductId int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    CategoryId int NOT NULL FOREIGN KEY REFERENCES Categories(CategoryId),
    Name varchar(200) NOT NULL
)

So the primary key is the only table to gain the table's name as a prefix -- IMHO it makes it easier to see when a join has gone wrong. Then again, I also like using GUIDs for primary keys if there is any possibility of having to cope with a merge replication situation at any point in the future...

Answer 3

One school of thought is that a column name should represent a single domain space across the database. For example, a product name is something quite different from a company name. One might be longer than the other, etc. In that respect you should use the first method since it distinguishes between the two.

On the other hand, I agree to some degree with Think Before Coding's logic. I always use the table.column syntax, so the context should be clear.

Using the more verbose column names usually avoids conflicts with reserved words.

Then again, it can be quite redundant if every column in your product table is ProductXxxx.

In other words, I don't have an absolute preference, but I do tend more towards the verbose naming convention.

Answer 4

I prefer to stick with the short version of "ID". It's an easy to understand and follow convention that a record identifier for a table would be called "ID" and it will refer to its "own" table and not to something else (though there is sometimes no need for an "own" identifier in some tables like mappings).

Another outcome that it gives is that whenever you write a query you don't need to think and remember the exact column name. You know that as soon as it makes sense for a table to have a record identifier it could be referenced by "ID".

It simplifies things.

Answer 5

I'm usually giving each table a unique prefix. So your example would be something like

CREATE TABLE Products
(
    PROD_ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
    PROD_CAT_ID int NOT NULL FOREIGN KEY REFERENCES Categories(CAT_ID),
    PROD_NAME varchar(200) NOT NULL
)

This makes joining and selecting columns easier, because you have no name conflicts. Unless you reference the same table more than once you will not even need table name aliases (most likely).

However lately I'm starting to thing that (2) might be better, because it's closer to the naming conventions I'm using when writing code (C# in my case).

Answer 6

I stick with as short as names as possible. People prefixing the name of the table on every column makes me violent. PERSON.first_name, not PERSON.person_first_name. We know its a person, its in the person table... what else would it be?

The only time I go against this rule is for id colums, for example: PERSON.personID.

The rule is, with apologies to Einstein; to be as verbose as necessary, but no more verbose.

Answer 7

I prefer uid, gid, pid, wid, lid, etc..

Answer 8

ID is terribly confusing when you have lots of joins. I prefer to have explict names of ids that match the name of the id in the foregin key. That way you always know that customerid will join with customerid in any other table that has the column.

Never use name as a field name. It is a reserved word and thus should be avoided. Reserved keyworsds are used by the database in some fashion, using them also as filed names is just poor proactice and can create more errors than using the correct descriptive name form the start.

Answer 9

I just read a good blog post on this here: http://petereisentraut.blogspot.com/2008/06/schema-design-and-id-fields.html

Answer 10

Check also related question: Is prefixing each field name in a table with abbreviated table name a good practice?

As I mentioned in my answer; the concept of prefixing field names with the table name comes from the old time of legacy systems when each field across the whole database needed to be unique. That is not required any more by the modern systems so it is just a convention that is no longer necessary. As mentioned by Think Before Coding "The table name already gives the context. No need to prefix columns name with it"

Answer 11

I think if the column name is not unique in database, you should prefix it with the tablename. Like, ProductId, ProductName. For columns not conflicting with columns in other table, don't prefix it.

Answer 12

Option 2 is the best (for me of course :) ),
I'm a PHP programmer not a .NET one.

Answer 13

I prefer the second option because the first option, to me, seems like unnecessary repetition. Of course, I'm primarily a Ruby on Rails programmer, so I love the DRY principle. :) I found it annoying coming to C# programming because everything does use the first, explicit-but-repetitious version.