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

Best Practices for Database Design

By J.D. Gonzalez, (first published: 2005/04/06)

Introduction

We've all had it happen before. Your manager or co-worker comes into your office and asks you to put together a database to manage some project they're working on. ... oh and by the way, they want it tomorrow! We create tables and name them something that only we as the developer can understand, but when it comes to developing reporting for the tool, our table, column and view names become difficult to read and debug.

If this is you, STOP!!! Remember, your work is a reflection of you as an employee and speaks volumes about you as a person. Yes, it sometimes takes extra time to understand the data model, but this extra time will pay dividends when you need to extract data or allow other users to access the database directly.

This article will not focus on normalization and how to write queries to meet the goal of the project, but will instead look at how we name our databases, tables, columns, stored procedures and even views.

Table (view) Naming Conventions

Why is this important? It's just a table right? Well... not really. Users need to understand what type of table it is. Is it a history table, a reference table... ? You get the idea. Before I even begin to think of column names, I determine what type of table it is going to be. I use the following conventions:

Table (view) type
Table suffix
Description
History table
hist
This type of table holds information in a historical manner. Will typically be a one to many relationship.
Reference (Dimension) table
ref
This type of table holds names and descriptions (e.g. 1 = Eggs).
Current snapshot
current
This type of table holds the most current information. Typically used in historical table to pull the last record for a given foreign key.
First instance
orig
This type of table holds the first instance of a foreign key. Opposite of current snapshot. May contain same records as the current snapshot if only one instance of the foreign key exists.

You'll notice that there really aren't too many types of tables. Now that we know what type of table it, we need to determine what type of data gets inserted into the table. This will vary based on your business model. I'll keep this example generic and refer to 'Promotions'. To keep the name short, we'll refer to a promotion as 'promo'. Let's take a stab a naming a promo reference table. To keep things organized, I call my tables in this manner: <<type of information>>_<<type of table>>. As a result, our promo reference table is called quite simply 'promo_ref'.

Views Special Case

I know many of you don't let your users access tables directly, but instead use views to query against the table. In my book, there's nothing wrong with that as long as there are not too many joins. I usually just create a view of the table this way:

Create view v_promo_ref
as
Select promo_code, promo_name, promo_desc
From promo_ref

For view, the use of the letter 'v' or any other letter ('v' is my favorite, but others use 'vw') in front of the name help when viewing table and view names in a tool outside of Enterprise Manager such as Microsoft Access. At a glance, your users can determine whether this is a view or a table, what type of information is stored in the table and what type of table it is.

You'll also notice that only the view has a prefix and not the table. I make the assumption that unless they are marked with a view or stored procedure prefix, they are tables. I do this since all of our data, regardless of database, is based on what is in a table.

Column naming conventions

As a beginner several years ago, this was one of my downfalls. My column names never matched from table to table. Needless to say, I found myself doing way too much debugging and not much development. If you have a column in one table called acct_nbr, call it acct_nbr in the other table. This one simple move will save you from having to look at the table you are joining to in order to determine the field name. Below are the column naming conventions that I use:

Column type
Column suffix
Description
Naming of item
name
This is used to describe the name of a primary key.
Description of item
desc
This suffix is used to describe name in more detail (e.g. '20% off of first sale')
Date data entered to database
entry_date
This is used to timestamp when the row was entered into the database.
User who entered data to database
entered_by
This is used to stamp the record with the user (or application) that entered the record.
Date data updated on database
update_date
This is used to timestamp when the row was updated on the database.
User who update data on database
updated_by

This is used to stamp the record when the row was updated on the database.
Numeric primary key
<<name>>_id
This is used to describe the primary key when the key is a numeric value (e.g. '1005'
Alphanumeric primary key <<name>>_code This is used to describe the primary key when the key is an alphanumeric value (e.g. 'PN1')

Stored procedures

From an organizational standpoint, you can probably get away with not naming your stored procedures. But it comes in very handy when you are debugging your application. The nomenclature I use is 'usr_<<some descriptive name>>. When debugging my application, I know that when I see the prefix 'usr', I'm dealing with a stored procedure and I know exactly where to begin troubleshooting.

For the descriptive name, I always include what the stored procedure does. For example, if the stored procedure gets a collection of menu values and names, then I call it 'usr_getMenus'. To make things easier to read, I always use underscore for the first word and capitalize the second word. Furthermore, I make the name two or more words so that I can follow this convention. Finally, with the exception of the underscore (_) at the beginning, I do not include underscores between words.

Databases

Lastly, the use of a naming convention on the database name greatly aids during maintenance of the database. Similar to the naming convention used for the stored procedures, I use the prefix 'dev' and 'prod' to distinguish between a development database and a production database. Secondly, I always follow the prefix with the name of the application it supports. For example, my development database for a project tracking tool would be called 'devProjectTracker'.

Conclusion

The naming conventions I listed above are not difficult. Usually, we are pressed for time to use them. Don't worry; we're all guilty of it. However, the more you use them, the more they become second nature. You'll also thank yourself when you need to make revisions to the code eight months later. The key takeaway here is consistency. If you're consistent in the manner you architect your databases, modifying your code and/or application will become much easier.

I'd like to hear any other suggestions that readers may have.

Thank you, J.D. Gonzalez


Total article views: 50016 | Views in the last 30 days: 22
 
Related Articles
ARTICLE

Strict Database Standards and Conventions

In this article by Mattias Fagerlund, he shows you how he employs strict database naming standards a...

FORUM

Hungarian notation convention

Hungarian notation convention

FORUM

Naming Conventions Standards

Naming Conventions SQL Server Tables, Views, Stored Procedures, Functions

ARTICLE

Database Standards and Conventions

Having a good set of naming conventions for your SQL Server objects is one of the most vital things ...

ARTICLE

Stored Procedure Naming Conventions

As your SQL Server applications grow, chances are that you have more and more objects, especially st...

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