Best Database Design Practices

[Ken Cooper]

Hi All,

I have been asked to redesign a database application that has run into
problems and need to justify any changes I make.

At present there are multiple tables storing price information. I know that
similar things should be stored in the same place. There should only be one
price table and that should store all prices regardless of product type (A
field can be used to relate product prices to another table of product
types).

Is this a normalisation rule or some other design principle?

Tables should only be used to store information about one type of thing, but
what rule is being broken when similar things are stored across multiple
tables?

Can anyone point me to a good authority that will back me up when I
amalgamate these separate tables into a single price table?

Thanks for any help,

Ken

 

[S.L.]

Hi Cooper,

For me, Database design is based on purpose of your application. There are
some situations those cause a single table to be split into many tables Or
single field appears into many tables. One of these is whenever you have to
balance between performance of your application and degree of program
processing. I always found that If I care about performance, it seems to me
to split table or field and that cause my program harder to write, more
complex algorithm.

Normallization has more than 3'rd normal form. You have to follow this rule.
But finally, as my teacher teach me, after follow the rule and you found it
not suitable, you can break the rule.

For my Sales Price table, my fields are Product Code, Customer Code, Start
Effective Date, Sales Qty Level and Price Per Unit. The first 4 fields
combined as primary key. This proof that your sentense "...and that should
store all prices regardless of product type (A..." is not correct (if
analyzed in my business factor).

In the case that you split into many tables, You must have reason to answer
Why you do that ? If you can not answer yourself, Follow the rule and
combine into single table. Handbook of Relational Database Design by Candace
C. Fleming (Author), Barbara von Halle (Author) (Hardcover - January 1989)
is my backup thory. You can find everything in this book. Although this book
is old, It always new for every DB designer. Oh! one thing to realize is In
this world why Distributed DBMS is very very expensive ??? It break the
normalize rules, doesn't it ? (Answer : Because business is the key factor
for DB design)

HTH,
Suntisuk