"Curiouser and curiouser!"
Alice in Wonderland
OK, here is ANOTHER DB design scheme I have just heard yesterday, so I want to run this by everybody. My acquaintance tried to model his DB tables according to the "classic" accepted way, that is how Tomm Carr and many others have suggested. Shared attributes in a "root" table and all the object-specific child-class attributes in other tables, joined by FKs.
Unfortunately, his schema was quite extensive and was changing rapidly during development. So, my acquaintance run head-first into exactly the same problems I have mentioned: 7-8 join queries were the norm and SQL base quickly became unmaintainable due to constant changes in the schema.
So my acquaintance implemented a MODIFIED 3-table design:
CustomersSuperclass
----------------
id
objectType
----------------
----------------
2 tables:
Customer
----------------
id
name
address
favorites
comments
----------------
----------------
WholesaleCustomer
----------------
id
name
address
limit
pin
department
----------------
----------------
As you can see, this results in some field duplication (name, address), but the tables are still pretty well normalized. A huge advantage of this scheme, is that you can still perform reports on the entire set of various Customer subtypes, but in order to read an individual customer, you no longer need to perform a join. Instead, we can just read it directly from the WholesaleCustomer table. The added bonus is that you end up with no null fields and additional data integrity that you do not get with a 1-table design.
Any thoughts?
Greg
===========================================================
ORIGINAL PROBLEM:
CustomersAbstractSuperclass
----------------
id
name
address
----------------
----------------
inherited individually by:
Customer
----------------
favorites
comments
----------------
----------------
and:
WholesaleCustomer
----------------
limit
pin
department
----------------
----------------
http://www.sys-con.com/java/list.cfm
