mercredi 20 avril 2016

How to build a relationship between multiple sub-entities and a list attribute?

The model of the (PHP & MySQL based) application I'm currently working on contains a inheritance similar to the one described here. Simplified for the purpose of this question the classes structure can look like this:

enter image description here

To mat this to the database I'm using the Class Table Inheritance design pattern. Here is the physical data model:

enter image description here

The most specific attributes are actually specific for every single subclass. But there are some attributes, that are needed in several classes (but also not in all of them -- otherwise they could be managed in the Foo class/table). When it's a simple attribute, it causes some code duplication, but isn't a big problem. But there are also some cases with complex attributes.

E.g.: FooTypeBaz and FooTypeBuz should contain a list of Whatever elements.

Normally I would implement this 1:n relationship with a table whatever containing FOREIGN KEY. But in this case I would need multiple FOREIGN KEY columns whatever (for foo_type_baz, foo_type_buz, and maybe some tables more). It's dirty.

Another solution: Something like a "facade" table for the table whatever:

enter image description here

Looks better (for me), but I'm still not happy with this model.

How to build a relationship between multiple sub-entities and a collection/list attribute? Is there an elegant solution for this problem? Maybe a best practice / design pattern?

Aucun commentaire:

Enregistrer un commentaire