0

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!

Bob Risky
  • 805
  • 1
  • 9
  • 22
  • 2
    You have to **analyze** the saved filter no matter what technique. Serialization is just a poor man's database table. Don't worry about the time it takes, worry about this approach being flexible if new filter become necessary. Which one can adapt and which one will require hard coding. – Jacques Amar Sep 27 '17 at 01:05
  • I think for reading all the filters for a table, #2 is an okay schema. I was a bit confused on how to perform batch updates. For example, suppose the user un-checks a few filters and checks a few more filters, and then hits "Save." What would the query look like to now update the MySQL table? At the very minimum, I would have to figure out the set difference between the current state and the stored state, then perform 1 batch delete query and 1 batch insert query in the Filters table. Whereas in schema #1, I could just do a single row-update after serializing the current state. – Bob Risky Sep 27 '17 at 18:07
  • 1
    ORRR, delete old saved data and INSERT new ones. Same differnce.I swear :) - **personally** I don't like serialized data, as it's hard to do searches if you ever do searches. This usage might not be relevant. Here is the wisdom under which I operate: *"We will encourage you to develop the three great virtues of a programmer: laziness, impatience, and hubris."* -- **Larry Wall** – Jacques Amar Sep 27 '17 at 20:22
  • There are many questions re 'SQL store filters'. How are they inadequate? Also, you have to put into your question (not comments) whatever criteria are important for your question. Also: As wiith all "best" questions, the answer depends on demonstrated implementation- and usage- dependent details, so best is an initial straightforward design. Eg what is the mapping from a straightforward representation of the GUI user notion of filters to corresponding SQL? Eg why are you not just re-querying? You need to estimate/measure to compare design options under particular implemeantion/usage. – philipxy Oct 11 '17 at 21:03

0 Answers0