Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

closed as off topic by Jack Douglas May 23 '12 at 12:30

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

    
How do you determine when a QAP is associated with an Order vs. an EReport? If an EReport must have a QAP, and an Order must have at least one EReport, then it seems weird that an Order could have its own QAP. –  Aaron Bertrand May 19 '12 at 19:02
    
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. I'm not interested in orders, I am interested in E-Reports filled by user, and QAP have information needed to fill it up. –  VansFannel May 19 '12 at 20:22
    
Then it seems the qapid column on the Order table is redundant, no? If an order has multiple EReports, which qapid is in the Order table? Why store just that one a second time? –  Aaron Bertrand May 19 '12 at 20:26
    
So, if it is redundant, how can I know which QAP is need it to fill E-Report up? –  VansFannel May 19 '12 at 20:29
    
There is a qapid on each row in the EReport table, right? –  Aaron Bertrand May 19 '12 at 21:11
show 6 more comments

Browse other questions tagged or ask your own question.