Hi,
I agree with Greg's comments that the design of the database depends
heavily on it's intended use. The problem is that it is normally impossible
to predict the future needs that the design will be required to meet.
Sometimes it can even depend on the processing strengths of the database
you're using.
My initial preference would be to have a "customer" table with additional
tables holding attributes specific to different types of customer. I think
it gives greater flexibility for meeting future requirements, e.g. if a new
type of customer is required you can then add a new table without affecting
the processing/datamodel for existing types.
Just my thoughts ....
Peter.
Greg Nudelman
<greg.nudelman@im To: "JDJList"
<[EMAIL PROTECTED]>
x.com> cc:
Subject: [jdjlist] Interview Question:
DB Design from Objects
27/06/02 20:41
Please respond to
"JDJList"
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
