[jdjlist] RE: Interview Question: DB Design from Objects

[Greg Gent]

Title: Interview Question: DB Design from Objects

I would propose that the Object Model from which you're designing the tables is flawed.

 

A WholesaleCustomer is, indeed, a Customer, no?  Especially given the data elements, a WholesaleCustomer would still have preferences and comments.  Now a model of:

 

Customer(abstract)

WholesaleCustomer extends Customer

RetailCustomer extends Customer

 

might make a little more sense, to me anyway.

 

As to the table design INHO both, er...all three designs, are perfectly acceptable.  DB design is most always an exercise in trade-offs.

 

The question that really needs to be asked before tables are to be designed is "how will the database be used?".

 

DO you need to query combined Customer data...and if so, how frequently?

 

DO you need to support user ad-hoc queries...or are all queries developed into canned reports?  (reports/queries maintained by your development staff having complicated joins may be acceptable)

 

What are the performance requirements of the above queries?

 

If you're asked Design "normalized" tables from .... then his initial point is indeed valid.  Although, in my experience, a normalized (as normalized as developers could bear) was never easy for ad-hoc user reports.  Some tools out there assist in mitigating the difficulty in understanding joins (but usually at the cost of a developer in-house maintaining a catalog of sorts).

 

Design, even DB Table Design, is an art.  There is no correct answer.  As I stated above, one may be more preferable in one scenario or another....more correct?

 

Just my .02

 

Greg

 

 

-----Original Message-----
From: Greg Nudelman [mailto:[EMAIL PROTECTED]]
Sent: Thursday, June 27, 2002 3:42 PM
To: JDJList
Subject: [jdjlist] Interview Question: DB Design from Objects

Design DB tables from the following Object scheme:

CustomersAbstractSuperclass
----------------
id
name
address
----------------
----------------

inherited individualy by:

Customer
----------------
favorites
comments
----------------
----------------

and:

WholesaleCustomer
----------------
limit
pin
department
----------------
----------------

+++++++++++++++++++++++++++++++++++++++++++++++++++++

I proposed a single table:

Customers
----------------
id
name
address
favorites
comments
limit
pin
department
objectType

to which Mr. interviewer replied: this will waste space.  As WholesaleCustomer record will have Customer fields empty (and vice versa). Also, you will be required to search one giant table by specific objectType to get customers or wholesale customers:

SELECT * FROM Customers WHERE objectType=1

I said OK, alternatively (WORSE) you can also split this in 2 tables, Customer and WholesaleCustomer.

However, if you often have to run querries against the whole set, you'll end up with lots of redundant app logic or SQL joins/unions of 2 tables to do composite sorts, paging through the whole collection, and getting totals/averages from 2 types of objects.

He proposed a third scheme then.

1) Can you guess what it was (hint: he used 3 tables)?
2) Whould this third scheme be better then my single table?  Why?

Any thoughts would be MUCH appreciated. 
Let's hear some good discussion!!

Thanks,

Greg

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