I'm redesigning a health insurance costs database (actually they're not really insurance plans in my country but I think that for the sake of the example it doensn't matter). We're the health plan consulting firm so we don't provide health plans, we manage health plans on our client's companies. My problem lies specifically on the relationship between the insured and his or her dependants.
I've chosen to model the relevant situation as follows:
Insured attribute named
type is important here. It identifies if the insured person in question is the 'primary plan member' or a 'dependant'. But, for dependants, and dependants only, we need a
relationship_type attribute ('spouse' or 'child').
If I simply add an
relationship_typeattribute to the
insuredentity type (and a column the corresponding logical table), there'll be multiple null marks when the insured person is a 'primary plan member'.
If I create a
relationship_typeentity type and the respective logical table, the foreign key column will retain null marks on 'primary plan member' rows in the
Both options include a lot of nulls and I've read that that's not a good sign.
relationship_type is an attribute that depends on the value of another attribute and I don't know how to implement that. Could you share some insights about how to model this situation?