SQL Reference ID Naming

Posted on July 28th 2011 3:51am Thursday, by Blaine

Readable code is important when you are working with others. If you plan to work with others on projects it is good to learn the appropriate way to do things. In some cases there are many appropriate ways to do one thing but it also important to consider the readability.

For instance, some people write their tables primary id name as ID, id, or table_name_id. While people may argue about the appropriate way to do it, all of these are correct as long it is constant throughout the project.

The problem I see developers running into is naming reference table IDs. This can cause confusion for other developers. The appropriate way to reference another table’s ID is to include the table full table name. You never know when you are going to have similar table names that will cause confusion for another developer.

Here are some example reference IDs and the tables that they would link to the IDs from.
user_id is from the user table.
user_group_id is from the user_group table.
client_group_id is from the client_group table.

Here is an example of where another programmer might have trouble easily understanding your database. If you have a database with several tables including user, client_group, and user_group. And the user table references the user_group table with a column named group_id. Another programmer may assume that group_id is actually referencing the id from the client_group table.

Leave a Reply