4

We're designing a system to define user groups. The groups will be used to run queries, to target promotions at relevant users. For example, to send a "$5 Free if you come back this week" offer to users who haven't shopped at a particular merchant in a while.

A group is defined by a set of filters, of which there are many. For example:

  • Has made a purchase within the last 30 days
  • Has shopped at merchant X
  • Total spent greater than $50

We can expect new filters to be added regularly, as the needs of the marketing department change.

A user group has a name, and an associated set of filters. For example:

const lapsedMcDonaldsUsers = UserGroup({
  name: "Big McDonalds spenders who've lapsed",
  filters: [
    TotalSpent({greaterThan: 100.00, atMerchant: MCDONALDS_ID}),
    DaysSinceLastPurchase({greaterThan: 30, atMerchant: MCDONALDS_ID})
  ]
})

Someone from the marketing department will define the groups and their filters using a web interface. That definition will be stored in a database. They'll then be able to schedule emails to be sent to the user groups. At send time, a query based on the filters will run to generate the list of recipients.

The question is how best to define the database schema to store the user groups and filters. Here's my first attempt:

      UserGroups
      ----------
      id: primary_key
      name: string


      Filters
      -------
      id: primary_key


            TotalSpentFilters
            -----------------
            id: primary_key
            filter_id: foreign_key
            merchant_id: foreign_key
            total_spent: integer


            DaysSinceLastPurchaseFilters
            ----------------------------
            id: primary_key
            filter_id: foreign_key
            merchant_id: foreign_key
            days_since: integer

            .... (many more)


      UserGroupFiltersBindery
      -----------------------
      id: primary_key
      user_group_id: foreign_key
      filter_id: foreign_key

So I'm using a table inheritance pattern, where each individual filter is defined in its own table, which points to a record in the general Filters table. The UserGroupFiltersBindery is then responsible for linking together a UserGroup and its Filters.

Thoughts on this design? Ideas from improvements? Possible problems?

  • Will the filters always have that structure (shops at merchant X, spends $Y, last seen in Z days)? – FrustratedWithFormsDesigner Feb 7 '17 at 22:04
  • @FrustratedWithFormsDesigner No. You could have filters for range spent between X and Y, has claimed a deal between date D1 and D2, etc. In short, you can't assume anything about the possible structure of the filters. – Jonah Feb 7 '17 at 22:06
  • So the fields in the filters are very dymanic... – FrustratedWithFormsDesigner Feb 7 '17 at 22:07
  • @FrustratedWithFormsDesigner Correct. – Jonah Feb 7 '17 at 22:07
2

The filters are really predicates in your SQL statement. (WHERE clause)

A predicate consists of:

Column Name, Operator, and Value

Additionally there could be multiple predicates with AND/OR combinations.

So, start with a table of Name, Operator, and Value. Also a table of how predicates are combined.

Then code to read tables and build up the WHERE clause appropriately.

**Filter Table**
ID, Description
1, 10 Purchases with a total amount over 100 dollars

**Predicate Table**
ID, Filter ID, Name, Operator, Value
1,1,PurchaseCount,GreaterThanEqual,10
2,1,PurchaseAmount,GreaterThan,100

**PredicateCombination Table**
ID, Filter ID, LeftPredId, RightPredId, Condition
1, 1, 1, 2, AND
  • Jon, thanks. This is similar to @Frustrated's answer. It's definitely more flexible and better captures the structure of the problem. My big concern is that Value will hold values of type float, int, string, date, etc, all as plain strings. You could make it a foreign key into a values table with optons for all the different types... do you have any better solutions to this issue? – Jonah Feb 8 '17 at 22:26
  • Yes you correct on the Value column. The other option is to have a table of columns for those types of data and then a column that describes the value. IntValue, DecimalValue, etc. Then a column that says what type of data it is. This would lead to a table with a lot of NULL values however, but less type conversion. – Jon Raynor Feb 9 '17 at 16:31
  • right. which way would you lean personally? – Jonah Feb 9 '17 at 17:35
  • With the 1 value and then a column describing the type. That way no nulls in the database. Then do the conversion in code. – Jon Raynor Feb 9 '17 at 18:24
  • Probably best overall tradeoff, though nulls are fairly low on my hate list. ofc, you could combine your design, correct types, and normal form by using the table inheritance pattern on your "value" table, with subtables for each of the db datatypes you needed. that's probably the most by the book "correct" design, but the extra complexity is probably not worth it. – Jonah Feb 9 '17 at 18:33
