database model คือ

Database Model

Database Model Relational NoSQL Document Graph Key-Value Column-Family Time-Series การจัดเก็บข้อมูล SQL

Relational Model

# === Relational Database ===

# CREATE TABLE users (
#     id SERIAL PRIMARY KEY,
#     username VARCHAR(50) UNIQUE NOT NULL,
#     email VARCHAR(100) NOT NULL,
#     created_at TIMESTAMP DEFAULT NOW()
# );
#
# CREATE TABLE orders (
#     id SERIAL PRIMARY KEY,
#     user_id INTEGER REFERENCES users(id),
#     total DECIMAL(10,2) NOT NULL,
#     status VARCHAR(20) DEFAULT 'pending',
#     created_at TIMESTAMP DEFAULT NOW()
# );
#
# CREATE TABLE order_items (
#     id SERIAL PRIMARY KEY,
#     order_id INTEGER REFERENCES orders(id),
#     product_name VARCHAR(100) NOT NULL,
#     quantity INTEGER NOT NULL,
#     price DECIMAL(10,2) NOT NULL
# );
#
# -- Join Query
# SELECT u.username, o.id AS order_id, o.total, o.status
# FROM users u
# JOIN orders o ON u.id = o.user_id
# WHERE o.status = 'completed'
# ORDER BY o.created_at DESC;
#
# -- Aggregation
# SELECT u.username, COUNT(o.id) AS order_count, SUM(o.total) AS total_spent
# FROM users u
# LEFT JOIN orders o ON u.id = o.user_id
# GROUP BY u.username
# HAVING SUM(o.total) > 1000;

from dataclasses import dataclass

@dataclass
class RelationalFeature:
    feature: str
    description: str
    benefit: str
    trade_off: str

features = [
    RelationalFeature("ACID Transactions",
        "Atomicity Consistency Isolation Durability",
        "ข้อมูลถูกต้องเสมอ ไม่มี Partial Update",
        "ช้ากว่า NoSQL สำหรับ Simple Operations"),
    RelationalFeature("Normalization",
        "แบ่งข้อมูลเป็นตารางย่อย ลด Redundancy",
        "ข้อมูลไม่ซ้ำ อัพเดทที่เดียว",
        "ต้อง JOIN หลายตาราง อาจช้า"),
    RelationalFeature("SQL",
        "Structured Query Language มาตรฐาน",
        "เรียนรู้ครั้งเดียวใช้ได้ทุก RDBMS",
        "Complex Query อาจยากต่อการ Optimize"),
    RelationalFeature("Foreign Key",
        "สร้างความสัมพันธ์ระหว่างตาราง",
        "Referential Integrity ข้อมูลสัมพันธ์ถูกต้อง",
        "Constraint อาจทำให้ Write ช้าลง"),
    RelationalFeature("Index",
        "สร้าง Index เพิ่มความเร็วการค้นหา",
        "SELECT เร็วขึ้น 10-1000x",
        "ใช้ Storage เพิ่ม Write ช้าลง"),
]

print("=== Relational Features ===")
for f in features:
    print(f"  [{f.feature}] {f.description}")
    print(f"    Benefit: {f.benefit}")
    print(f"    Trade-off: {f.trade_off}")

NoSQL Models

# === NoSQL Database Models ===

# Document (MongoDB)
# db.users.insertOne({
#   username: "john",
#   email: "john@example.com",
#   profile: { bio: "Developer", skills: ["Python", "JavaScript"] },
#   orders: [
#     { total: 150.00, items: ["laptop case", "mouse"] },
#     { total: 89.99, items: ["keyboard"] }
#   ]
# })

# Key-Value (Redis)
# SET user:1001 '{"name":"john","email":"john@example.com"}'
# GET user:1001
# HSET user:1001 name "john" email "john@example.com"
# HGETALL user:1001
# SETEX session:abc123 3600 '{"user_id":1001}'

# Graph (Neo4j Cypher)
# CREATE (john:User {name: "John"})
# CREATE (jane:User {name: "Jane"})
# CREATE (john)-[:FOLLOWS]->(jane)
# CREATE (john)-[:LIKES]->(post:Post {title: "Hello"})
# MATCH (u:User)-[:FOLLOWS]->(friend)-[:LIKES]->(post)
# WHERE u.name = "John"
# RETURN friend.name, post.title

