Sql – Naming of ID columns in database tables


I was wondering peoples opinions on the naming of ID columns in database tables.

If I have a table called Invoices with a primary key of an identity column I would call that column InvoiceID so that I would not conflict with other tables and it's obvious what it is.

Where I am workind current they have called all ID columns ID.

So they would do the following:

,   il.ID 
    Invoices i
    Left Join InvoiceLines il
        on i.ID = il.InvoiceID

Now, I see a few problems here:
1. You would need to alias the columns on the select
2. ID = InvoiceID does not fit in my brain
3. If you did not alias the tables and referred to InvoiceID is it obvious what table it is on?

What are other peoples thoughts on the topic?

Best Solution

I always prefered ID to TableName + ID for the id column and then TableName + ID for a foreign key. That way all tables have a the same name for the id field and there isn't a redundant description. This seems simpler to me because all the tables have the same primary key field name.

As far as joining tables and not knowing which Id field belongs to which table, in my opinion the query should be written to handle this situation. Where I work, we always prefece the fields we use in a statement with the table/table alias.

Related Question