Menu
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:
Select i.ID , il.ID From 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?
217
Each column name should be unique. If two columns from different tables serving different purposes are in the same database then use some kind of prefixes that separate the two. Step-2 : Column names must not be abstract or cryptic.
Database object names, particularly column names, should be a noun describing the field or object. Avoid using words that are just data types such as text or timestamp . The latter is particularly bad as it provides zero context. Underscores separate words.
Use underscores to separate words in the field name - convert these to Pascal case in code. pk_ prefix means primary key. _id suffix means an integer, auto-increment ID. fk_ prefix means foreign key (no suffix necessary)
Column names must contain only A to Z, 0 to 9, and underscore (_) characters. Column names can contain multiple underscores. The column name must not be very generic. Avoid words such as term, multiplier, description, name, code, and so on.
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.
183
Theres been a nerd fight about this very thing in my company of late. The advent of LINQ has made the redundant tablename+ID pattern even more obviously silly in my eyes. I think most reasonable people will say that if you're hand writing your SQL in such a manner as that you have to specify table names to differentiate FKs then it's not only a savings on typing, but it adds clarity to your SQL to use just the ID in that you can clearly see which is the PK and which is the FK.
E.g.
FROM Employees e LEFT JOIN Customers c ON e.ID = c.EmployeeID
tells me not only that the two are linked, but which is the PK and which is the FK. Whereas in the old style you're forced to either look or hope that they were named well.
25
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
