3 Top Tips for Database Naming

Perhaps this is more of a rant than a post but I do keep running into issues with databases with names that are inconsistent - which makes them really difficult to work with. When designing a database, there are a few points to consider:

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!

9 thoughts on “3 Top Tips for Database Naming

  1. 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

  2. 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.

  3. 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.

  4. 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

  5. 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.

Leave a Reply

Please use [code] and [/code] around any source code you wish to share.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>