portfolio_assets_currency_converter

assets_converter.md

Tài liệu chi tiết thiết kế CSDL quản lý tài sản đa tiền tệ

1. Giới thiệu tổng quan

Hệ thống được thiết kế để quản lý tài sản với khả năng xử lý nhiều loại tiền tệ khác nhau. Cho phép người dùng theo dõi, quản lý các giao dịch tài sản và xem giá trị quy đổi giữa các đồng tiền.

2. Chi tiết từng bảng và mối quan hệ

2.1. Bảng Users (Người dùng)

Mục đích

• Lưu trữ thông tin người dùng trong hệ thống
• Quản lý quyền truy cập và xác thực

Chi tiết các trường

CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Cách hoạt động

• Email được sử dụng làm định danh duy nhất cho đăng nhập
• password_hash lưu mật khẩu đã được mã hóa
• is_active dùng để vô hiệu hóa tài khoản thay vì xóa

2.2. Bảng Currencies (Tiền tệ)

Mục đích

• Quản lý danh sách các loại tiền tệ trong hệ thống
• Cung cấp thông tin cơ bản về mỗi đồng tiền

Chi tiết các trường

CREATE TABLE currencies (
    id VARCHAR(10) PRIMARY KEY,  -- VND, USD, EUR
    name VARCHAR(50) NOT NULL,   -- Vietnamese Dong, US Dollar
    symbol VARCHAR(10),          -- ₫, $, €
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

Cách hoạt động

• Được tham chiếu bởi nhiều bảng khác để xác định đơn vị tiền tệ
• Một currency có thể được sử dụng trong:
  • Giao dịch (asset_transactions)
  • Số dư (asset_balances)
  • Tỷ giá (exchange_rates)

2.3. Bảng Exchange_rates (Tỷ giá)

Mục đích

• Lưu trữ tỷ giá quy đổi giữa các cặp tiền tệ
• Cho phép tính toán giá trị quy đổi giữa các đồng tiền

Chi tiết các trường và quan hệ

CREATE TABLE exchange_rates (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    from_currency_id VARCHAR(10),
    to_currency_id VARCHAR(10),
    rate DECIMAL(24,8) NOT NULL,
    effective_date TIMESTAMP,
    created_at TIMESTAMP,
    FOREIGN KEY (from_currency_id) REFERENCES currencies(id),
    FOREIGN KEY (to_currency_id) REFERENCES currencies(id)
);

Cách hoạt động

• Lưu tỷ giá từ một đồng tiền sang đồng tiền khác
• Ví dụ: 1 USD = 23,500 VND

  INSERT INTO exchange_rates 
  (from_currency_id, to_currency_id, rate, effective_date)
  VALUES ('USD', 'VND', 23500, '2024-01-20');

• Sử dụng trong quy đổi giá trị:

  -- Quy đổi giá trị từ VND sang USD
  SELECT amount_vnd / rate AS amount_usd
  FROM exchange_rates
  WHERE from_currency_id = 'VND' 
  AND to_currency_id = 'USD';

2.4. Bảng Assets (Tài sản)

Mục đích

• Định nghĩa các loại tài sản trong hệ thống
• Quản lý thông tin cơ bản về tài sản

Chi tiết các trường và quan hệ

CREATE TABLE assets (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    asset_type VARCHAR(50),      -- crypto, stock, real_estate
    name VARCHAR(100),           -- Bitcoin, Apple Inc.
    symbol VARCHAR(20),          -- BTC, AAPL
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

Cách hoạt động

• Là bảng trung tâm liên kết với:
  • asset_transactions: ghi nhận giao dịch
  • asset_balances: theo dõi số dư
  • portfolio_assets: quản lý trong danh mục
• Ví dụ về luồng hoạt động:
  1. Tạo tài sản mới (Bitcoin)
  2. Ghi nhận giao dịch mua Bitcoin
  3. Cập nhật số dư Bitcoin
  4. Thêm vào danh mục đầu tư

2.5. Bảng Asset_transactions (Giao dịch)

Mục đích

• Ghi nhận tất cả các giao dịch mua/bán tài sản
• Theo dõi lịch sử giá mua/bán

Chi tiết và cách hoạt động

CREATE TABLE asset_transactions (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    asset_id BIGINT,
    transaction_type VARCHAR(20),    -- buy, sell
    quantity DECIMAL(24,8),
    price_per_unit DECIMAL(24,8),
    currency_id VARCHAR(10),
    transaction_date TIMESTAMP,
    created_at TIMESTAMP,
    FOREIGN KEY (asset_id) REFERENCES assets(id),
    FOREIGN KEY (currency_id) REFERENCES currencies(id)
);

Ví dụ luồng giao dịch

1. Mua Bitcoin:

INSERT INTO asset_transactions 
(asset_id, transaction_type, quantity, price_per_unit, currency_id)
VALUES (1, 'buy', 0.5, 23500, 'USD');

2. Trigger sau giao dịch:

CREATE TRIGGER after_transaction
AFTER INSERT ON asset_transactions
FOR EACH ROW
BEGIN
    UPDATE asset_balances
    SET quantity = quantity + NEW.quantity
    WHERE asset_id = NEW.asset_id;
END;

2.6. Bảng Asset_balances (Số dư)

Mục đích

• Theo dõi số dư hiện tại của từng tài sản
• Tính toán giá mua trung bình

Cách hoạt động

-- Tính số dư và giá trung bình
SELECT 
    a.name,
    ab.quantity,
    ab.average_purchase_price,
    (ab.quantity * ab.average_purchase_price) as total_value
FROM asset_balances ab
JOIN assets a ON ab.asset_id = a.id;

2.7. Portfolios và Portfolio_assets

Mục đích và quan hệ

• Tổ chức tài sản thành các danh mục
• Theo dõi phân bổ tài sản

Ví dụ sử dụng

-- Tạo danh mục mới
INSERT INTO portfolios (user_id, name) 
VALUES (1, 'Crypto Portfolio');

-- Thêm tài sản vào danh mục
INSERT INTO portfolio_assets 
(portfolio_id, asset_id, target_percentage)
VALUES (1, 1, 50.0);  -- 50% Bitcoin

3. Các tình huống sử dụng phổ biến

3.1. Quy đổi giá trị danh mục sang USD

SELECT 
    p.name as portfolio_name,
    SUM(ab.quantity * ab.average_purchase_price * er.rate) as usd_value
FROM portfolios p
JOIN portfolio_assets pa ON p.id = pa.portfolio_id
JOIN asset_balances ab ON pa.asset_id = ab.asset_id
JOIN exchange_rates er ON ab.currency_id = er.from_currency_id
WHERE er.to_currency_id = 'USD'
GROUP BY p.id;

3.2. Theo dõi lợi nhuận/thua lỗ

SELECT 
    a.name,
    ab.quantity,
    ab.average_purchase_price as buy_price,
    aph.price as current_price,
    (aph.price - ab.average_purchase_price) * ab.quantity as profit_loss
FROM asset_balances ab
JOIN assets a ON ab.asset_id = a.id
JOIN asset_price_history aph ON a.id = aph.asset_id;

4. Bảo trì và tối ưu hóa

• Tạo indices cho các trường thường query
• Định kỳ cập nhật tỷ giá
• Lưu trữ lịch sử giá
• Backup dữ liệu định kỳ

5. Thiết kế tổng thể

erDiagram
    currencies ||--o{ exchange_rates : "has"
    currencies ||--o{ asset_transactions : "uses"
    currencies ||--o{ asset_balances : "uses"
    assets ||--o{ asset_transactions : "has"
    assets ||--o{ asset_balances : "has"
    assets ||--o{ asset_price_history : "has"
    
    currencies ||--o{ asset_price_history : "quoted_in"

    users ||--o{ portfolios : "owns"
    portfolios ||--o{ portfolio_assets : "contains"
    assets ||--o{ portfolio_assets : "belongs_to"
    
    users {
        bigint id PK
        varchar email
        varchar name
        varchar password_hash
        boolean is_active
        timestamp created_at
        timestamp updated_at
    }

    portfolios {
        bigint id PK
        bigint user_id FK
        varchar name
        varchar description
        timestamp created_at
        timestamp updated_at
    }

    portfolio_assets {
        bigint id PK
        bigint portfolio_id FK
        bigint asset_id FK
        decimal target_percentage
        timestamp created_at
        timestamp updated_at
    }

    asset_price_history {
        bigint id PK
        bigint asset_id FK
        varchar currency_id FK
        decimal price
        timestamp price_date
        varchar price_source
        timestamp created_at
    }

    currencies {
        varchar id PK
        varchar name
        varchar symbol
        boolean is_active
        timestamp created_at
        timestamp updated_at
    }

    exchange_rates {
        bigint id PK
        varchar from_currency_id FK
        varchar to_currency_id FK
        decimal rate
        timestamp effective_date
        timestamp created_at
    }

    assets {
        bigint id PK
        varchar asset_type
        varchar name
        varchar symbol
        boolean is_active
        timestamp created_at
        timestamp updated_at
    }

    asset_transactions {
        bigint id PK
        bigint asset_id FK
        varchar transaction_type
        decimal quantity
        decimal price_per_unit
        varchar currency_id FK
        timestamp transaction_date
        timestamp created_at
    }

    asset_balances {
        bigint id PK
        bigint asset_id FK
        decimal quantity
        decimal average_purchase_price
        varchar currency_id FK
        timestamp last_updated
    }

Loading