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"?
See Question&Answers more detail:os
