Troubles with an attribute that depends on the value of another attribute

database-designerd

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:

Health plan mini-ERD

The 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').

My considerations

  • If I simply add an relationship_type attribute to the insured entity 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_type entity type and the respective logical table, the foreign key column will retain null marks on 'primary plan member' rows in the Insured table.

Both options include a lot of nulls and I've read that that's not a good sign.

The question

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?

Best Answer

My first thought off the top of my head is that you could put in the relationship_type attribute and have it be "self" rather than null for the primary. Another possibility is that you could make the "type" include "primary", "dependant spouse", or "dependant child", depending on how you need to access it.

I know that's not anything clever, design-wise. I'm interested to hear what others suggest, also.

Related Question