Why good Database Design is important

It is very important to have good database/Application design as per our current application requirement. But, it is also very important that we should keep in mind that, this is not the only application which uses this database and we are not only one who use this, there may be lots of other applications which may develop on this database. So we should try our best to design one strong base so that other peoples also can easily scale application and can build new features on it.

One very good example of good design is the way Oracle build core database engine /architecture, way back in the past. If you are DBA, then you might note that, despite addition of lots of new features in each version of Oracle RDBMS, the core of Oracle remains same. There is hardly any change on core structure to accommodate/build more feature on it.

Oracle has layered (storage) architecture, and at core it has block(Managed block, for it's own use) than extent than datafile, redo log file, control file. And because of this intelligently designed architecture, it is easy to add functionality and features without changing or rewriting core structure. (From here, I can see that, one day oracle RDBMS not even require OS and because of this, it would be very very fast database engine. ASM is just first step towards this)

Just for one example, take a look on below up-gradation of Oracle RDBMS

• Oracle first have redo log files, which fills circularly.
• Next time Oracle decide to archive them before it get overwrite and we have "Archived Log File"
• Than Oracle decide to use these log file for database recovery.
• Than Oracle thought, if Archived Log file is used to recover the database from which it originates then it might be possible that we can recover copy of original database and upgrade it, and result of this is we have luxury of "Dataguard" and now in 11g there is Active data guard as well....and this up-gradation going on and on...

Similar way, you can consider the improvements in Oracle memory (SGA/PGA) architecutre.

Back to the main point from world or Oracle....!!
Recently, I have one assignment to develop small application for "time calculation" functionality.
When I analyze the architecture and design of database I realize that, it would be much complex to complete this task than my first estimation.

The database misses the core rule of normalization. I don't want to post original design in detail over here as it is strictly confidential things but let me explain it why it is missing some basic design rule with one example.

There is one table which maintain working hours for weekdays. and it is store like one mention below:-
Old Design
Day | Hour(Varchar)
MON| 8:00-12:00,13:00-17:00
TUE | 8:00-12:00,13:00-17:00

As you can see original designer save lots of column :) and uses only one varchar column to save all time part in it. (Thanks god, this enterprise has only one break during the day :).

There are more than one way to redesign this table based on different requirements.
For example we can change this as one shown below:-

Day | InTime | Outtime | Breaktime
Mon | 8:00 | 17:00 | 12:00-13:00

Because of old table design, I have to do some string manipulation operation and type casting operation to get data that I need for my purpose. I wonder, how many other application uses this table and how many other people have to do type casting and string manipulation operation to get data in correct(usable) form. Because of all this, it took double of time for me to complete this task than it would takes if this table design follow some normalization rule.

So, next time when you start working on database design just remember that your database design would save lots of time and efforts of other people in future, if it has really good design.