[jdjlist] RE: Another DB design (was: Interview Question: DB Desi gn from Objects)

[Tom Jordan]

Title: [jdjlist] Another DB design (was: Interview Question: DB Design from Objects)

Greg,

 

The duplicated name and address fields are problematic. What if a customer's name changes (typically when a woman marries or divorces) or changes address? The name and address fields in all of the customer tables must change to the new value for that customer id.

 

Furthermore, how do you assure that the customer's name and address is consistent in all of these tables? Triggers? Java code? Either way this problem adds complexity and is somewhat error prone.

 

For these reasons redundant data is not recommended.

 

 

I agree that your 8-deep class hierarchy is difficult to manage with all of the rational database techniques discussed so far. IMO the classic solution described by Tomm Carr is the best overall (or "least objectionable", depending on how you look at it). But let's face it, your class hierarchy is a "round peg" and a relational database is a "square hole". The frustration you are experiencing is due to the inherent impedance mismatch between the two.

 

If you are not satisfied with that (and you have good reasons not to be) consider an OODMS such as Mattisse (www.fresher.com/product_information/). I will stop short of claiming this is a "magic-bullet" solution, but your objects will map directly to your database and this is especially important with deep class inheritance and nested containment. Additionally, Matisse lets you access their OODMS with SQL, so you don't have to learn a new query language. The development kit is a free download (deployment fees are reasonable and negotiable). I have used it and it works.

 

Let me know what you think,

Tom J

 

P.S. White paper on Object-SQL database: www.fresher.com/pdf/product_information/collateral/the_emergence_of_the_object-sql_database.pdf

-----Original Message-----
From: Greg Nudelman [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 02, 2002 2:30 PM
To: JDJList
Subject: [jdjlist] Another DB design (was: Interview Question: DB Design f rom Objects)

"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
----------------
----------------

To change your membership options, refer to:
http://www.sys-con.com/java/list.cfm

To change your membership options, refer to:
http://www.sys-con.com/java/list.cfm

THIS TRANSMISSION, INCLUDING ANY ATTACHMENTS OR FILES,

CONTAINS AIRNET COMMUNICATIONS CORPORATION CONFIDENTIAL

AND PROPRIETARY INFORMATION WHICH MAY BE OTHERWISE EXEMPT

FROM DISCLOSURE.

The information is intended to be for the exclusive use of the individual

or entity named above. If you are not the intended recipient,

be advised that any disclosure, copying, distribution or other use

of this information is strictly prohibited. If you have received this

transmission in error, please notify us by telephone at 1-321-984-1990 or

by email to [EMAIL PROTECTED] immediately and do not read, print

or save this information in any manner.