Relational db how to store complex logical relations? [duplicate]

Question

For example, we have a system that lets user claim X amount of gifts base on the events they joined.

event table:

| id | name    |
----------------
| A  | event A |
| B  | event B |

gift table:

| id | name | formula   |
--------------------------
| 1  | dog  | A*1 + B*2 |
| 2  | cat  | (A|B)*3   |

In the gift table we have some formulas:

• A*1 + B*2 - User joined event A can get 1 toy, if they joined event B, they can get another 2 toy.
• (A|B)*3 - User joined either event A or B can get 3 toys.

Formulas are evaluated in the backend, but I'm wondering whether we can represent the relation of event and gift in the database somehow? Specifically, we'd like to:

1. SQL SELECT event base on gift, and vice versa
2. keep track of number of times users claimed a gift base on each event.

Thank you so much in advance

Answer 1

What you need is a bridging table (associative entity) that logs which users have been to which events. This would look like the following:

User_Events:

User_ID | Event_ID
------------------
1       | 1
1       | 2
2       | 2

Note that each value represents a foreign key (id) in the relevant table.

From here, you can apply your logic based on 'events that the user has been to', by querying something like:

SELECT E.*
FROM Users U
JOIN Events E
  ON User_Events.User_ID = User_Events.Event_ID