Database Design Theory?

[Stephen Smith]

I'd like to share a bit of experience that may be of use.  Back in the 
'80s I read Cobb's book on database design and found that beyond 3rd 
normal form, much of what was said was useless in real life.  Then I 
read a book by Schlaer and Melor call Object-Oriented Database Design.  
Between the two I've struck on a wonderful way of designing databases 
that works well.

First, I look at the objects that I need to model in the database.  
Define their attributes and look for obvious patterns of interaction 
that may define other objects.  Second, I create a table for each object 
that represents the private data segment of each object. Last, I 
normalize each table to 3rd normal form. 

As I've looked at it, any relational database can, if designed well, be 
a repository of the private data segments of objects; each table being a 
class of an object and each row the private data that uniquely desribes 
and instance of that object.  By using this as the design basis, I then 
create stored procedures to access and manipulate the private data and 
object interactions, much akin to methods of an object.

The results of this approach has been extremely successful.  As with all 
database applications, change is enevitable.  Using this approach they 
become easily managable and extensible. Because the  business rules for 
using objects is imbedded in the stored procedures, it is easy to change 
the rules with out changing the objects.  By the same token, if a new 
class of objects needs to be added, its addition usually will not impact 
the existing application, thus limiting the amount of regression testing 
required to release the new version of the application.

In one instance, I created an application to track the manufacturing of 
serialized items from plain PC boards to completed units, in their 
display boxes, in cases on pallets for shipping.  The system underwent 
2-5 changes a week to support the introduction of new products and new 
customers.  Virtually all changes were implemented without the 
manufacturing floors' knowledge because the extension of the design 
never impacted the users on the floor.  Most of my changes were 
designed, tested and  implemented on the production system, simply 
because of the isolation that object oriented design gives the database.

Hope this helps, and good luck

Steve