Effectively implementing associated values in a MySQL database

Question

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?

Answer 1

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.