Best Practices for Database Design

,

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

Rate

2.03 (115)

Share

Share

Rate

2.03 (115)