Database design problem

Question

I have a problem creating a database schema for the following scenario:

(I’m not creating a dating site but just using this as an example)

A user logs on to a dating site and is given a multiple selection for the hair colour they’d like their date to have:

This is easy enough to model with the three tables below:

Tables:

User {key}

HairColour {key}

UserHairColour {UserKey} {HairColourKey}

However, the user also has the option to select ‘any’ which means they don’t care about hair colour and all hair colour should be included in the selection.

How do I give the user the ‘any’ option?

I could obviously select all hair colours and shove them into ’UserHairColour’ but what if I need to add a new hair colour in the future?

Answer 1

Absence of any records for this particular user in the UserHairColour table will indicate they do not care about the hair colour.

Absence of a decision indicates they have no preference. Obviously, it cannot mean they want their date to have no hair color at all.

I do not see here a need for a separate value or any extra table design. What you have allows you to achieve your goal in a simple way.

---

EDIT: As reaction to a proposed solution with ANY extra value.

The idea of "ANY" will conceptually interfere with the other selections. We are talking about presenting the user with a multitude of choices, ANY being one of them, and allowing them to select many. So the user can technically select ANY along with the other options, making it unclear what takes precedence - ANY or specific options. I believe the approach with simply no records as an indicator of ANY is clearer - it can only be interpreted one way. No records - no preferred values. You obviously cannot interpret it in the other way - no preferred value - user does not want this value to be present - this will make for transparent hair color which makes no sense. You can say it can mean no hairs at all, but I would suggest to have a separate option or a separate question for that already.