May 10, 2003

A good naming convention for tables in a database

We're currently maintaining a product that was developped by a java team about a year and a half ago. Although, we tend to mostly be horrified by the code and sometimes the architecture, they did one thing really well I think and that is naming the tables in the database. I've never seen anyone or any project use their nomenclature so I'll share it here with everyone.

The basic idea is this

  • Every table name is prefixed by a three letters related to the name. So for example a table representing account would be ACC_ACCOUNT or a table reprensenting devices would be DEV_DEVICE

  • A table linking rows in two other tables is just made up of the prefixes and a LINK suffix. Imagine two tables TBA_TABLE_A and TBB_TABLE_B. Their link table would be TBA_TBB_LINK
  • every column name is prefixed with those same three letters, so you get ACC_NAME and DEV_BRAND for example

  • Columns which are part of a primary are further prefixed by PK_ in addition to the three letters. So you typically get PK_ACC_ID

  • If your column is a link to another column in an other table you prefix the name of the column in the other table with FK_. Imagine a contract table (CTR_CONTRACT) that is linked to an account through the account's primary key. In that first table, you would have a FK_ACC_ID column

  • Finally if your foreign keys make up your primary key (usually for a many-to-many relationship) you prefix with PK_FK_. Using the same example as above, you get for the link between tables A and B : PK_FK_TBA_ID and PK_FK_TBB_ID

What this gets you is

  • You (almost) always know what table a column belongs to in a query

  • You mostly don't need to to use table aliases in your queries

  • It's easy to build JOIN queries because you usually have WHERE clauses such as : PK_ACC_ID=FK_ACC_ID and such

  • It's easier for someone learning the schema to have a consistent nomenclature

Anyone else does things similar to this ? Better ideas ?

Posted by pgirolami76 at May 10, 2003 10:20 AM | TrackBack
Comments

1 - "prefixing table name with the first 3 letters of the name". Not sure what is the benefit. OTOH, if you have multiple tables within a logical module, it certainly helps in prefixing the table name with an abbreviation for a module. eg: ACC_ACCOUNTS, ACC_TRANSACTIONS

2 - Prefixing with PK and FK. Usually, I don't name columns like that. Instead, create constraints named like PK_ or FK_. Reason is that business logic and names sometimes change (usually, more columns get added to the PK) :-( It is a lot more easier to rename constraints than columns. Additionally, if you don't name columns like PK/FK, you have *same* column names in both tables. This also lets people know about a relationship. And it prevents people from writing "select A.*, B.* from" queries - better chance for them spelling out column names they want to retrieve.

3 - I think table aliases in queries are really good. Even if I am selecting from just one table, I use an alias. Makes life lot more easier when additional tables need to be added. Or if source tables need to be changed - often, when too many sources get into the picture, a view becomes much more effective. This way, my SELECT clause changes much less than my FROM clause changes. This is a tremendous advantage when you have reports and forms running from SELECT clause. As long as I don't change SELECT clause, I don't need to touch the report/form definitions.

Posted by: Babu at May 12, 2003 01:23 PM

I think prefixing the three letters (e.g., ACC_ACCOUNT.ACC_ID) is redundent and makes any sql more difficult to read. The table name is part of the name of a column. I have worked with code like this and grew to hate the naming convention. ACCOUNT.ID is much more elegant and clear. Plus if it makes sense on the table/column level, why not on the schema level? Assuming a schema name of STO_STORE, you would get STO_STORE.STO_ACC_ACCOUNT.ST0_ACC_ID instead of STORE.ACCOUNT.ID.

Posted by: Gregg at June 4, 2003 12:50 AM

1. I agree with Babu and Greg on all points.

2. You tend to run out of useful TLAs pretty quickly (hence Microsoft letting go of the 8.3 file name format).

3. Table naming does need a lot of careful thought. Names should be clear and concise, but not become useless as a schema grows, nor too constrictive.

4. Common abbreviations, well documented and consistently used as aliases are a good idea.

Posted by: Will at August 29, 2003 04:23 PM

I agree with you

Posted by: milf at April 3, 2004 11:40 PM

A wolf in sheep's clothing. (c)

Posted by: Newton at June 14, 2004 01:45 AM

Thanks for good info

Posted by: Celebrex at June 15, 2004 06:30 AM

Thanks for good info

Posted by: Insurance at June 20, 2004 10:31 PM

Thanks for great info

Posted by: Calories at July 4, 2004 06:44 PM

Thanks for great info

Posted by: Payday loans at July 7, 2004 02:06 PM

Thanks for great info

Posted by: Weight loss surgery info at July 28, 2004 02:52 PM

Thanks for great info

Posted by: Generic viagra info at July 29, 2004 03:05 PM

Thanks for good info!

Posted by: Gmac mortgage info at August 3, 2004 11:56 AM

Thanks for good info!

Posted by: Weddings info at August 3, 2004 10:18 PM

Thanks for good info!

Posted by: Music lyrics info at August 5, 2004 01:27 PM

Thanks for good info!

Posted by: Prudential real estate info at August 6, 2004 02:21 PM

Thanks for good info!

Posted by: Divorce rates info at August 6, 2004 03:18 PM

Thanks for good info!

Posted by: Travel agents info at August 7, 2004 04:59 PM

Thanks for your useful information!

Posted by: World health organization info at August 16, 2004 01:09 PM

I agree with you :) Thanks!

Posted by: The south beach diet center at August 18, 2004 11:33 PM

You right =) Thanks for good info!

Posted by: homeowners insurance at August 19, 2004 07:11 PM

Thank you! You right :)

Posted by: Student health insurance center at August 26, 2004 07:11 PM

I agree with you :) Thank you for great information!

Posted by: Divorce records center at August 28, 2004 11:23 PM

1879 http://www.e-free-credit-reports.com cool eh?

Posted by: credit reports at August 31, 2004 10:59 PM
Post a comment









Remember personal info?