Database design for storing user groups based on filter criteria

databasedatabase-designdesignrelational-database

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?

Best Answer

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
Related Topic