Object-oriented-like structures in relational databases [closed]

Folks,

For the n-th time in a row, i’m hitting the same old problem again. It’s about “how do I map OOP structures to database tables in a painless way.”

Here’s a scenario: I have several types of “actors” in my system – workers, employers, contacts. They have certain pieces of functionality in common; other pieces are vastly different. The entities that all actors deal with are “communications”, “notes” (admins like to leave notes on customers), and a few more. There are tons of types of other entities that each actor type deals with, while the others don’t.

Currently, my database schema includes tables for:

Actors:

• worker
• employer
• contact

Entities:

• communication
• notes
• etc.

Association tables between entities and actors:

• worker-communication-assn
• employer-communication-assn
• worker-notes-assn
• etc, you get the drill.

This feels like a “code smell” to me. Whenever a customer changes their role (i.e. promoted from “contact” to “employer”), a bunch of crazy scripts need to be run. Yuck… On the other hand, if i was operating in a purely OOP-driven world, this would be much easier – have a base class for all entities with common properties, and be done with it…

In the DB world, that option seems theoretically possible, but sounds very messy… I.e. if I understand this right, I’d have a new base_actor table, and each other actor would have a base_actor_id, and then the associations would be between base_actor and the entities… But then, how do I do reverse-association queries? I.e. “show me all communications with just actors of type worker”?

Any advice? Any general thoughts on the subject of “mapping OOP structures to relational DB”?

Answer

Here’s a solution I came up with about 10 years ago. The system that uses this design is still running, so it worked well enough to survive longer than most of my code. 😉 Today I may use one of the ORM packages that Scott mentions, but there’s really no huge problems just using SQL directly.

1. Model all of your inheritance relations as joins between tables. Each table in your system will hold the attributes of a specific class.

2. Use a synthetic object id (oid) as your primary key for all objects. A sequence generator or autoincrement column is necessary to generate oid values.

3. All inherited classes must use the same oid type as their parent. Define the oid as a foreign key with cascaded delete. The parent table gets the autoincrement oid column and the children get plain oid columns.

4. Queries on final classes are made on the corresponding table. You can either join all the parent class tables into the query or just lazy load the attributes you need. If your inheritance hierarchy is deep and you have many classes, an ORM package can really simplify your code. My system had less than 50 classes with a maximum inheritance depth of 3.

5. Queries across child classes (i.e. queries on a parent class) can either lazy load the child attributes on a per-instance basis, or you can repeat the query for each child class joined with base classes. Lazy loading child attributes based on a parent class query requires you know the type of the object. You may have enough information in the parent classes already, but if not you’ll need to add type information. Again, this is where an ORM package can help.

Virtual classes without member attributes can be skipped in the table structure, but you won’t be able to query based on those classes.

Here’s what “show me all communications with just actors of type worker” looks like.

select * from comm c, worker w where c.actor=w.oid;

If you have sub-classes of communication, and you want to immediately load all the child class attributes (perhaps your system does not allow partial construction), the easiest solution is to eager join on all the possible classes.

select * from comm c, worker w, missive m where c.actor=w.oid and c.oid=m.oid;
select * from comm c, worker w, shoutout s where c.actor=w.oid and c.oid=s.oid;

One last thing. Make sure you have a good database and correct indexes. Performance can be a serious problem if you database can’t optimize these joins.