I'm not sure it is possible to do what you are trying to. What you are trying to do seems to contradict itself. What I mean by this is the following:

1) You have a registration table to track patients.
2) You want to track which diseases that patient has by going through the tblDiseaseSpecialization table
3) Not all Diseases have a Specialization.
4) No record in the tblDiseaseSpecialization table can have a NULL Specialization.

Now, if a disease does not have a Specialization, then it can not be recorded in the tblDiseaseSpecialization table because NULL Specializations are not allowed. As a result, that disease can not be recorded in the registration table because no tblDiseaseSpecialization record exists.

What is wrong with using a table with NULL values? My only suggestion is to create a dummy Specialization called "No Specialization". This will allow you to record the dummy specialization as a specialization for diseases that don't have a true specialization while avoiding NULL specializationIDs; however, again, I don't see what is wrong with NULL specializationIDs.

Hai timothybard,

Thanks. I meant exactly the same thing you quoted. The problem I thought of with storing NULL values for diseases having no specialization is that if I want to insert some specializations to that disease at a later time, I should delete the existing disease specialization record wtih value "No Specialization" and another set of records must be inserted for that disease. In this case, if I registered any person with the diseaseSpecializationId corresponding to a "No Specialization", after update I won't be able to track the disease of that person (and in most database design, such a modification will be prevented by referential integrity constraints).

roshni.

If you use a "No Specialization" record, you would still be able to track the disease for that person.

Suppose the tables have the following records:

tblDiseases
ID, Disease Name
1, Disease 1
2, Disease 2
3, Disease 3

tblSpecializations
ID, Specialization Name
1, No Specialization
2, Specialization 1
3, Specialization 2

tblDiseaseSpecialization
diseaseSpecializationId, specializationId, diseaseId
1, 1, 1
2, 1, 2
3, 2, 3

Therefore, if a person has a DiseaseSpecialization ID of 1, you know that they have disease 1, which has no specialization. If you have a person with a DiseaseSpecialization ID of 2, you know they have disease 2, which also has no specialization. Therefore, even through both people have a disease with no specialization, you still know exactly which disease they have.


Back to the larger issue; even if you use the No Specialization record, if a disease later has a specialization, you will need to either update the DiseaseSpecialization record for that disease from No Specialization to the correct Specialization or delete the No Specialization for that record. You would have to do the same thing if the SpecializationID for that record was NULL. Therefore, it looks like you would have the same issue whether you use NULL or No Specialization. The issue is that you would either need to edit the record or delete the record and create a new one.

If you really don't want to delete records or modify records, then you could program the database so that the table still has a NULL record or a No Specialization record but those records are ignored if other records for that disease is present. That would require you to create a series of queries to perform that task and then you would have to use the resulting query as your source to determine whether or not to show the DiseaseSpecialization record (hiding the record would be done with a WHERE condition in all of your SQL statements that display DiseaseSpecialization).

Thanks timothybard for such a detailed reply. We have decided to keep 'No specialization' records even after new specialization are added to a specific disease so that old patients can still refer to their disease. Thanks for your valuable help.

roshni