Do You Prefer Verbose Naming When It Comes to Database Columns

Do you prefer verbose naming when it comes to database columns?

The table name already gives the context.
No need to prefix columns name with it.
When joining tables use table.column syntax.

Database, Table and Column Naming Conventions?

I recommend checking out Microsoft's SQL Server sample databases:
https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

The AdventureWorks sample uses a very clear and consistent naming convention that uses schema names for the organization of database objects.

1. Singular names for tables
2. Singular names for columns
3. Schema name for tables prefix (E.g.: SchemeName.TableName)
4. Pascal casing (a.k.a. upper camel case)

MySQL naming conventions, should field name include the table name?

I agree with you. The only place I am tempted to put the table name or a shortened form of it is on primary and foreign keys or if the "natural" name is a keyword.

Users: id or user_id, username, password, last_login_time
Post: id or post_id, user_id, post_date, content

I generally use 'id' as the primary key field name but in this case I think user_id and post_id are perfectly OK too. Note that the post date was called 'post_date" because 'date' is a keyword.

At least that's my convention. Your mileage may vary.

Naming of ID columns in database tables

ID is a SQL Antipattern.
See http://www.amazon.com/s/ref=nb_sb_ss_i_1_5?url=search-alias%3Dstripbooks&field-keywords=sql+antipatterns&sprefix=sql+a

If you have many tables with ID as the id you are making reporting that much more difficult. It obscures meaning and makes complex queries harder to read as well as requiring you to use aliases to differentiate on the report itself.

Further if someone is foolish enough to use a natural join in a database where they are available, you will join to the wrong records.

If you would like to use the USING syntax that some dbs allow, you cannot if you use ID.

If you use ID you can easily end up with a mistaken join if you happen to be copying the join syntax (don't tell me that no one ever does this!)and forget to change the alias in the join condition.

So you now have

select t1.field1, t2.field2, t3.field3
from table1 t1 
join table2 t2 on t1.id = t2.table1id
join table3 t3 on t1.id = t3.table2id

when you meant

select t1.field1, t2.field2, t3.field3 
from table1 t1 
join table2 t2 on t1.id = t2.table1id
join table3 t3 on t2.id = t3.table2id

If you use tablenameID as the id field, this kind of accidental mistake is far less likely to happen and much easier to find.

Should I specify names via @Column and @Table always or only when necessary

Since this is an question on best practice, I can only share my perspective. According to me, the key question is "How fluid is your database design?" (which probably boils down to how fluid is your domain model).

Either way, I will provide table name and column name always. ( this provides a certain degree of clarity). If I had written an abstraction layer on top of entity ( which groups entity access as functional units), then I can, to a certain degree handle the changes in my entity design gracefully.

Not providing table and column name just reduces the readability of code and with proper design, we can make sure that the name does not cause any undue resilience while refactoring. I should be able to regenerate my entity model and the refactoring could be done in the abstraction layer gracefully. Hope I have expressed my thoughts clearly.

Is it better to name the primary key column id or *_id?

Tablename_Id is my strong preference. When you do joins to Fks you know exactly what to join to what and don't make mistakes where you join to ID in table a when you meant tableb below is an example of how easy this is to do especially if you copy the on clause from somewhere else

FROM tablea a
JOIN tableb b
  ON a.ID = b.tableaid
JOIN tablec c
  ON a.ID = c.tablebid

In the case above, you really wanted to join to B.Id but forgot to change it from a when you copied. It will work and give you a resultset that isn't correct. If you use table_id instead, the query would fail the syntax check.

Another problem with using Id is when you are doing complex reports. Since the repport queries have to have fields with individual names, you can end up wasting time writing a bunch of aliases you wouldn't need if you had named the id with the tablename.

Now people who use ORMs don't write a lot of SQl but what they do write and what report writers write are generally complex, complicated statements. You need to design you database to make it easier to do those things than simple queries.

The use of ID as the name for the identifying field is considered a SQl antipattern. http://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557/ref=sr_1_1?s=books&ie=UTF8&qid=1308929815&sr=1-1

Is prefixing each field name in a table with abbreviated table name a good practice?

Don't do that. It's redundant and leads to frustration in the long run.

The only field, where you could apply this might be id, because user_id would obviously be the id of the user and it would simplify writing joins in SQL. But I wouldn't even do that.

Sql naming best practice

prefixes like that are pointless, unless you have something a little more arbitrary; like two addresses. Then you might use address_1, address_2, address_home, etc

Same with phone numbers.

But for something as static as age, gender, username, etc; I would just leave them like that.

Just to show you
If you WERE to prefix all of those fields, your queries might look like this

SELECT users.user_id FROM users WHERE users.user_name = "Jim"

When it could easily be

SELECT id FROM users WHERE username = "Jim"

---

Related Topics