Database design for storing user groups based on filter criteria

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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

id: primary_key
name: string
sql: string