@dataclass
class NoSQLModel:
    model: str
    data_structure: str
    query: str
    strength: str
    weakness: str
    use_case: str

models = [
    NoSQLModel("Document",
        "JSON/BSON nested documents",
        "MongoDB Query Language, Aggregation Pipeline",
        "Flexible schema, nested data, horizontal scale",
        "No JOIN (denormalized), eventual consistency",
        "Web API, CMS, E-commerce catalog, User profiles"),
    NoSQLModel("Key-Value",
        "Simple key → value pairs",
        "GET/SET/DEL, Hash, List, Set operations",
        "Fastest read/write, simple model, in-memory",
        "No complex queries, limited data modeling",
        "Cache, Session store, Rate limiting, Leaderboard"),
    NoSQLModel("Column-Family",
        "Column families with row keys",
        "CQL (Cassandra), HBase Shell",
        "Write-heavy, linear scale, high availability",
        "Limited query patterns, eventual consistency",
        "IoT data, Messaging, Activity feeds, Analytics"),
    NoSQLModel("Graph",
        "Nodes + Edges with properties",
        "Cypher (Neo4j), Gremlin, SPARQL",
        "Relationship traversal O(1), pattern matching",
        "Not for bulk analytics, smaller ecosystem",
        "Social network, Recommendation, Fraud detection"),
]

print("=== NoSQL Models ===")
for m in models:
    print(f"  [{m.model}] Structure: {m.data_structure}")
    print(f"    Query: {m.query}")
    print(f"    +: {m.strength}")
    print(f"    -: {m.weakness}")
    print(f"    Use: {m.use_case}")

Decision Guide

# === Database Selection Guide ===

@dataclass
class UseCase:
    scenario: str
    requirements: str
    recommended: str
    why: str
    alternative: str

cases = [
    UseCase("E-commerce Order System",
        "ACID transactions, complex joins, inventory consistency",
        "PostgreSQL (Relational)",
        "ต้องการ ACID สำหรับ Payment Inventory ถูกต้อง",
        "CockroachDB (Distributed SQL)"),
    UseCase("Social Media Feed",
        "High write throughput, flexible content, global scale",
        "Cassandra (Column-Family) + Redis (Cache)",
        "Write-heavy ได้ Global Replication + Fast Read Cache",
        "DynamoDB + ElastiCache"),
    UseCase("Real-time Chat App",
        "Low latency, pub/sub, presence, message history",
        "Redis (Key-Value) + MongoDB (Document)",
        "Redis สำหรับ Real-time MongoDB สำหรับ History",
        "Firebase Realtime Database"),
    UseCase("Fraud Detection System",
        "Complex relationship traversal, pattern matching",
        "Neo4j (Graph)",
        "Graph Database หา Pattern ความสัมพันธ์ผิดปกติได้เร็ว",
        "Amazon Neptune, TigerGraph"),
    UseCase("IoT Sensor Monitoring",
        "Time-series data, high ingestion rate, aggregation",
        "InfluxDB / TimescaleDB (Time-Series)",
        "Optimized สำหรับ Time-series Compression Aggregation",
        "Cassandra with time-based partitioning"),
]

print("=== Database Selection ===")
for c in cases:
    print(f"  [{c.scenario}]")
    print(f"    Requirements: {c.requirements}")
    print(f"    Recommended: {c.recommended}")
    print(f"    Why: {c.why}")
    print(f"    Alternative: {c.alternative}")

เคล็ดลับ

• Default: เริ่มด้วย PostgreSQL ถ้าไม่แน่ใจ รองรับ JSON JSONB ด้วย
• Polyglot: ระบบใหญ่มักใช้หลาย Database Model ร่วมกัน
• Schema: Relational เหมาะเมื่อ Schema ชัด NoSQL เหมาะเมื่อ Schema เปลี่ยนบ่อย
• Scale: NoSQL Scale แนวนอนง่ายกว่า Relational Scale แนวตั้ง
• ACID: ถ้าต้องการ ACID เลือก Relational หรือ Distributed SQL

Database Model คืออะไร

รูปแบบจัดเก็บข้อมูล Relational Table Document JSON Key-Value Graph Node Edge Column-Family Time-Series โครงสร้าง ความสัมพันธ์

สรุป

Database Model Relational SQL NoSQL Document MongoDB Key-Value Redis Graph Neo4j Column-Family Cassandra Time-Series InfluxDB เลือกตาม Use Case