Dear Tom,
I know nothing of the system you're working on. However, my definition of a "coding nightmare" is a little different. Mine is an 8-class deep inheritance that someone has coded following your advice.
Let me address some of your key points:
>Absolutely false. Throwing all the attributes into a single table would
>make code development a nightmare, especially when a modification to the
>table is made. It takes a great deal of extra code to work around poor
>db design.
IMHO, I disagree. You can easily code all your SQL using views. This way, the query is encapsulated in a view, so you never have any problem with internal implementation. Just like any other good OO design principle.
1-table design:
create view WholesaleCustomerView as
select
id,
name,
address,
limit,
pin,
department
FROM Customers;
3-table design:
create view WholesaleCustomerView as
(select
c.id,
c.name,
c.address,
w.limit,
w.pin,
w.department
FROM Customer c, WholesaleCustomer w
WHERE w.pk = c.fk);
Now, all you need to do to findByPK()
is to write SQL against the view:
select * from WholesaleCustomerView where id = xxx;
No matter what the underlying implementation.
I invite anyone with an hour to spare to write 10 of these views for 10 sister classes in an 8-table cascade design... This would take about a 2 minutes with a 1-table. Also let's imagine you got to write 8*10 updates, deletes, and also copy objects from one table structure to another... Note that few of these methods in code will be shared, because SQL is different! Better take the whole afternoon off...
Again, I have no wish to offend you, Tom. And I am not even saying one design is better then the other. All I am trying to point out that designing tables directly based on the object model is not always appropriate in a relational schema. We have an 8-class deep inheritance (an abomination of sorts, I realize) but to try and map all these objects to a ladder of tables that are all tied together by FKs!
This maybe a good strategy for some things, but, in my experience, here it will kill both your performance and development time, even if you use views. Imagine the changes in your SQL if you move an attribute from a superclass into a subclass or vice versa. It would, at best, take hours of programming time (these SQL changes will be very hard to find, as well) not to mention re-testing all the SQL that reads those tables.
Whereas a flattened structure, will allow you to easily re-design your objects and add any number of instance variables rapidly, without changing your SQL code drastically.
I think both 1 and 3 -table designs are applicable. Both have plusses and minuses.
My point is simply that we must think carefully, not blindly copy the code's OO structure, as SQL does not allow you to take advantage of inheritance like Java, instead it adds lots of extra work.
Greg
> > > > > > > CustomersAbstractSuperclass
> > > > > > > ----------------
> > > > > > > id
> > > > > > > name
> > > > > > > address
> > > > > > > ----------------
> > > > > > > ----------------
> > > > > > >
> > > > > > > inherited individualy by:
> > > > > > >
> > > > > > > Customer
> > > > > > > ----------------
> > > > > > > favorites
> > > > > > > comments
> > > > > > > ----------------
> > > > > > > ----------------
> > > > > > >
> > > > > > > and:
> > > > > > >
> > > > > > > WholesaleCustomer
> > > > > > > ----------------
> > > > > > > limit
> > > > > > > pin
> > > > > > > department
> > > > > > > ----------------
> > > > > > > ----------------
-----Original Message-----
From: Tomm Carr [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 28, 2002 2:31 PM
To: JDJList
Subject: [jdjlist] Re: Interview Question: DB Design from Objects
Greg Nudelman wrote:
> Dear Jason and willineuer@gmx,
>
> Thank you both for your replies -- I appreciate the support! Every
> day I learn something different on this discussion board.
>
> I think it is fair to summarize:
>
> 1) 3-table structure is more elegant, but harder to maintain in code
> then 1-table.
>
Absolutely false. Throwing all the attributes into a single table would
make code development a nightmare, especially when a modification to the
table is made. It takes a great deal of extra code to work around poor
db design.
> 2) we need to know more about the projected growth of the db and/or
> classes to build a good system.
>
Not a general rule. You can design a good, extensible database that
will work well with more than 90% of all applications without knowing
much about the applications, just following the standard design rules.
For one thing, it is rare that a single application will access the
database and each application will have different requirements. Spend
too much effort tuning to one application and almost invariably you will
hurt the others. Sometimes an important application will require some
changes to the design to satisfy its particular requirements, but this
is relatively rare and justification should be stringent.
> 3) 1-table is a good substitute for rapid development and prototyping.
>
Wrong again. An improperly designed database is just about the biggest
monkey wrench you can throw at a development effort. (Office politics
gets my vote as the worst potential obstacle.)
Tomm
To change your membership options, refer to:
http://www.sys-con.com/java/list.cfm
http://www.sys-con.com/java/list.cfm