1

Here's an idea:

You define each component of a filter as a record in a "filter fields" table.

The filter fields define which field is being filtered on, and how.

For example, here's what the data structures could look like:

filter
------
id - PK
name - Logical name such as "Spent >= $20 at ThatBurgerPlace"

dataField
---------
id - PK
name - such as "date last seen", or "total amount spent"

filterField
-----------
id - PK
filter_id - FK to filter.id
data_field_id - FK to dataField.id
operator - should be a from a defined list of comparison operators such as: >, <, =, !=, etc...
filter_value - The value to filter against.

And some data to fill it in:

filter
------
ID   |  name
-----+------
1    |  Users who spent more than $20 at BurgerPlace
2    |  Users who made a purchase within last 30 days
3    |  Lapsed users who spent more than $50 at BurgerPlace

dataField
---------
ID   |  name
-----+------------
1    | amount spent
2    | vendor visited
3    | most recent purchase date

filterFields
------------
ID   | filter_id  | data_field_id  | operator | filter_value
-----+------------+----------------+----------+-------------
1    | 1          | 1              | >=       | 20 dollars
2    | 1          | 2              |     =    | BurgerPlace
3    | 2          | 3              | <=       | 30 days
4    | 3          | 1              | >=       | 50 dollars
5    | 3          | 2              |     =    | BurgerPlace
6    | 3          | 3              | >=       | 90 days

So in the example above, I've defined a filter to find lapsed BurgerPlace customers/users: they have spent more than $50, but their most recent purchase date is more than 90 days ago.

If you want to get fancy you can also define types for fields, such as:

dataField
---------
id - PK
name - such as "date last seen", or "total amount spent"
data_type - such as int, string, date

That could be used to prevent a person who creates filters from using a string operator on a date.

Trickier is if you want to be able to enforce foreign key constraints on the filter values, such as by merchant/vendor ID.

Also, I've assumed that you already have some way of finding values on user/customer entities based on dataField.

  • Interesting.... upvoted. Pros: more flexible and far fewer tables. Cons: At the expense of data type enforcement, and having the business concepts less visible in the structure. I'm not sure where I come out, but I'm definitely going to consider this. – Jonah Feb 7 '17 at 22:56
0

Congratulations, you are now on the receiving end of what is known as "market segmentation analysis." If you're really lucky, there is a marketing analyst who will be dedicated to coming up with obscure and unpredictable demographic indicators, which you will have to implement. Cheers!

Having done this before, I would recommend that you forget about an elegant filtering table structure (you will never be able to anticipate the sort of things Marketing can come up with) and instead do something that is completely flexible.

Two options come to mind:

  1. Use an individual table-valued UDF to return rows that meet each filter. When marketing comes up with a new filter idea, you just write a new UDF. This gives you complete flexibility, as long as you only need data that are in your database. When you need to pull a list of leads, join to the UDF or UDFs as needed.

  2. Define a filter-customer join table (only two columns, FilterID and CustomerID). Populate the join table with a service or agent job that runs once a day. This gives you complete flexibility to use data from your database, other databases, or even external services (e.g. IP geolocation APIs). When you need to pull a list of leads, just join the customer table to the join table with the right filter ID.

0

Would "SQL injection" be more elegant? Or is it an oldfashioned view?

id: primary_key
name: string
sql: string
  • 1
    SQL injection answers will most likely get you down votes, but yes storing the SQL as a raw string is a possibility. But I think you want to abstract that away somewhat to avoid users typing in SQL directly. – Jon Raynor Feb 8 '17 at 17:18
  • 1
    Ironic quotes intended. Going down, going down. Just saying you can store SQL in the database, you can generate SQL, and have higher order SQL. – bbaassssiiee Feb 8 '17 at 18:14

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.