1

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?)

vs

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:

  1. That could be an extremely large number of relationships for just one alert, depending on the size of my list of items.
  2. New items wouldn't be in the alert. It would only be items that existed at the time of the creation of the alert.
Isaac Fife
  • 113
  • 4
  • If this website will never have more than 50 items for sale, this might work. In most cases, though, it would make more sense for the user to hit the page for the item, and select that they want to track the price there. All items would be more like a regular "on sale" email to be sent out. Two different use cases, so two different mechanisms. And, probably, stored two different ways. Mentioned in case a different mechanism altogether might actually be the solution - never hurts to look at it from another angle. – RDFozz Aug 17 '18 at 23:10

2 Answers2

1

The "All options" should be part of the option list with an ID (-1 is OK to be recorded in the options master table). I wouldn't include a NULL value, because it is commonly used to note lack of data.

Your second option is totally worthless from my point of view: you can easily calculate the 2nd field using the data in the 3rd field.

AMG
  • 1,352
  • 6
  • 12
0

An option ID of -1 has the major disadvantage of rendering foreign key constraints impossible (unless you actually have an option with ID -1). It also complicates any queries retrieving if an user has chosen an option -- the special case has to be handled and must not be forgotten.

So, as the database is there for storing the data in a proper and save way and not to provide or reflect input aids for the end user -- that's what the application is for -- I'd have the application translate the "All" to really store them all, i.e. multiple lines in the target table.

This also had the advantage, that it paves the road to an extension of your application, that seems natural to me. If a user can chose one option and all, which are obviously many (unless there is only one, which would turn "All" a bit useless), why cannot they pick more than one but less then all? Sooner or later you might want to implement that.

sticky bit
  • 4,834
  • 2
  • 13
  • 19
  • The problem that I will have if I create the foreign key relationship with all options is that the options can change over time, some being removed, others being created. I'll give a more concrete example in the question so that you have more context. – Isaac Fife Aug 16 '18 at 19:45
  • @IsaacFife: Removing is no problem, if you use the ON DELETE CASCADE option in the FK constraints. The DBMS will remove them automatically for you. (And what are you doing now, if a user selected, say option 1 and option 1 is removed?) For the other direction, adding a new option, you can use an INSERT trigger on the options table, that also inserts into the user options table if necessary. – sticky bit Aug 16 '18 at 19:52
  • @IsaacFife: OK, I give you that it's hard to decide what is the situation if there is only one option available. Did a user select it as one option or via the all options? But to be honest I think the current input is inconsistent anyway. Why can a user only select only one or all options and not multiple but not all? I'd change the drop down to multiple check boxes and a "Select All" button, that just checks all of them. Then you can define whether a new option is automatically added to all users or not. It's no matter of "all" or "not all" any longer. – sticky bit Aug 16 '18 at 19:56