Database design: table with NULL keys

Question

I'm designing a table that will hold numeric values for 2-3 situations of data:

Situation 1: has Age and Sex, along with the numeric value

Situation 2: has only Age, along with the numeric value

Situation 3: has only Sex, along with the numeric value

I don't want to create 3 different tables. Instead, only one table, with the following columns:

AgeID (references a table that contains information about the Age)

SexID (references a table that contains information about the Age)

Value (the numeric value itself)

AgeID and SexID as Foreign Keys and linked to the appropriate tables.

The problem is: my query is always doing a INNER JOIN with Age and Sex tables. For Situation 1 it works well because values are present. For Situations 2 and 3 I don't get any data, because either AgeID or SexID is null.

What solution is the correct one?

• Change something in the table design?

• Use Entity-Attribute-Value table to be more generic?

• Use LEFT JOIN instead of INNER JOIN for all queries involving the nullable columns??

• Any other idea?

Could someone clarify?

Thanks!

Answer 1

Yes an outer Join, Left or right, the Inner join is meant to filter out everything that doesn't have a match in both tables.