I have a form where a user is able to select a single value from a dropdown. The first option in the dropdown is "All", followed by all of the individual options that they can pick. So, they can either pick an individual thing, or they can pick "All" which will effectively mean that they have picked everything in the list.
All Options Option 1 Option 2 Option 3
The database is storing this currently with an ID for the selection, or when they pick all, we store a value of -1. Is this anywhere close to best practice, or should we have something like a boolean column "all_options", and another column "option" which would be forced to be null if "all_options" is true?
user option 1 1 2 3 3 -1 (or null?)
user all_options option 1 false 1 2 false 3 3 true null
I can't find anything about this, and I think it has to do what not knowing what to search for.
A more concrete example
The options are say, items for sale on a website. The user is setting up alerts on price drops. One use case is that the user wants to know when a particular item is 50% off or more. They select the individual item from the dropdown of say 50 items. Another use case is, the user wants to know anything that is on sale in general. They pick the "All Items" option. The list of items changes, so if I create the relationship between this alert and every single item then:
- That could be an extremely large number of relationships for just one alert, depending on the size of my list of items.
- New items wouldn't be in the alert. It would only be items that existed at the time of the creation of the alert.