1

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.

VansFannel
  • 45,055
  • 107
  • 359
  • 626
  • 1
    Yes, but where is the problem that needs solving or help? – felixgaal May 20 '12 at 06:44
  • I'm asking if this design is correct. I'm not sure if adding qapId column to Order and E-Report table is correct. – VansFannel May 20 '12 at 06:47
  • 1
    Based on how you described it, it looks good to me. You'll just need some business logic somewhere to ensure data integrity; e.g. if an Ereport is assigned to an order that already has a qapId, what happens to the qapIds in the Ereports and Orders table? – Tom May 20 '12 at 07:01
  • @VansFannel - Is it possible for an `EReport` to have a null `orderId`? Or do all `EReports` have to have an `Order` at all times? – Joel Brown May 20 '12 at 11:33
  • @JoelBrown: No, an `EReport` always will have an `orderId`. Yes, `EReports` will have an `Order` always. – VansFannel May 20 '12 at 15:47
  • 1
    @VansFannel - 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. – Joel Brown May 20 '12 at 18:32
  • @JoelBrown: the right answer it's your last comment. If you add an answer with it, I'll check it. – VansFannel May 23 '12 at 13:12

3 Answers3

1

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.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • Thanks for your response. I don't think that if an Order hasn't got a QAP E-Reports could have different QAP will imply that there's only one QAP per order. If Order doesn't have a qapId, its E-Reports could have differents qapId. – VansFannel May 20 '12 at 17:57
  • @VansFannel: You said that, if Order hasn't got a QAP, then E-reports that have different QAPs is a problem. (See the last quote from you in my answer.) If E-reports aren't supposed to have different QAPs, then they must need to have the *same* QAP--so only one QAP per order. – Mike Sherrill 'Cat Recall' May 27 '12 at 22:05
1

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:

enter image description here

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.

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
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.

Joel Brown
  • 14,123
  • 4
  • 52
  • 64