I have following class model that I’m quite happy with:
A Whole has a special mandatory Part. Furthermore it has a collection of parts, that contains at least the mandatory part, but may contain many more. In the OO model parts don’t need to know where they belong.
So now I came up with this RDB model:
Whole:
id - primary key
special_part_id - non-nullable, unique, foreign key to Part
Part:
id - primary key
whole_id - non-nullable, foreign key to Whole
The only constraint that is not checked here is Whole = Whole.special_mandatory_part.whole.
However, with this table layout I’m not even able to insert rows. Might be worth mentioning that I’m using an ORM, but even with plain SQL I would not know how to do it.
So my questions are:
- Is this DB design bad, and if so why?
If not, how would you insert rows?- What design would you recommend and why?
I’m aware of this very similar question. However, because of the not null constraint my problem is slightly different (worse I’d say) and I’m not satisfied with the given answers.
UPDATE Just found an answer on how to insert rows with a table layout like this.
First the evident errors:
special_part_idinWholeshouldn’t existwhole_idinPartshould be a FK toWhole, not to user
That way the relationships is that Whole is comprised of one or more parts.
Now to clarify something:
- Conceptually you can model a one-to-many relationship that is mandatory both ways, but physically it’s imposible to implement. You can only implement it being mandatory on one side and not-mandatory in the other. In this case, it’s mandatory on the Part side, but optional on the Whole side.
8
Stop trying to model business rules in your database, it is a time sink that will get you no where. It is also distracting you from modeling these rules where it is appropriate to do so, your business object layer.
Databases have one job only, and that is to persist your application’s state.
Please do not take this to mean that basic referential integrity checks are not a good idea; they are. But, when it comes to the minutia, like X must have one Y, that is the role of your application.
It might be more flexible to add a IsPartRequired (bit) column to your part table and let the application do the enforcement. It might also be possible that you are trying to put something in the DB which does not need to be there at all.