Middle+

How to implement cashback storage in a database with two tables?

sobes.tech AI

Answer from AI

A common schema for storing cashback in a database with two tables is as follows:

Users Table — contains information about clients, such as user_id, name, contact details.
Cashback Table — stores records of cashback accruals and deductions, such as cashback_id, user_id (foreign key to Users), cashback amount, operation date, operation type (credit/debit).

This approach allows storing the history of cashback transactions and easily calculating the current balance for a user by summing the values in the cashback table.

Example table structures:

CREATE TABLE Users (
  user_id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

CREATE TABLE Cashback (
  cashback_id INT PRIMARY KEY,
  user_id INT,
  amount DECIMAL(10,2), -- positive for credit, negative for debit
  operation_date DATETIME,
  description VARCHAR(255),
  FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

To get the current cashback balance of a user:

SELECT SUM(amount) AS cashback_balance
FROM Cashback
WHERE user_id = ?;

Previous Next