Lets assume we have to model the following entities:
- Application : Application submitted by users
- Application Study: the study to determine if the Application should be accepted
- 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:
- have a nullable foreign key in Consultation table referencing Application Study
- 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.