1

I'm a Swift iOS developer, and whenever I come across a data modeling problem where there is a property of an object that can have one of two different values, its type is usually going to be an enumeration. Now, if there is an additional value associated with one of the two values, for example if the enumeration represents a frequency (either .daily or .weekly) and a .weekly instance requires the day of the week. Then I would define something like so:

enum Frequency {
    case daily, weekly(weekday: Weekday)
}

Then I could just use a single property to hold the frequency value, and if the frequency happens to be .weekly it will also contain the corresponding weekday; without having to define a separate property to hold the Weekday that is always null if the frequency happens to be .daily.

I'm wondering if this behavior can be effected in MySQL without having to define columns whose values are always null if the value of the enumeration happens to be one without an associated value. Also, the column holding the value of the enumeration — not the associated value — should be indexable, so it'd be possible to efficiently query for all .daily or .weekly records for example.

A couple of solutions I've come up with that leave a little to be desired:

  1. Define a JSON column that holds the enumeration. Then have a generated column whose value is the ID of the enumeration. This way the enumeration can be as complicated as desired (as many associated values as desired), and there won't be any optional columns. One inconsistency with this approach is when the enumeration doesn't have an associated value. Then the JSON column value is just a duplicate of the enumeration ID column value.
  2. Have a separate table for this column value and reference it in the parent table. Then use nullable columns and check constraints to ensure that associated value columns aren't null when the enumeration value is their corresponding case, and are null when it is not their corresponding case.

Are there any approaches that are more suitable than either of the above?

7
  • For the given example, I'd try to generalize. You could model what you want in a different, more general and more precise way. Have a point in time that is the start occurrence. Then have a time interval till the next occurrence. And maybe a maximum count, unless it should reoccur until infinity.
    – sticky bit
    Jan 15, 2022 at 5:57
  • @stickybit And what happens when the interval is variable? Something that occurs monthly may occur with a different number of days between difference occurrences. And what if some frequencies are only temporary in duration? As more frequencies got added, the number of columns within the table just attempting to address the frequency component of the record could become a hassle.
    – shoe
    Jan 15, 2022 at 7:05
  • @stickybit What would be easier is a way to deal with frequencies in a non-ambiguous, type safe way despite the possibility that new ones may be added in the future that require different pieces of data being associated with it — such as a day of the month for a monthly frequency or both a month of the year and a day of that month for a yearly frequency. What's above is just an example of a situation where associated values could come in handy.
    – shoe
    Jan 15, 2022 at 7:07
  • @stickybit What'd I'm wondering is if there is a way to effect it that is more suitable than those that I've provided above as it is a solution to a modeling problem that often comes up, and it's not practical to define a subtable for each case of an enumeration that either has associated values or associated values different from other enumeration cases.
    – shoe
    Jan 15, 2022 at 7:08
  • "Something that occurs monthly may occur with a different number of days between difference occurrences." -- Use an interval that not only allows a number but also a unit. "And what if some frequencies are only temporary in duration?" -- Addressed by the maximum count. And if necessary multiple records can be assigned to a "master record" to allow for gaps. "such as a day of the month for a monthly frequency or both a month of the year and a day of that month for a yearly frequency." -- The starting point in time can be at everyday in a month in any year. All covered by my model. ...
    – sticky bit
    Jan 15, 2022 at 7:21

1 Answer 1

0

How about

ENUM('daily', 'Mondays', 'Tuesdays', ... 'Sundays')

It requires a bit more coding, but things like these still work:

WHERE freq = 'daily'

WHERE freq != 'daily'

Or...

SET('Mondays', 'Tuesdays', ... 'Sundays')

where "daily" is encoded as having all 7 set.

Either approach occupies a single byte.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.