sql server field naming conventions

Question

i have a database that has 50 tables and all the tables have primary key on a field named ID. so example, Employee.ID, Customer.ID, order.ID, every single table has ID as its primary key. should it not be Employee.Employee_ID, Customer.Customer_ID and so on? is there any drawback of using ID as name of every ID field in each table? if so please explain or give link to explanation

Answer 1

I'd use Employee.Id and Customer.Id with the qualifying table names. Employee.EmployeeId and Customer.CustomerId seem a bit redundant to me.

Answer 2

I typically follow the same naming conventions (Entity.Id rather than Entity.EntityId).

If you think of your tables as entities, adding the table name to the ID field is redundant, forces you to write more code, and makes your queries harder to read.

Answer 3

There are different schools of thought on this. Personally, I prefer to use identical column names only for columns that have primary key / foreign key relationships -- it helps make it easier to write complex joins.

Also, the apparent redundancy disappears when you use table aliases. For example:

SELECT o.OrderDate, c.CustomerName
    FROM Customers c
    INNER JOIN Orders o ON o.CustomerID = c.CustomerID

Answer 4

It's largely a matter of personal style.

However, if your queries always use appropriate aliases for your tables, and you always use aliases when referencing columns, then it's not too bad.

Please don't fall into the habit (as many do) of saying "There's only one Customer_EmployeeID field in my query, so I'll leave off the alias". It's really ordinary, and drives SQL people crazy when they look at code that's done this way (you end up wanting to query sys.columns to see which table contains a column called latest_status).

So if you're writing your queries nicely, it shouldn't really matter how you name your columns, so long as you're consistent. Using a plain old "ID" for any integer identity field is just fine, so long as that's what you always do.