The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

mapping class hierarchies to relational databases

Hey guys,

I'm no expert in databases, but right now, I'm grappling with the following problem:

1. Say i have class hierarchy, an abstract class that is inherited by a say, 3 concrete classes.
2. Say i want to save object data in a relational database.
3. How should i go about mapping the objects to tables? More specifically, should i have a separate table for each concrete implementation of the abstract class? OR should i have just one table with say, 50 fields, and let all objects be stored in that table? This is really bugging me, since I can't come up with a solution that can easily scale as more concrete classes are added. I'm sure that this is a problem that has been faced many times before, so i'd be interested to get your input!
Sarge Send private email
Monday, July 02, 2007
 
 
Just wanted to point out a third option, which is to have one table with the data from the abstract class, and a table per-subclass which is related to the abstract table. This probably has slightly less impact when adding subclasses, since the tables holding the data for the other subclasses are not affected.

I would also take a look at a few ORM frameworks (Hibernate etc) which can handle the mapping pain for you whilst still offering flexibility in the way your tables are structured.
Anon
Monday, July 02, 2007
 
 
Your approach is totally wrong. Instead of creating a class hierarchy first and a database structure last you should reverse it - design a properly normalised database, THEN create a class for each table. This avoids the need for that abomination called Object Relational Mapper (ORM).
Tony Marston Send private email
Monday, July 02, 2007
 
 
+1 for Tony. If your ORM is at all difficult, you've done something wrong.

That said, each class becomes its own table; the primary key of the superclass (abstract or not) becomes the primary key of the subclasses as well as a foreign key to the superclass.
Steve Hirsch Send private email
Monday, July 02, 2007
 
 
One of the Hibernate books (maybe Hibernate in Action, but don't quote me on that) has at least one, maybe two chapters on this.

There are three ways of doing it:
1) One table
2) One table per concrete subclass
3) One table per abstract or concrete class

The book goes through the pros and cons of each of these in great depth.  The short answer is 'it depends.'
Michael G Send private email
Monday, July 02, 2007
 
 
To put the above some perspective: there are multiple schools of ORM, reflected in implementations like nHibernate, CSLA, BLToolkit, upcoming dlinq etc. There are even people, who refuse to accept ORM altogether ;)

The thing is, that OO and relational data structure designs are different enough to make sure it's hard for them to peacefully share non-trivial data structures. An architect has to decide how much functionality should be placed in database and application code (BL), based on functional requirements and considering restrictions, applied by chosen ORM solution. Naturally, both DB- and BL–heavy solutions have their advantages and problems.

At the moment (and for a non mission-critical application) I’d try to go with XML fields in SQL Server 2005 and a simple generic XML-serialization solution (with full awareness that this would raise a good deal of ungooglable questions).
DK
Monday, July 02, 2007
 
 
It's called the object-relational impedance mismatch:
http://en.wikipedia.org/wiki/Object-Relational_impedance_mismatch

And definitely +1 for designing your database schema first!
Mark Pearce Send private email
Tuesday, July 03, 2007
 
 
We design our database first, then design our objects - we code gen them off the database - HOWEVER, this is not a blind process, and there needs to be thought into the role and responsibilities of each class.

Some tables in your database design should not result in classes at all.  And in some (well thought-out) cases you may have multiple classes with a single base table.

Specifically, to implement many-to-many relationships, you will have "link" tables.  There should not be corresponding classes in your object model.  Instead there will be either subordinate collections or methods which return collections or related objects.
Cade Roux Send private email
Tuesday, July 03, 2007
 
 
Thanks for the link, Mark. It's a good reading.
DK
Tuesday, July 03, 2007
 
 
> Specifically, to implement many-to-many relationships,
> you will have "link" tables.  There should not be
> corresponding classes in your object model.  Instead
> there will be either subordinate collections or methods
> which return collections or related objects.

I disagree. If I want to make changes to a "link" table then I do it through the class that deals specifically with that link table.
Tony Marston Send private email
Wednesday, July 04, 2007
 
 
I agree with Tony. The idea that intersection tables are non-entities (pun not intended) to be ignored where at all possible doesn't work very well in the real world. Not only are there instances where the intersection is its own non-trivial entity in its own right, but even where it is just two foreign keys, leaving it out of the logical and object models doesn't make either of them clearer.

BTW, if you're into these things, Oracle has a built-in ORM that lets you model your objects in the database itself, while it surreptiously converts them into tables.
Steve Hirsch Send private email
Wednesday, July 04, 2007
 
 
"Your approach is totally wrong."

Well, that's a bit strong. I'm building a smallish system now and deferring the database until last. I have a spike of some of the high risk areas running with flat files. When I have a working system with all data provided through test fixtures, then I'll worry about persistence. That won't always work, but it's not always "totally wrong" either.
Stan James Send private email
Thursday, July 05, 2007
 
 
> That won't always work, but it's not always "totally wrong" either. <

It is for the kind of applications that I have been writing in software houses for the last 25 years. If a cient wants an order processing system, a stock control system, a subscriptions system or whatever, the starting point is ALWAYS the database. You have to know what entities are required, what attributes each entity will need, and the relationships between those entities so that you can design the system. This includes identifying how many tasks/programs you will need, the screen/report layout for each of those tasks, the data access requirements for each of those tasks, and the business rules. It is only AFTER such a design process can you begin writing any code. Anybody who leaves the database design till last would not survive past the first project in the highly competitive world of software houses.
Tony Marston Send private email
Sunday, July 08, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz