Middle+

// [DATABASE] Design a database for users and their electronic wallets. // A user can own multiple electronic wallets in different currencies, but cannot own two wallets in the same currency. // Possible currencies: USD/EUR/RUB. The wallet should contain the current balance; // Currencies should have an additional name in Russian.

sobes.tech AI

Answer from AI

For designing a user database and their electronic wallets considering the requirements, the following structure can be used:

Tables

users — stores information about users

id (PK)
name
email
...

currencies — currency reference

code (PK) — for example, 'USD', 'EUR', 'RUB'
name_ru — currency name in Russian (e.g., 'Доллар США')

wallets — electronic wallets

id (PK)
user_id (FK -> users.id)
currency_code (FK -> currencies.code)
balance (DECIMAL)

Constraints

The wallets table must ensure the uniqueness of the pair (user_id, currency_code) so that a user cannot have two wallets in the same currency.

Example SQL schema (PostgreSQL):

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE currencies (
    code CHAR(3) PRIMARY KEY,
    name_ru VARCHAR(255) NOT NULL
);

CREATE TABLE wallets (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id),
    currency_code CHAR(3) NOT NULL REFERENCES currencies(code),
    balance NUMERIC(19,4) NOT NULL DEFAULT 0,
    CONSTRAINT unique_user_currency UNIQUE (user_id, currency_code)
);

-- Example of inserting currencies
INSERT INTO currencies (code, name_ru) VALUES
('USD', 'Доллар США'),
('EUR', 'Евро'),
('RUB', 'Российский рубль');

This design allows:

Storing users and their wallets in different currencies
Ensuring that a user does not have two wallets with the same currency
Easily expanding the list of currencies and storing their Russian names

When working with wallets, it is important to ensure correct balance updates considering transactions and locks to prevent race conditions.

Previous Next