How to model common data for different kind of objects in a relational database?

Question

In a current project, we are creating an application with some entites like Customer, Project, Company, Ticket, Request and more. The model of these is straight forward. The complicated part where I can't get my head around is the following:

We would like to have some common entities that can be "attached" to various objects. Some examples include Documents, Tags and Notes. Each of those should be manageable for each of the above mentioned domain entities.

In the server-side code (Java), we want to implement the common functionality as components and reuse/embed them on the specific edit pages of our domain objects. We are using an ORM and would also like to reuse those "metadata" classes.

I think this use-case would be easy to model with a document database. But how would this be done in a relational model?

Answer 1

What you're asking for is a way to make your relational data model fit an arbitrary code constraint, namely: all "attached" items use a single ORM class. Rather than tell you how you might do that, I'm going to try to explain why this is not a good idea and what you should do instead.

Code reuse is a great principle to apply to code. Data isn't code, and relational databases shouldn't be designed primarily to facilitate code reuse. You should design your database for data integrity not for code reuse. Designing for data integrity means that you should treat distinct types of things in distinct ways, even if they happen to "look similar" at present. This is what relational databases are designed and built to do. They aren't designed to optimize for code reuse.

Furthermore, ORM code is generally not reusable, because it's meant to reflect the structure of and to operate on specific tables. Even if you happened to have multiple tables that all looked alike structurally and even if you defined interfaces which allowed these tables to work through a common set of code, you still have the need for the data to be persisted in different tables. There's also the matter of ORM code generators, which are just not built to look for code reuse opportunities like what you're trying to achieve.

You could conceivably develop your own ORM (I built one myself in the 1990's). If you did this you could build it in such a way as to make it meta-data driven at runtime. This would give you the feel of using a document database in code while storing your data in a relational database. However, what you're really doing is mashing a square peg into a round hole. If your application calls for a document database you'd be better off using one instead of a relational database.

The best you could (and probably should) try to achieve is to make a reusable code component of the middle tier/model code (and the front-end/view, if possible) which branches internally at the back-end/data tier to use per table ORM classes. This lets you achieve the benefits of using relational databases, the benefits of using ORMs for persistence code, and the benefits of components in your business layer and user interface code.

Answer 2

_{Community wiki answer:}

The functionality in question should be managed via application program code (it entails computational capabilities), perhaps OOP elements.

If, on the other hand, the informational requirements demand handling certain things (let's say, AttachableEntities) that have some properties in common, then modeling them with a conceptual supertype-subtype structure might be called for.

If that's the case, you may find the accepted answer to How to model an entity type that can have different sets of attributes? of interest to deal with the structural aspect of the database.

Answer 3

Each of this entities would have a relation table between them and the possible other entities they can be related to. These will at least contain the id of the objects on both sides, but perhaps also a number of characteristics, although my imagination fails me currently. These tables implement a many to many relationship.