Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Strict Database Standards and Conventions

By Mattias Fagerlund,

Strict Database Standards and Conventions

I recently read Brian Knight's “Database Standards and Conventions”. I found that I agreed with his recommendations, but I feel that he doesn’t go far enough. He writes “I receive more hate mail about this article than anything I've ever written”, so I guess I’m setting myself up for a torrent of criticism in publishing this…

Background

I learned this standard in 1996 when I started work on a project for a large insurance company here in Sweden. Since then, I have since used these conventions on just about every project I have worked on, and I have learned to love them. Feel free to contact me with any thoughts or comments on mattias@cambrianlabs.com. I work as a senior applications developer for Cambrian Labs AB in Stockholm, Sweden.

Modules

First off, when your conceptual database design is more or less done, divide it into modules. What modules you use totally depend on what kind of database you’re designing. For instance; you could have one module for contacts (addresses to companies and individuals), one module for products, one module for contracts, one module for agents and so on. As a simple rule, tables should more often reference tables within it’s module than tables outside it. If they don’t, they probably belong in another module. Keep the modules fairly small (perhaps 3 to 25 tables), otherwise the advantages are lost.

 

This also helps to conceptualize the database design, learning how the system works is easier one module at a time, than the entire system all at once. They can also be used when several people are working on the same project - work can easily be divided along module boundaries.

 

Number the modules 01, 02, 03 and so on. These module numbers will be used when naming tables.

Tables

Tables are also numbered, but their number is unique within the module, not across modules. That means that table 01 can exist in many modules - but they will still be unique in combination with the module numbers. The name of the table should be T + module number + table number + “_” + descriptive name. For instance

·         T0101_CONTACT  = the first table in the first module

·         T0102_CONTACT_TYPE = the second table in first module

·         T0201_ARTICLE = the first table in the second module

·         T0202_ARTICLE_AUTHOR = the second table in the second module

 

This has many advantages;

·         the tables will be sorted in the module order in the Enterprise Manager

·         finding a particular table in a long list of tables is easy when you just have to match the prefix

·         it’s easy to tell to what module a table belongs, and therefore what it does

 

Don’t worry, you’ll memorize the prefix of any frequently used table very fast.

 

I find that I sometimes forget the name of tables I haven’t used in a while, and listing all tables (when there are many) doesn’t really help finding that one table I need. But I always remember what module the table belongs to, so I just write sp_tables “T05%” (or actually, I write tables_like T05, which is a script much like sp_tables, but it lists the table names first, so I don’t have to scroll a lot ;)). Out pops something like 10 to 20 tables, and picking out the one I need is a breeze.

 

Two tables should never have the same prefix, that would make things much more difficult in the future. If you find that you have, by mistake, created two tables with the same prefix, correct the mistake as soon as possible, also correcting the column names!

Column names

When creating columns, their names are prefixed with the table prefix. The only exception is foreign keys, but more on that later. So, the name would be table prefix + “_”  + descriptive name. The table T0101_CONTACT would contain columns like T0101_NAME, T0101_ADRESS_1 and so on. When you join two tables, there’s never any question to which table a certain column belongs. It’s not even an issue – it’s obvious from the column name.

 

If there is a simple primary key (simple as in a single column), then it is treaded as a special case. If the key is an identity column, it’s named table name + “_” +  id. It would be T0101_CONTACT_ID in this case. If it’s a code of some kind, it’s named table name + “_” + code. This is because the primary key is often referenced in foreign keys, and therefore the full name is very useful to have, as I’ll show below.

 

As Brian Knight suggests in his paper, abbreviations should be used sparingly, and using underscore between names is good practice. Oh, if you forget to add an underscore somewhere in a column name and don’t go back and change it, then it will come back and bite you. Remembering that one exception, where there wasn’t an underscore, is very difficult.

Foreign keys

When adding a foreign key (a column that references the primary key on another table) to a table, the column should have the name of the original column. If my T0101_CONTACT table refers to a T0102_CONTACT_TYPE, then the column should be named T0102_CONTACT_TYPE_ID (assuming it is the primary key of T0102_CONTACT_TYPE).  This has huge advantages;

·         It’s always obvious what table a foreign key refers to

·         It’s always obvious what columns actually are foreign keys (as they have a different prefix)

·         When joining two tables, you have most (if not all) of the information you’ll be needing to write the query

·         When creating the relational integrity diagram in the enterprise manager, you’re always connecting two keys of identical names, so no searching is required - it’s the default selection in the editor.

 

For instance, running the query “select * from T0101_CONTACT”, it is immediately obvious that it contains a foreign key called “ T0102_CONTACT_TYPE_ID”. It’s “obviously” a foreign key because it has a different prefix. It’s also obvious that it refers to a table called T0102_CONTACT_TYPE. Therefore it would be possible to write a join between these two tables without doing any more research;

 

select

  *

from

  T0101_CONTACT,

  T0102_CONTACT_TYPE

where

  T0101_CONTACT.T0102_CONTACT_TYPE_ID =

    T0102_CONTACT_TYPE.T0102_CONTACT_TYPE_ID

 

When one table contains two foreign keys to the same table they’re named with the original column name plus a descriptive text – as always, separated with an underscore.

Naming views

The naming convention of views is very similar to that of tables, except the prefix is V instead of T. This makes it “V” + module number + view number + “_” + descriptive name

 

However, it’s rather convenient to have views named like the table they’re looking at (with the V instead of the T) and maybe a description of what they do. This way they’re easy to remember, and they’re easy to find. You remember the name and the number of the table, just look for views with the same number prefixes and you’re home. This would violate the numbering scheme in case there was more than one view per table. Also, if the view looks at many tables (as the most often do), which of these tables should supply the prefix?

Naming stored procedures

Again, the naming convention of stored procedures is very similar to that of tables, but we prefix them with a P, as in “P” + module number + stored procedure number + “_” + descriptive name.

In closing

If you were with me this far, I would like to thank you for taking the time to read this. I hope you at least found some small piece of wisdom among all these many words.

Total article views: 11126 | Views in the last 30 days: 12
 
Related Articles
FORUM

Column Data type issue

Insert Decimal column data prefix with symbol

FORUM

Multiple references to same foreign table/column

expert opninion on use of multiple foreign keys on same column

FORUM

Pivot with dynamic number of columns

Pivot with dynamic number of columns

SCRIPT

Check Candidate Columns for FOREIGN KEY Constraints

Reports table columns that are not foreign keys but that perhaps should be.

FORUM

How to get schemaname, tablename, identity column, foreign key constraints

How to get schemaname, tablename, identity column, foreign key constraints

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones