Search code examples
databasedatabase-designrelational-databasetable-relationships

Redundant relationship?

I'm trying to design a database model and I have a question.

I need to represent this:

An order could have zero or one QAP.
An order could have one or more E-Report.
An E-Report must have one QAP.

To represent this, I have this model:

enter image description here

If an orden doesn't have a QAP, user must select one to add it to an E-Report. If an order has a QAP, user doesn't need to select one to add it to an E-Report because all E-Reports will have the order's QAP.

A user will receive an Orden ,adn then he will fill up E-Reports. I'm not interested in orders, I am interested in E-Reports filled by user, and QAP have information needed to fill it up.

My problem is if Order hasn't got a QAP E-Reports could have different QAP.
For example, Order1 doesn't have a QAP, but it has two E-Report:

  • E-Report1 has QAP1, and
  • E-Report2 has QAP2.

Is this database design correct?

I'm not sure if repeat qapId column in Order table and in E-Report table is correct.

Solution

  • Since EReports can have different QAPs before the mandatory Order gets a QAP, there is no way to use declarative referential integrity (alone) to represent your constraints. The model you have now is what you need to use, in combination with some procedural logic to impose your rule that EReports must share the same QAP if their Order has one.