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

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 column 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...