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
