-1

I am designing a database and have run into 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 come up with the following tables:

Scenario (Scenario_ID, Scenario_Name)
Toggles (Scenario_ID, Toggle_1, Toggle_2, ..., Toggle_64)

This leaves me with a 65-column Toggles table. 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.

What is a better database design?

philipxy
  • 14,867
  • 6
  • 39
  • 83
  • Does this answer your question? [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – philipxy Nov 08 '22 at 10:17
  • [Strategy for “Which is better” questions](https://meta.stackexchange.com/q/204461) – philipxy Nov 08 '22 at 10:18

5 Answers5

6

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.

S.Skov
  • 707
  • 4
  • 8
  • A valid option, but be aware that you'll get ton's of ugly joins and complex queries, if you are using the toggles for predicates in the where clause. – Jens Schauder Jul 25 '09 at 22:35
1

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.

maciejkow
  • 6,403
  • 1
  • 27
  • 26
1

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

John Parker
  • 54,048
  • 11
  • 129
  • 129
1

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.

Josh Davis
  • 28,400
  • 5
  • 52
  • 67
1

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.

Matt Howells
  • 40,310
  • 20
  • 83
  • 102