2

Lets assume we have to model the following entities:

  1. Application : Application submitted by users
  2. Application Study: the study to determine if the Application should be accepted
  3. Consultation : A consultation with an external party as part of the Application Study. Consultations with external parties could also be initiated independent of an Application Study.

So we have entity Consultation that can have parent Application Study or stand on its own. What is the best way to model this in a relational database?

I see 2 options:

  1. have a nullable foreign key in Consultation table referencing Application Study
  2. have 2 tables: Application Study Consultation with a non nullable application_study_id and another Consultation table without any reference to application study.

Which is the best practice way?

EDITED: There is a ONE-TO-MANY relationship between Application Studies and Consultations. In other words, an application study can have multiple Consultations. Hense the foreign key. In addition, an Application Study may or may not have a consulation.

Improve this question
4
  • 4
    The standard approach for designing database models is: start with a normalized model first, and de-normalize only when you really need it (for example, after you measured some performance bottleneck). Keeping that in mind, I am sure you answer your question by yourself.
    – Doc Brown
    Commented Sep 17, 2016 at 7:44
  • @chrisl08: You need to be a bit more detailed about the business logic. Does every study have a consultation? Can a study have more than one consultation? Without this info it is impossible to answer.
    – JacquesB
    Commented Sep 17, 2016 at 14:07
  • It is possible that in this domain a Consultation may also relate to zero, one, or even more than one Application Study?
    – Erik Eidt
    Commented Sep 17, 2016 at 14:38
  • No, it is a 1-* relationship between studies and consultations
    – chrisl08
    Commented Sep 17, 2016 at 14:42

1 Answer 1

Reset to default
3

  • Option 2 is ruled out because you don't need a join table for 1:M relationships.

  • The relationship between APPLICATION_STUDY and CONSULTATION is 0:M, not 1:M.

  • Option 1 is the correct one. That's the usual way of modeling 0:M relationships: a nullable FK on the MANY side.

EDIT : Note that the 0 in 0:M mean 0 or 1, which is why you have a nullable FK on the many side.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.