Progettazione di database per la memorizzazione di gruppi di utenti in base a criteri di filtro

I filtri sono realmente predicati nell'istruzione SQL. (Clausola WHERE)

Un predicato è composto da:

Nome colonna, operatore e valore

Inoltre potrebbero esserci più predicati con le combinazioni AND / OR.

Quindi, inizia con una tabella di Nome, Operatore e Valore. Inoltre una tabella di come vengono combinati i predicati.

Quindi codifica per leggere le tabelle e crea la clausola WHERE in modo appropriato.

**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

Ecco un'idea:

Definisci ciascun componente di un filtro come un record in una tabella "campi filtro".

I campi filtro definiscono su quale campo viene filtrato e come.

Ad esempio, ecco come potrebbero apparire le strutture dati:

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.

E alcuni dati per riempirlo:

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

Quindi nell'esempio sopra ho definito un filtro per trovare clienti / utenti scaduti BurgerPlace: hanno speso più di $ 50, ma la loro data di acquisto più recente è più di 90 giorni fa.

Se vuoi divertirti, puoi anche definire i tipi per i campi, come ad esempio:

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

Potrebbe essere usato per impedire a una persona che crea filtri di utilizzare un operatore stringa su una data.

Trickier è se vuoi essere in grado di imporre vincoli di chiave esterna sui valori del filtro, ad esempio da merchant / vendor ID.

Inoltre, ho assunto che tu abbia già un modo per trovare valori sulle entità utente / cliente basate su dataField .

Congratulazioni, ora sei alla fine di ciò che è noto come "analisi della segmentazione del mercato". Se sei davvero fortunato, c'è un analista di marketing che si dedicherà a inventare indicatori demografici oscuri e imprevedibili, che dovrai implementare. Cheers!

Avendo già fatto ciò, ti consiglio di dimenticare un'elegante struttura di tabella dei filtri (non sarai mai in grado di anticipare il tipo di cose che il Marketing può generare) e invece di fare qualcosa che sia completamente flessibile.

Mi vengono in mente due opzioni:

1. Utilizza una singola UDF con valore di tabella per restituire le righe che soddisfano ciascun filtro. Quando il marketing si presenta con una nuova idea di filtro, basta scrivere una nuova UDF. Questo ti dà la massima flessibilità, a patto che ti servano solo i dati presenti nel tuo database. Quando hai bisogno di estrarre un elenco di lead, unisciti all'UDF o alle UDF secondo necessità.

2. Definire una tabella di join filtro-cliente (solo due colonne, FilterID e CustomerID ). Popolare la tabella di join con un servizio o un lavoro agente che viene eseguito una volta al giorno. Ciò ti offre la massima flessibilità per utilizzare i dati dal tuo database, altri database o anche servizi esterni (ad esempio API di geolocalizzazione IP). Quando devi estrarre un elenco di lead, aggiungi la tabella dei clienti alla tabella di join con l'ID del filtro corretto.

La "iniezione SQL" sarebbe più elegante? O è una vista vecchio stile?

id: primary_key
name: string
sql: string