3 Top Tips for Database Naming
Singular and Plural
This goes for table names, and also for the names of join tables. If you call your tables “user” and “group” then you probably want your linking tables to be “user_group”. If you go for plurals (my personal favourite) then be consistent over whether the linking tables are called “user_groups” or “users_groups”.
ID Columns
I’ve seen two main variations on the column names for primary keys, one is to call them all simply “id”, and the other is to name them after their table name such as “user_id” or “group_id”. It doesn’t really matter but my recommendation is for the latter – that way, the user_id column in any other table clearly joins on to the user_id column in the users table, making it easy to read and understand.
Case and Capitalisation
Due to my EXtreme DOuble CApitalitis, I prefer everything to be lower case, but the key is consistency, so that it is easy for developers to get used to the patterns in the database setup and to develop against your schema without having to refer back to it all the time.
Consistency is Key
In general, I like database schemas which are predictable and well-laid-out. Although I have my own preferred conventions, I don’t mind what is used so long as it is predominantly in step with itself – this makes my life as a developer so much easier! What’s your top tip for sane database naming conventions? Leave a comment and let me know!
Lorna,
I agree with what you’re saying. Having consistency, even if the choice is poor, is much better than the best conventions inconsistently applied. Having a clear and consistently applied nomenclature makes it simple for both the original developer(s) and the maintainer(s) that come thereafter.
Matt
I agree, consistency plays a major factor in database schema maintenance.
I talked about last year and my slides are at http://www.slideshare.net/bngsudheer/mysql-naming-conventions-bangalore-mysql-user-group-oct-11-09
Another advantage of using table_id instead of id is that if the referential key is called the same, many databases support: a JOIN b USING user_id, which saves typing out the where clause.
I think the naming of the foreign keys should also be standardized. However I wouldn’t use the name of the table they refer to (like is common) but to describe the role, i.e. if there is a relationship between project and employee, I wouldn’t call the key ’employee_id’ but ‘projectmanager_id’. This makes your point about keys that are named equal moot, but it does have more flexibility when there are multiple relationships between the same tables.
Lorna,
I come across this all the time and agree that it frustrates me. I think Matt hit the nail on the head in the previous comment.
One book I recommend is
Joe Celko’s SQL Programming Style
http://www.amazon.com/Celkos-Programming-Kaufmann-Management-Systems/dp/0120887975
It’s fantastic!
Great post, and the other commenters are right – consistency is key.
One thing that bugs me particularly though is where the column names consistently contain the table name. If your table is called user, there really is no need to name your columns user_type, user_class, user_isactive, user_age and so on.
LJ,
It is funny to me that while I agree with your main points, in each case I disagree with your methods. I was working on the database for a project this morning so it is fresh in my mind. I prefer singular database names, I’d cols named I’d and camelCase for field names. The latter is the important one though as I use camelCase in my code as well and it keeps me from having to switch from underscores to camelCase depending on whether I am writing SQL or PHP.
=C=
Well, consistency is the main thing, the rest is a matter of taste :) I so often work with other people’s code that I don’t really mind what they choose as long as I can get a feel for it without having to look every single table/column/function name up every time
Yeah I have to agree with you on all these points.
I personally use camel casing throughout my code so it would make sense for me to use camel casing for database as well when calling database objects. It would be rather weird to have something like
[geshi lang=php]$userGroup->user_group_id[/lang]
The key to it is consistency and some degree of standardization.