I have following class model that I'm quite happy with:
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.