MySQL schema advice for an interactive table with filters?

117 Views Asked by At

I'm trying to build an interactive table that shows data about people's shopping habits, with filters on the LHS that dynamically change the shown data. When no filters are checked, all data is shown. Thus in a default state, the table might look like:

State                  State | Person  | Spend / Mo | Gender |
  [ ] CA               ---------------------------------------
  [ ] NY               CA    | Kathy   | $500       | Female |
  [ ] AZ               NY    | Dianne  | $600       | Female |
                       CA    | Jeff    | $1000      | Male   |
Gender                 CA    | Chris   | $800       | Male   |
  [ ] Male             AZ    | Kristen | $750       | Female |
  [ ] Female           NY    | Zachary | $2500      | Male   |

Monthly Spend
  [ ] <$500
  [ ] $500-$1000
  [ ] $1000-$5000

Another configuration (with filters checked) might look like:

State                  State | Person  | Spend / Mo | Gender |
  [ ] CA               ---------------------------------------
  [X] NY               NY    | Dianne  | $600       | Female |
  [X] AZ               AZ    | Kristen | $750       | Female |

Gender                
  [ ] Male             
  [X] Female          

Monthly Spend
  [X] <$500
  [X] $500-$1000
  [ ] $1000-$5000

I would want to be able to save this filter configuration for the user in a MySQL database, but I'm having trouble with how the schema would be designed. So far I have two possibilities. The first is:

table MyTable
   id        int NOT NULL AUTO_INCREMENT,
   name      varchar(255) NOT NULL,

   // a serialized list of all filters for the table, e.g.,
   // "{State:CA,AZ}, {Gender:Female}, {Spend:,<500,500-1000}"
   filters   TEXT,  
   PRIMARY KEY (id)

I know that serializing a list as text is typically frowned upon, so the the second possibility is:

table MyTable
   id       int NOT NULL AUTO_INCREMENT,
   name     varchar(255) NOT NULL,
   PRIMARY KEY (id)

// one row would correspond to one checked filter for a table.
table Filters
   filter_type    varchar(255) NOT NULL,
   filter_name    varchar(255) NOT NULL,
   table_id       int,
   PRIMARY KEY (table_id, filter_type, filter_name),
   INDEX (table_id),
   FOREIGN KEY (table_id) REFERENCES MyTable(id)

To me, the downside of the second method is that I would have to make an additional query to Filters every time I want to load a user's saved table.

Is one of these options preferred, or is there a third method that is better for representing this kind of pattern? As a note, I am locked into using a relational database for this app (the rest of the environment uses MySQL). Thanks in advance for your help!

0

There are 0 best solutions below