How does everyone name table fields?
Which style is better/more acceptable…
PRODUCT table (approach #1)
ID
Name
Color
Weight
Style
OR
PRODUCT table (approach #2)
ProductID
ProductName
ProductColor
ProductWeight
ProductStyle
Why do you use one approach versus the other versus another alternative?
I prefer the first approach because having Product.ProductName in a query seems verbose and it adds no real value.
TomTees
I also go for the first approach, but I write ID it “id” and don’t start column names with a capital either, since in my opinion everything starting with a capital is a classname, while everything starting with a lowercase character is a variable name, and I deem database fields name to be variables, in a slight abuse of notation. (In other words iUseCamelCase)
I use the first approach because
i totally agree – (1) verbose (2) no real value
the only thing worse would be Product_table.ProductName
I very much prefer the first option myself, but I’ve had a hard time getting any traction at work. This is mainly because they insist on foreign keys being named identically to the fields they reference in other tables. This imposes the latter approach at least as far as keys are concerned.
What is most perplexing about this that we use SQL Server, which offers no syntactic support for natural joins, which for me is the only convincing argument to keep field names identical across tables.
Cheers,
D.
Naming the id field productID is about the only field that should ever be considered for using the second style of naming convention. There is no reason whatever for any of the other fields to need product on the front of the field name since you can easily place it there when necessary by adding the table name to the front eg. product.Name
There is the possibility of some tables containing more than one id field though and since they are also likely to have the same id field appear in multiple tables means that specifying what it is the id for as a part of the field name can help make things easier to follow.
Seems to me you’ve got a large number of people involved, and someone’s trying to enforce some sort of consistency/standards so people can look quickly at a query (especially complex ones) and know what is what.
As long as the naming style is consistent from table to table, you’ll be fine. So if a field is named ProductID, and everyone knows that ProductID reflects to the ID field on the Product table, you’ll be fine. But if people start using the ID nomenclature and it’s not for a foreign key, you’ll have problems. Or if the field is ProductID in one table and Product_ID in another (not a major problem, but consistency…)
The only noticable benefit to using more verbose names is you won’t have to alias like fieldnames on complex joins (ex Product.Name and Category.Name)
(oh, and I would never use Product.ProductName in a query. It would either be Product.Name or ProductName. If you’ve got ProductName in two different tables, then you’ve most likely got a problem with your nameing conventions)
I prefer any convention that involves less typing. “ID” is a perfectly valid name for a surrogate key field, because identifying a record is the only role it plays. My naming convention for foreign keys is almost always the name of the table they reference.
Cheers,
D.
I agree.
Where a business has a set of standards for their business that specify how something should be done then that’s the way you do it regardless of whether it is your preferred way or not. So if their standards say to use option 2 then that’s the only choice you’ve got in that situation.
The standards are there to ensure that everyone in the team does things the same way so that it doesn’t matter who does what, when you get the job of having to fix something it will look the same regardless of who did it.
as well as to foster innovation
Well I just grit my teeth and bear it, but few things annoy me as much as having to type “join some_table_with_a_long_name as b on a.some_table_with_a_long_name_id = b.some_table_with_a_long_name” for the thousandth time (yes, some get almost that long).
That’s why I shall relentlessly pimp for my convention. It’s better in every conceivable way.
Cheers,
D.