2

I have following class model that I'm quite happy with:

Class Model

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.

2

First the evident errors:

  • special_part_id in Whole shouldn't exist
  • whole_id in Part should be a FK to Whole, 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.
  • whole_id being a FK to User was a c&p error, thanks for pointing that out – raphinesse Jul 10 '14 at 14:59
  • I agree. If a part is to be treated specially, Part table should have a flag column indicating as such. – Neil Jul 10 '14 at 14:59
  • @Neil what about performance when using a flag and having a large number of parts? – raphinesse Jul 10 '14 at 15:09
  • @user61852: so are you suggesting the same solution as Neil? It just feels so wrong to me ;) – raphinesse Jul 10 '14 at 15:10
  • @raphinesse I don't see Neil's answer. I only see mine as of now. why you say mine is the same as Neil's ? – Tulains Córdova Jul 10 '14 at 15:47
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.