php - Object-oriented-like structures in relational databases

Question

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 1

.. 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.