A better database design?

Question

I am designing a database and have run into a snag. Ok, not so much of a snag as just something that I do not like. I will have up to 1000 different scenarios, and in each scenario I will need to store the state of each one of 64 different toggles. I've worked this around as best I can and have come up with the following:

Table Scenario

Scenario_ID
Scenario_Name


Table Toggles

Scenario_ID
Toggle_1
Toggle_2
...
...
Toggle_64

This leaves me that with a 65 row Toggles table. I keep thinking that there has to be a better way than having 64 columns of "on" or "off", but I am at a loss as to what it might be. I don't want to store the toggle state in a CSV in one column because it will be constantly changing, and needs to be parsed quite often. It would be much easier for me to update the table by simply updating Toggle_14 to "off" rather than parsing a CSV, changing it, and reloading it. Any thoughts?

Answer 1

Use a Many-to-Many relation:

Table Scenario (Id, Name)


Table ScenarioToggles (ScenarioId, TogglesId, ToggleState)


Table Toggles (Id, ToggleName)

The field, ToggleState in the ScenarioToggles table will hold the On/Off value.

It also yields some flexibility if you ever need more than 64 toggles.

Answer 2

You can design Toogles Table it like this:

Scenario_ID
Toogle_nr
Toogle_state

and get 64 entries for each scenario.

However, if toogles count is not going to change often and in every scenario you'll need all the toogles, your solution seems to be the best and the simplest.

Answer 3

It's a hack (sort of, ish, kinda), but depending on your lookup requirements, you could just store a single 64 character string, which you could access in your programming language of choice as an array.

However, the many, many, downsides of doing this more than likely outweigh the benefits. (Then again, storing 64 bits of flippable state information in a database is a pretty odd requirement in the first place.)

Answer 4

MySQL's BIGINT type happens to be 64 bits, so you could use it for storing up to 64 flags in a bit field. Technically this breaks first normal form (only one value per column), but that is fine if 1) you'll never have more than 64 flags, and 2) you're always retrieving the flags as a group anyway.

Alternatively, you could store each flag in a separate row, such as:

        composite key
    =====================
    Scenario_ID Toggle_ID Value
         1          0       0
         1          1       1
         1          2       1
       (...)
         1         63       0
         2          0       1
       (...)

This way is more extensible and makes it easier to deal with single flags in queries, but you'll be retrieving 64 rows at a time if dealing with them all as a group in the application.

Answer 5

Since your question is tagged "mysql" I'd recommend using the SET type. Updating records to toggle values can be a little tricky though, as MySQL doesn't natively offer functions to that purpose (search for REMOVE_FROM_SET at MySQL's bug tracker for a workaround.) On the other hand, it's very compact and it won't require you to join 64 tables or use aggregate functions for every SELECT query.

Answer 6

Some people are just trying to be way too clever. Use one table, with scenario Id, name, and 64 toggle columns. This is well normalised, fast, space efficient, and extensible.