I am conscious of several question about this forum relevant for this. But I am not speaking about splitting tables for the similar entity (like user for instance)

Suppose I've got a huge options table that stores list options like Gender, Marital Status, and much more domain specific groups with same structure. I intend to capture inside a OPTIONS table. Another simple choice is to achieve the area set as ENUM, but you will find disadvantages of that a lot. 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 <more like a description, and not a name/value pair>
group

Query: select .. from options where group = '15'

Usage: Gender &lifier Marital_Status come in the individuals tables nevertheless the value saved can come from Options

    eg. 
    Person 
    ..
    id=34 name=Prasad gender=31 marital_status=41
    .. 

    Options
    .. 
    31 gender male male
    32 gender female female
    ...
    41 marital_status single single 
    42 marital_status married married
    ..
  • Because this table is anticipated to become multi-tenant, no of rows could grow drastically.
  • In my opinion splitting the tables rather than finding through the group could be simpler to create &lifier faster to complete.
  • or possibly partitioning through the group or tenant?

Pl suggest. Thanks

This really is basically an EAV model, with all the pros and cons therein.

An EAV model can be used in conditions where the amount of characteristics (qualities, parameters) you can use to explain a factor (an "entity" or "object") is potentially huge, however the number which will really affect confirmed entity is comparatively modest. It's also called a "sparse matrix."

Among a suitable use to have an EAV table is signs and symptoms inside a medical database. Although you will find potentially 1000's of possible signs and symptoms, an average joe visiting the physician is only going to present having a much more compact quantity of signs and symptoms.

The Wikipedia article about EAV should let you know whether this model is suitable for the particular application, and suggest some guidelines for the reason that regard.

Observe that in case your example posts are Gender and Marital Status, and you've got an individuals table, individuals posts more properly belong within the Persons table, no EAV table.