Database Table Field Naming Conventions

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:

SELECT * FROM customers c
JOIN orders o ON c.customer_id = o.customer_id

I like this much better than having something like:

SELECT * FROM customers c
JOIN orders o ON c.id = o.customer_id

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.

This entry was posted in Programming. Bookmark the permalink.

One Response to Database Table Field Naming Conventions

  1. fenerli says:

    And even better:

    SELECT * FROM customers c
    JOIN orders o USING customer_id

    I’ve deliberately used that instead of natural joins, but that is also an option:

    SELECT * FROM customers c
    NATURAL JOIN orders o

Leave a Reply

Your email address will not be published. Required fields are marked *