Author:  Michael Harneez

In this article I am going to discuss the 12 U's for good database design. If you follow these guidelines then I am very much sure that you will be able to create a good database design.

1. Use proper planning

Always plan before you design. This will reduce time and will help you get a good database design at the end. Gather all the requirements and identify all the objectives. Proper planning will result in good database design.

2. Use proper naming conventions

Always use proper naming conventions. Do not use dashes (-), hashes (#), quotation marks (“”,'') or brackets ([], ()) in the names of tables or fields. Use such naming conventions so that if someone else is reading your documentation then he can easily understand everything.

3. Use ‘ yyyy' year format in dates

Some people use ‘M-D-Y' format while some use ‘Y-M-D'. Now let's take an example of date 08-08-21 . It is hard to find out which value represents the month and which represents the year. Therefore it is advised to use understandable date format like 2007-08-21 so that you need not to go to documentation and understand at the first glance that what date format it represents.

4. Use normalization

Always normalize your tables to maximum 3NF. This will remove the redundant data and will give performance boost to your system.

5. Use unique foreign key on a table

Always use unique foreign keys. A foreign key may be an integer or a character but the important thing is that it should never be seen or manipulated by the users. This helps in developing normalized relations between tables. If you relate tables by a code then a change in code will be an overhead. However if the tables are related by foreign keys then only one record that matches the code will need to be changed. You can also add some noise to that foreign key so that it becomes hard for people to hack your program and manipulate the records.

6. Use M:N relationships appropriately

All many-to-many relationships should be properly handled. Consider the below example

1. A student registers M courses.

2. A course is registered by N students.

The tables we have are

Table 2.1: Student

Table 2.2: Courses

There is the problem of redundancy in Courses table. There should be a separate table for student and courses and an additional table that has data of student and his registered courses. The tables will be

7. Use field extender table

Sometimes your database is very large with hundreds of tables. Now after some time your requirement changes and you want to add a new field to any table. If your table lies at the end then searching through the hundreds of tables and finding your required table is an overhead. However field extender table will make your life easy. An example table is as under

Table 6.1: FieldExtender

The query at the back end will be like ALTER TABLE @tableName ADD COLUMN @ addedFieldName @dataType (@Length) @nullAllowed @Unique

8. Do not use rowid/recid in data

Do not use rowid/recid in data because when a database is dumped and loaded then it is not guaranteed that the rowid/recid for a given record is going to be the same.

9. Use DB_config table

If you are working on more than one database or have different environments then it is better to record some information about the database in DB_config table. The information you can store can be database type, its version, the environment, client's website, administrator email etc.

10. Use stored procedures 

Use stored procedures for database access. Stored procedures not only encapsulate a SQL statement to a function but they also act as a hiding layer between users and database. Stored procedures are pre-compiled statements so they increase the performance. Also you can use Transact-SQL (T-SQL) to embed conditional logic in the stored procedures. Learn more about stored procedures at http://en.wikipedia.org/wiki/Stored_procedure

11. Use purging fields 

It is a good practice to use purging fields like purge_state and timestamp on all the tables. It is very much helpful in clearing the stale data from the database.

12. Use input validation to avoid SQL Injections 

Always validate the input in order to avoid SQL injections. If your application has non-validated input then attackers make use of such vulnerabilities and can execute arbitrary SQL commands on the backend server through the web application. In worst case they can drop whole table in the database. You can learn more about SQL Injections at http://msdn2.microsoft.com/en-us/library/ms161953.aspx

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   The power of Oracle Optimizer Hints!
   Oracle – SQL: Performance Boost with Collections
   SQL in Action I - Single Table Queries
   A Guideline to Oracle Server Optimization