Storing conditional logic for compliance in a database

Question

I'm creating advertise system, which should be shown on ATM. For now, I have to make a database structure where advertise, ATM and customer compliance will be stored.

Advertise system must choose an advertise depending on ATM and customer.

The point is that customer is not strictly defined. It can be filtered with any its property (card number, account, client ID, client age, ...). For example:

1. On ATM=1 ADV=2 should be shown when customer is card like '1111%' On
2. All ATM except ATM=1 ADV=3 should be shown when ClientID in (1,2)

How Can I store these data in relational database?

I have one solution, but I don't like it, I will make a table were SQL where causes will be saved. For example:

advertise table
Adv_ID
Terminal_ID
Status (values can be: Allow/deny)
rule (here will be written SQL filter, for example: "card like '1111%' and client_id !=10230")
screen_id (on which page this adv must be shown) order (for adv priority)

Do you have any other suggestions?

Answer 1

You could try to design your configuration like this:

• columns
  • column_name
  • data_type (NUMBER, VARCHAR)
• advertisements
  • adv
  • seq (to assign priority)
• conditions
  • adv (FK)
  • column_name (FK)
  • modifier: NOT (check-constraint)
  • operator: LIKE, IN (would cover = and != but you can also add them explicitely)
  • values (child-table)

This would allow to generate all conditions while avoiding typos and SQL injection. I added data_type to check when only numbers are allowed and when quotes need to be added/escaped.

adv column_name  modifier  operator  values (shown denormalized)
2   atm                    IN        1
2   customer               LIKE      1111%
3   atm          NOT       IN        1
3   clientID               IN        1,2