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.