Database Table Field Naming Conventions

Posted By: Tony Baird

Last Updated: Friday July 4, 2008

It’s another day so it’s time for another rant about something that continues to bug me.

The naming conventions of fields in tables of a database!  There seems to be absolutely no consistency between applications and in some cases even within the application it self.   My biggest gripe I would have to say is the naming of the primary key’s and foreign keys of tables.  I’ve seen the following recently in applications that have say the table customers

id cid custid customer_id

I personally prefer the customer_id for the primary key but even consistency of one naming would be great.  The big reason I like using tablename_id  for the primary key is when joining other tables.  For example: [sql]SELECT * FROM customers c JOIN orders o ON c.customer_id = o.customer_id[/sql] I like this much better than having something like: [sql]SELECT * FROM customers c JOIN orders o ON = o.customer_id[/sql]

But this I can live with.  It’s when applications have no consistency what so ever.  They name the primary key “cid” for example then the foreign key is “cust_id” in one table and “customer_id” in another.

This can also be applied to pretty much all fields across all tables.  Some maybe are “tablename_field” while others are just “field”.  Pick one or the other in an application and if there are multiple developers it might be a good idea to come up with a convention that everyone follows.  But I doubt the developers could come to an agreement anyways I know from experience with an instance where the naming of a common foreign key in an application is still not consistent through out the application.  It is in the database, however the variable name for it seems to change from page to page with some using camel case on it while others believing it should be just one word.

Anyways I’m sure there are naming conventions somewhere that everyone is suppose to follow for these sort of things but no one seems to do it.  It also does not help that programmers constantly have a change of heart part way through resulting in the continued inconsistency.

Ready to get started? Build your site from