🎄 科学と神々株式会社アドベントカレンダー 2025
License System Day 12: データベーススキーマ設計
📖 今日のテーマ
今日は、ライセンスシステムの心臓部であるデータベース設計を学びます。
適切なスキーマ設計は、システムのパフォーマンスと拡張性を左右します。
🎯 データベース選択: SQLite vs PostgreSQL
SQLite（開発・中小規模）
利点:
✅ 設定不要（ファイルベース）
✅ 軽量（数百KB）
✅ 高速（ローカル）
✅ トランザクション対応
✅ クロスプラットフォーム
制限:
❌ 同時書き込みが弱い
❌ レプリケーション機能なし
❌ ユーザー権限管理が限定的
❌ 大規模データに不向き
適用場面:
- 開発・テスト環境
- 10万レコード以下
- 同時接続 < 100
PostgreSQL（本番・大規模）
利点:
✅ 強力な同時実行制御
✅ レプリケーション・HA構成
✅ 高度なインデックス（GIN, GiST）
✅ JSON型サポート
✅ Full-textサーチ
適用場面:
- 本番環境
- 数百万レコード以上
- 高可用性が必要
📊 テーブル設計
1. Users テーブル
CREATE TABLE users (
user_id TEXT PRIMARY KEY, -- UUID
email TEXT UNIQUE NOT NULL, -- メールアドレス（ユニーク）
password_hash TEXT NOT NULL, -- bcryptハッシュ
created_at TEXT DEFAULT CURRENT_TIMESTAMP,
last_login TEXT,
is_active INTEGER DEFAULT 1, -- 0=無効, 1=有効
-- インデックス
INDEX idx_users_email (email),
INDEX idx_users_active (is_active)
);
Nim実装:
proc createUser*(self: Database, userId, email, passwordHash: string): bool =
try:
self.db.exec(sql"""
INSERT INTO users (user_id, email, password_hash)
VALUES (?, ?, ?)
""", userId, email, passwordHash)
result = true
except:
result = false
proc getUserByEmail*(self: Database, email: string): Option[Row] =
let row = self.db.getRow(sql"""
SELECT user_id, email, password_hash, is_active
FROM users
WHERE email = ?
""", email)
if row[0] != "":
result = some(row)
else:
result = none(Row)
2. Subscriptions テーブル
CREATE TABLE subscriptions (
subscription_id TEXT PRIMARY KEY,
user_id TEXT NOT NULL,
plan_type TEXT NOT NULL, -- free/premium_monthly/enterprise_monthly
status TEXT NOT NULL, -- active/expired/cancelled/suspended
start_date TEXT NOT NULL,
end_date TEXT,
FOREIGN KEY (user_id) REFERENCES users(user_id)
ON DELETE CASCADE, -- ユーザー削除時に連鎖削除
INDEX idx_subscriptions_user (user_id),
INDEX idx_subscriptions_status (status),
INDEX idx_subscriptions_end_date (end_date)
);
Nim実装:
proc createSubscription*(self: Database, subscriptionId, userId: string,
planType: PlanType, endDate: DateTime): bool =
try:
self.db.exec(sql"""
INSERT INTO subscriptions
(subscription_id, user_id, plan_type, status, start_date, end_date)
VALUES (?, ?, ?, 'active', datetime('now'), ?)
""", subscriptionId, userId, $planType, endDate.format("yyyy-MM-dd HH:mm:ss"))
result = true
except:
result = false
proc getActiveSubscription*(self: Database, userId: string): Option[Row] =
let row = self.db.getRow(sql"""
SELECT subscription_id, plan_type, end_date, status
FROM subscriptions
WHERE user_id = ? AND status = 'active'
ORDER BY end_date DESC
LIMIT 1
""", userId)
if row[0] != "":
result = some(row)
else:
result = none(Row)
3. Licenses テーブル
CREATE TABLE licenses (
license_id TEXT PRIMARY KEY,
subscription_id TEXT NOT NULL,
client_id TEXT NOT NULL, -- デバイス識別子
activation_key TEXT UNIQUE NOT NULL, -- JWT
activated_at TEXT DEFAULT CURRENT_TIMESTAMP,
last_validated TEXT,
validation_count INTEGER DEFAULT 0,
is_active INTEGER DEFAULT 1,
device_fingerprint TEXT,
FOREIGN KEY (subscription_id) REFERENCES subscriptions(subscription_id)
ON DELETE CASCADE,
INDEX idx_licenses_subscription (subscription_id),
INDEX idx_licenses_key (activation_key),
INDEX idx_licenses_client (client_id)
);
4. Rate_limits テーブル
CREATE TABLE rate_limits (
user_id TEXT NOT NULL,
endpoint TEXT NOT NULL,
request_count INTEGER DEFAULT 0,
window_start TEXT NOT NULL,
PRIMARY KEY (user_id, endpoint),
INDEX idx_rate_limits_window (window_start)
);
Nim実装:
proc checkRateLimit*(self: Database, userId, endpoint: string, limit: int): bool =
let now = now()
let windowStart = now - initDuration(hours = 1)
let row = self.db.getRow(sql"""
SELECT request_count, window_start
FROM rate_limits
WHERE user_id = ? AND endpoint = ?
""", userId, endpoint)
if row[0] == "":
# 初回リクエスト
self.db.exec(sql"""
INSERT INTO rate_limits (user_id, endpoint, request_count, window_start)
VALUES (?, ?, 1, ?)
""", userId, endpoint, $now)
return true
let count = parseInt(row[0])
let storedStart = parse(row[1], "yyyy-MM-dd HH:mm:ss")
# ウィンドウ期限切れ
if storedStart < windowStart:
self.db.exec(sql"""
UPDATE rate_limits
SET request_count = 1, window_start = ?
WHERE user_id = ? AND endpoint = ?
""", $now, userId, endpoint)
return true
# リミットチェック
if count >= limit:
return false
# カウント増加
self.db.exec(sql"""
UPDATE rate_limits
SET request_count = request_count + 1
WHERE user_id = ? AND endpoint = ?
""", userId, endpoint)
return true
🔗 正規化と関連
ER図
┌─────────────┐
│ Users │
│ user_id (PK)│
│ email │
│ password_ │
│ hash │
└──────┬──────┘
│ 1
│
│ N
┌──────┴──────────┐
│ Subscriptions │
│subscription_id │
│ (PK) │
│ user_id (FK) │
│ plan_type │
│ status │
└──────┬──────────┘
│ 1
│
│ N
┌──────┴──────────┐
│ Licenses │
│ license_id (PK) │
│ subscription_id │
│ (FK) │
│ activation_key │
└─────────────────┘
リレーションシップ
1. Users 1 : N Subscriptions
- 1ユーザーは複数のサブスクリプションを持てる
- 過去のプラン履歴を保持
2. Subscriptions 1 : N Licenses
- 1サブスクリプションで複数デバイスを管理
- デバイス制限（例: 5台まで）
3. CASCADE DELETE
- ユーザー削除 → サブスクリプション削除 → ライセンス削除
🚀 インデックス戦略
プライマリインデックス
-- すべてのテーブルにプライマリキー
PRIMARY KEY (user_id)
PRIMARY KEY (subscription_id)
PRIMARY KEY (license_id)
PRIMARY KEY (user_id, endpoint) -- 複合キー
セカンダリインデックス
-- よく検索されるカラム
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_licenses_key ON licenses(activation_key);
-- 範囲検索
CREATE INDEX idx_subscriptions_end_date ON subscriptions(end_date);
CREATE INDEX idx_rate_limits_window ON rate_limits(window_start);
-- 複合インデックス
CREATE INDEX idx_subscriptions_user_status
ON subscriptions(user_id, status);
パフォーマンス比較
-- インデックスなし
SELECT * FROM licenses WHERE activation_key = 'xxx';
→ Full Table Scan: 10万レコード → 500ms
-- インデックスあり
SELECT * FROM licenses WHERE activation_key = 'xxx';
→ Index Seek: 1レコード → 5ms
約100倍高速！
🌟 まとめ
データベース設計の要点:
-
適切なテーブル分割
- Users / Subscriptions / Licenses
-
外部キー制約
- データ整合性の保証
- CASCADE DELETE
-
インデックス戦略
- よく検索されるカラム
- 複合インデックス
-
Nim実装
- Option型でNULL安全
- プリペアドステートメント
前回: Day 11: RESTful API設計
次回: Day 13: レートリミットの実装
Happy Learning! 🎉