データベース設計 - AI×Web開発中級編

データベース設計の基本概念

テーブル（Table）

データを格納する表。行（レコード）と列（カラム）で構成。

users テーブル

id	name	email
1	山田	yamada@...

リレーション（Relation）

テーブル間の関連。外部キー（FK）で参照。

posts.author_id -> users.id

「記事は1人のユーザーに属する」

主なリレーションの種類

1対多（One-to-Many）

1人のユーザーが複数の記事を持つ

User(1) -> Post(多)

多対多（Many-to-Many）

記事は複数のタグを持ち、タグは複数の記事に付く

Post(多) <-> Tag(多) 中間テーブルが必要

1対1（One-to-One）

ユーザーは1つのプロフィールを持つ

User(1) <-> Profile(1)

ブログアプリのテーブル設計

ER図（エンティティ関連図）

users

id, email, name, password

-> 1:N ->

posts

id, title, content, author_id

users テーブル

カラム名	型	制約	説明
id	INT	PK, AUTO_INCREMENT	主キー
email	VARCHAR(255)	UNIQUE, NOT NULL	メールアドレス
name	VARCHAR(100)	NOT NULL	表示名
password_hash	VARCHAR(255)	NOT NULL	ハッシュ化パスワード
created_at	DATETIME	DEFAULT NOW()	作成日時
updated_at	DATETIME	ON UPDATE NOW()	更新日時

posts テーブル

カラム名	型	制約	説明
id	INT	PK, AUTO_INCREMENT	主キー
title	VARCHAR(255)	NOT NULL	記事タイトル
content	TEXT	NOT NULL	記事本文
author_id	INT	FK -> users.id	著者ID
created_at	DATETIME	DEFAULT NOW()	作成日時
updated_at	DATETIME	ON UPDATE NOW()	更新日時

MySQL でのテーブル作成

-- usersテーブル
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- postsテーブル
CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    author_id INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE
);

-- インデックス（検索高速化）
CREATE INDEX idx_posts_author_id ON posts(author_id);
CREATE INDEX idx_posts_created_at ON posts(created_at DESC);

SQLAlchemy モデル（Python）

from sqlalchemy import Column, Integer, String, Text, DateTime, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from database import Base

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String(255), unique=True, nullable=False, index=True)
    name = Column(String(100), nullable=False)
    password_hash = Column(String(255), nullable=False)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())

    # リレーション
    posts = relationship("Post", back_populates="author")


class Post(Base):
    __tablename__ = "posts"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String(255), nullable=False)
    content = Column(Text, nullable=False)
    author_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())

    # リレーション
    author = relationship("User", back_populates="posts")

ポイント：SQLAlchemyのrelationshipを使うと、post.authorのようにオブジェクト経由でリレーション先にアクセスできます。

設計のコツ

良い設計

○ 適切な正規化（データの重複を避ける）
○ 明確な命名（users, posts）
○ created_at, updated_atを必ず含める
○ 適切なインデックス設定

避けるべきこと

✗ JSONカラムの多用
✗ パスワードを平文で保存
✗ 曖昧な命名（data, info）
✗ 外部キー制約なしの参照

まとめ

✓ テーブル間のリレーション（1対多、多対多）を理解する
✓ 適切な型と制約（NOT NULL, UNIQUE, FK）を設定
✓ created_at, updated_atは必須
✓ パスワードは必ずハッシュ化して保存