How to Achieve Good Relational Database Design

By Kalman Toth, M.Phil., M.Phil., MCDBA

January 14, 2005

Principal Trainer at: http://www.sqlusa.com/

Normalized database design is the very essence of a well constructed database. If you ask me what is the definition if a database normalized to the third form, I would have to look it up somewhere. However, I know how to design 3NF by heart.

Here are the practical rules to normalized database architecture:

1. Data should be in a form which can be processed by SQL. As an example, SQL cannot process list.

2. Data should only be at one place. That means in one cell which is the intersection of row and column in a table.

3. Each table should correspond to some logical information entity which reflects the way business is done. Examples for tables: Account, NameAndAddress, SalesRegion, Product, Supplier, Country, etc.

4. Each table should have a unique primary key.

5. Tables should connect to each other by primary key - foreign key relationship. Example: in the NameAndAddress table CountryID is a foreign key which refers for the primary key of the Country table

6. A table should not have sparse columns. Address2 in the NameAndAddress table may sparse. So it can be broken out to its own table and foreign keyed back the NameAndAddress table.

The bottom line: nothing is wasted in a well-designed database. Everything is very efficient. The main advantages: ease of maintenance, ease of extendibility, and ease of development.

The perceived disadvantage: too many joins. Too many joining of primary keys and foreign keys to pull the information together. This is a valid point, but it is a small price to pay for the tremendous advantages of a normalized database.

If you have a database which is not normalized, you still can add subsystems to it which are normalized. Sooner or later you can phase out the legacy tables and replace them with properly designed tables.