November 26, 2003

Database Design

When designing a database, use the single version not the plural of the name of the table. For instance, if you have a table that is full of people, call the table, tblPerson, not tblPeople. Why? Well for cardinality reasons, when deciding how to normalize the database, this convention gives a clearer tip to the relationship being either one to one, one to many, or many to many. For example, let's say we have a database, with a couple of tables, tblCustomer, tblDemographic, and tblVisit. One customer can have many visits, and a customer can have many demographics. The convention makes it just a little bit clearer when discerning the database schema.

The other item is to prefix database tables.


  • For tables, try using tbl

  • For views, try using vw

  • For queries, try using qry

  • For stored procedures, try using sp

These prefixes do a couple of things, when you are long gone and at that 6 figure job you deserve :), individuals reviewing your code, can easily determine what they are looking for, when it breaks. Also reserved words will never affect your application, who's going to make a reserved word tblfile vs file?

Posted by Elyse at November 26, 2003 5:50 AM | TrackBack
Comments

I completely agree with not using reserved words, and prefixing everything. Personally, I will also use...

tlkp -- prefix for lookup tables
tlnk -- prefix for many-to-many join tables

This saves time when going back to old databases, trying to remember which tables hold "real data" and which were just a place to store commonly used lists (states, zip codes, whatever).

However I will often switch between the plural and singular version of a word for my table name, based on what makes the most sense.

Example:

tlkpZipCodes (plural) -- it is, in fact, a list of zip codes. There is no relationship here. It's purely storage.

tblMessageBoard (singular) -- this is my message board, containing the message content, and probably some data like who posted the messages, dates they were posted, originating IP address, etc.

Also, since several databases use "sp" as their prefix for system (built-in) stored procedures, sometimes I will come up with another prefix for them. Usually something pertaining to project name. This is just so I can tell my stored procedures apart from the preexisting ones.

By the way...hello, I'm Nolan. I found your blog off a link from http://www.fullasagoog.com/ a few days ago.

Posted by: Nolan at November 26, 2003 1:08 PM

Hi Nolan!

Posted by: Brian LeRoux at November 28, 2003 1:55 AM