Single Large v/s Multiple Small MySQL tables for storing Options
- by Prasad
Hi there, I'm aware of several question on this forum relating to this. But I'm not talking about splitting tables for the same entity (like user for example)
Suppose I have a huge options table that stores list options like Gender, Marital Status, and many more domain specific groups with same structure. I plan to capture in a OPTIONS table.
Another simple option is to have the field set as ENUM, but there are disadvantages of that as well. http://www.brandonsavage.net/why-you-should-replace-enum-with-something-else/
OPTIONS Table:
option_id <will be referred instead of the name>
name
value
group
Query: select .. from options where group = '15'
- Since this table is expected to be multi-tenant, the no of rows could grow drastically.
- I believe splitting the tables instead of finding by the group would be easier to write & faster to execute.
- or perhaps partitioning by the group or tenant?
Pl suggest. Thanks
