Column Naming Advice

Question

I know there are other naming convention discussions, but the ones I saw were all general guidelines, I'm asking more for advice on good practice regarding my specific case. However, if there is another question that directly covers my case, please link it and I will gladly delete this question.

I have a database for modeling electronic devices, which contains tables such as Model, Unit, Brand, Manufacturer, etc. I'm wondering what to call the column that represents the actual textual name of the instance of that entity - as in "Canon" or "HP" for brand. Obviously, I could adopt the convention to simply calling the column "name," which I did at first. But then during a select, you are forced to reference the table names to get the columns since "name" is not unique among the selected columns:

SELECT Model.name, Brand.name, FROM Model, Brand

Which is ok, but then I got to the MobilePhoneFormFactor table and the Year table, the latter which stores accumulated data on a specific year. If I force myself to use the same convention with these tables, I get:

SELECT Model.name, Brand.name, Year.name, MobilePhoneFormFactor.name FROM Model, Brand, MobilePhoneFormFactor, Year

It seems a bit weird to refer to the year as "Year.name" instead of just "year." I could make this case an exception, but then I'm already breaking the naming convention I just committed to. So I considered this alternative naming convention:

SELECT model, brand, formFactor, year FROM Model, Brand, MobilePhoneFormFactor, Year

Here the Brand table has a "brand" column that is the textual name of the brand, the Model table has a "model" column that is the textual name of the model... etc. As shown in this select statement, this approach also allows me to conveniently drop the table name prefixes for columns in most cases (if you kept the table names, you get the rather redundant "SELECT Model.model, Brand.brand, ...").

But then what if I want to have a description field for each table? The obvious implementation is once again Brand.desc, Year.desc, just like my original approach to names. So this makes me think it's safest and most consistent to simply revert back to the Brand.name, Year.name scheme, even though it's a little weird in some situations.

A third option would be to name the columns "brandName, modelName" etc. but you might as well use simply "name" and always reference the table name in queries with "Brand.name, Model.name" in that case.

What do you think will be the best for my case? I'm not a database veteran, so I'm wondering what things down the road will make me look back and say, "it would have been so much more intuitive if I had just gone with Brand.name, Year.name" for instance.

Note: regardless of what I end up calling it, the textual name column (the natural key) is being used as an alternate key with a unqiue constraint; I'm using an IDENTITY column for the primary key.

Answer 1

I usually alias my tables in queries, such as:

SELECT b.Name, m.Name, mpff.Name 
FROM Brands b, Manufacturers m, MobilePhoneFormFactor f  etc...

However I think MobilePhoneFormFactor is too long of a name; I would just call it FormFactor.

Answer 2

I'd go with name. I don't disagree that Year.name is a little weird, but IMAO it's worth it to always know what this column is called and not have to remember anything.

While we're exploring options, though, some people like to do like Model.ModelName, Brand.BrandName, etc.

I, of course, think they're crazy.

Answer 3

I would generally go with the clearest column name that makes sense for tables, regardless of whether or not there is a collision of names; and when you do that, make sure that you explicitly state the table name before the column name in any select statement.

Such as:

SELECT Model.name, Brand.Name, mff.Name from Model, Brand, MobileFormFactor as mff...

Answer 4

I agree with the Neil N, it's a good idea to use table names or aliases in any case when you are doing a multi table join to eliminate ambiguity. Name your columns with names that make sense and then when you do multi table joins alias the tables and use the aliases.

Your table names should be as unambiguous as possible. Shorter names are ideal but if longer names are required to reduce ambiguity then use the longer names.

Answer 5

This is such a gray topic as you will find that there is no right or wrong answer but everyone has their own opinion and naming conventions.

Some general rules.

1. Be consistent.

2. Avoid repeating the table name in the columns.

3. Avoid SQL Server Key or Reserved words.

4. Be descriptive. If you and/or someone else cannot tell exactly what data is stored in the column based upon the name then it is not named correctly.

Answer 6

I don't like repeating things, hence I would not use Product.ProductName (and don't use Product.ProductId, too).

Naming the column just like the table seems also flawed to me - Product.Product?!? If a row from the table represents a product, how can a product be represented by a single column, too? So I absolutly suggest Product.Name.

In the case of the table Year I tend to say that the name is not choosen very well - you are not storing information about a year, but something like an accumulation interval. Maybe call it ReportingInterval and then there is no problem with calling the column Year. And I don't think this violates the naming convention - it's just a column name like Name. If you really want a key column with the same name for all tables, you should consequently name it NaturalKey because you want a column with a specific function, not describing a common attribute of all your tables.

By the way, table names must be pluralized ... :D

Answer 7

I would stick with the simplest scheme possible. All schemes break down after a while and it becomes difficult to remember what or why you used something other than name and id.

I always alias my tables and use those aliases when I select the column. My alias convention (and yes this scheme has its limits as well) is the first initial from each word in the table name. So

SELECT m.name, b.name, FROM Model AS m, Brand AS b ...

and

SELECT m.name, b.name, y.name, mpff.name FROM Model AS m, Brand AS b, MobilePhoneFormFactor AS mpff, Year AS y ...

This keeps the naming schema simple and memorable and also makes the SQL more readable IMHO. I also use explicit JOIN statements to distinguish table joins from filtering clauses in my WHERE clause, but that is probably getting outside the scope of the question.

Answer 8

I always use:

SELECT ModelName, BrandName, FROM Model, Brand

If you use

SELECT Model.name, Brand.name, FROM Model, Brand

then you'll need to give each column an alias when you join up the tables in views, like this

SELECT Model.name AS ModelName, Brand.name AS BrandName, FROM Model, Brand

If you use fields like ModelID, ModelName, ModelDescription then you always know what you are dealing with when you join your tables up -- especially when you use aliases (which you should) and then have m.ModelName instead of Model.ModelName.