Redundant relationship?

Question

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:

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.

Answer 1

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.

Answer 2

An order could have zero or one QAP.

Making Order.qapid a nullable foreign key will fit this requirement.

An order could have one or more E-Report.

Making EReport.orderid a non-nullable foreign key will accommodate that requirement, but won't quite fit it. There's no declarative way to require that every order have at least one EReport. You could do that using updatable views, triggers, etc, though. But it's not crystal clear that you mean "Every order must have at least one EReport," which implies that a row in EReport is created at the same time as a row in Order.

An E-Report must have one QAP.

Making EReport.qaoid a non-nullable foreign key will fit this requirement.

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.

If you're copying an order's QAP to an EReport, you might be approaching this whole problem the wrong way. You also said

My problem is if Order hasn't got a QAP E-Reports could have different QAP.

which implies that there's only one QAP per order. It might be that you need to store one and only one QAP in the Order table, and no qapid at all in the EReport table.

Answer 3

So you want to represent a situation where Order doesn't have QAP, but it's EReports do, and it must be the same one?

Well, since EReport must always have Order, you can omit the EReport.qapId and rely on Order.qapId, something like this:

If Order.forOrder is false, then your client application can interpret Order.qapId as if it only applies to child EReports, otherwise it applies to both Order and EReports.