Object-oriented-like structures in relational databases

.. It's about "how do I map OOP structures to database tables in a painless way."

You don't.

Object oriented and relational algebra are two fundamentally different paradigms. You can't transition between them without a subjective interpretation. This is called an impedance mismatch, and has been dubbed the The Vietnam of Computer Science.

It's called ORM or Object Relational Mapping. There are dozens of products that purport to help you map OO structures to relational tables. Ruby on Rails, for example, offers Active Record to help bridge the divide. For PHP you have Propel and Doctrine and Porte and many others.

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.