Table design for saving data based on user choices...

Question

Suppose that we have a table for saving human-being data (Human Table) The human table has a column named "relatives" in order to save what type of relatives the human is supporting. The relatives column can have 2 values: 1: means that "close relative" 2: means that "far relative"

Now, imagine that user can select one choice between close and far. When chooses "close" he/she can select one or more between "Sister", "Brother", "Father", "Mother" and save them. But when he/she chooses "far", then it's possible to select one or more items between "uncle", "aunt", "nephew" ,...

Now my question is: how the relations and design of tables could be? I use SQL Server 2008.

Thank you

Answer 1

One approach is to have a separate table, maybe call it RelativeTypes. It has two columns: Distance (for close/far) and Description for (mother/uncle/etc.). The primary key on this table would be a composite key consisting of both columns. Then to build the list of choices, you can select from the RelativeTypes table where Distance matches the user's distance column.

This isn't very flexible, so a different approach will be needed if you ever want the user to support more than one relative.

Answer 2

No, I would not have a column with those two values to select.

I'd have a RELATIVE table with a primary key, a string NAME (e.g., 'SISTER', 'BROTHER', 'FATHER', etc.) and a boolean IS_NEAR.

The table that pointed to a relative would have a foreign key that referenced the RELATIVE primary key.