I have three tables :
1. tblDiseases(diseaseId, diseaseName) where diseaseId is the primary key
2. tblSpecializations(specializationId, specializationName) where specializationId is the primary key
3. tblDiseaseSpecialization(diseaseSpecializationId, specializationId, diseaseId) where diseaseSpecializationId is the primary key
All diseases need not have an associated specializationId. In the disease registration table with fields for person ID and disease, I would like to store the diseaseSepcializationId rather than keeping separate fields for diseaseId and specializationId. But in this case, if a disease has no specialiations, we don't have any diseaseSepcializationId for storage.
Even if the disease has no specialization related to it, we can keep a row in tblDiseaseSpecialization with value null for specializationId. But this will create problems when at a later stage specializations are added to that disease.
How can I design a database which allow me to store diseaseSpecializationId in disease registration tables.