Folks,

For that n-th time consecutively, i am striking the same kind of problem again. It comes down to "how do you map OOP structures to database tables inside a painless way."

Here is a scenario: I've various kinds "stars" during my system - employees, companies, contacts. They've certain bits of functionality in keeping other pieces are greatly different. The organizations that stars cope with are "communications", "notes" (admins prefer to leave notes on clients), along with a couple of more. You will find a lot of kinds of other organizations that every actor type handles, as the others don't.

Presently, my database schema includes tables for:

Stars:

  • worker
  • employer
  • contact

Organizations:

  • communication
  • notes
  • etc.

Association tables between organizations and stars:

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

This feels as though a "code smell" in my experience. Each time a customer changes their role (i.e. marketed from "contact" to "employer"), a lot of crazy scripts have to be run. Yuck... However, basically was operating inside a purely OOP-driven world, this is much simpler - possess a base class for those organizations with common qualities, and be happy with it...

Within the DB world, that option appears theoretically possible, but sounds very untidy... I.e. basically appreciate this right, I'd possess a new base_actor table, and one another actor might have basics_actor_id, and so the associations could be between base_actor and also the organizations... However, how do you do reverse-association queries? I.e. "show me all communications with only stars of type worker"?

Any advice? Any general ideas about "mapping OOP structures to relational DB"?

It's known as ORM or Object Relational Mapping. You will find a large number of items that purport that will help you map OO structures to relational tables. Ruby on Rails, for instance, offers Active Record to assist bridge the divide. For PHP you've Propel and Doctrine and Porte and many more.

.. It comes down to "how do you map OOP structures to database tables inside a painless way."

You do not.

Object oriented and relational algebra are two essentially different paradigms. You cannot transition together with no subjective interpretation. This really is known as an impedance mismatch, and it has been named the The Vietnam laptop or computer Science.

The apporach you suggest appears justified in my experience. You can include a actortype column for your actor-base table for distinguishing between various kinds of stars. The PK of every specific actor table will be a FK towards the actorbase table to prevent 'hairy' queries and also to emulate the inheritance-like 'is-a' relationship.

What you're searching for is Disjoint-subtypes ... ORM is really a hack.