database field คือ

Database Field

Database Field คอลัมน์ตาราง Data Type Constraints NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY Index Normalization Schema Design Production

Schema Design

# === Database Schema Design ===

# -- Users Table
# CREATE TABLE users (
#   id BIGSERIAL PRIMARY KEY,
#   username VARCHAR(50) UNIQUE NOT NULL,
#   email VARCHAR(255) UNIQUE NOT NULL,
#   password_hash VARCHAR(255) NOT NULL,
#   first_name VARCHAR(100),
#   last_name VARCHAR(100),
#   phone VARCHAR(20),
#   is_active BOOLEAN DEFAULT TRUE,
#   role VARCHAR(20) DEFAULT 'user' CHECK(role IN ('admin','editor','user')),
#   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
#   updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
# );
#
# -- Products Table
# CREATE TABLE products (
#   id BIGSERIAL PRIMARY KEY,
#   name VARCHAR(255) NOT NULL,
#   slug VARCHAR(255) UNIQUE NOT NULL,
#   description TEXT,
#   price DECIMAL(10,2) NOT NULL CHECK(price >= 0),
#   stock INT NOT NULL DEFAULT 0 CHECK(stock >= 0),
#   category_id INT REFERENCES categories(id),
#   is_published BOOLEAN DEFAULT FALSE,
#   metadata JSONB DEFAULT '{}',
#   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
#   updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
# );
#
# -- Orders Table
# CREATE TABLE orders (
#   id BIGSERIAL PRIMARY KEY,
#   user_id BIGINT NOT NULL REFERENCES users(id),
#   total DECIMAL(12,2) NOT NULL CHECK(total >= 0),
#   status VARCHAR(20) DEFAULT 'pending'
#     CHECK(status IN ('pending','confirmed','shipped','delivered','cancelled')),
#   shipping_address JSONB NOT NULL,
#   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
#   updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
# );
#
# -- Indexes
# CREATE INDEX idx_users_email ON users(email);
# CREATE INDEX idx_products_category ON products(category_id);
# CREATE INDEX idx_products_slug ON products(slug);
# CREATE INDEX idx_orders_user ON orders(user_id);
# CREATE INDEX idx_orders_status ON orders(status);
# CREATE INDEX idx_orders_created ON orders(created_at);

from dataclasses import dataclass

@dataclass
class FieldDesign:
    table: str
    field: str
    data_type: str
    constraints: str
    index: bool
    reason: str

fields = [
    FieldDesign("users", "id", "BIGSERIAL", "PRIMARY KEY", True, "Auto-increment unique ID"),
    FieldDesign("users", "email", "VARCHAR(255)", "UNIQUE NOT NULL", True, "Login identifier, searchable"),
    FieldDesign("users", "password_hash", "VARCHAR(255)", "NOT NULL", False, "Never query by password"),
    FieldDesign("users", "is_active", "BOOLEAN", "DEFAULT TRUE", True, "Filter active users"),
    FieldDesign("products", "price", "DECIMAL(10,2)", "NOT NULL CHECK>=0", False, "Exact money precision"),
    FieldDesign("products", "metadata", "JSONB", "DEFAULT '{}'", True, "Flexible extra data, GIN index"),
    FieldDesign("orders", "status", "VARCHAR(20)", "CHECK IN(...)", True, "Frequent WHERE filter"),
    FieldDesign("orders", "created_at", "TIMESTAMP", "DEFAULT NOW()", True, "Sort and range queries"),
]

print("=== Field Design Decisions ===")
for f in fields:
    idx = "Indexed" if f.index else "No Index"
    print(f"  [{f.table}.{f.field}] {f.data_type}")
    print(f"    Constraints: {f.constraints} | {idx}")
    print(f"    Reason: {f.reason}")

Index and Performance

# === Index Strategy ===

# -- B-tree Index (default) — Equality and Range
# CREATE INDEX idx_orders_created ON orders(created_at);
# -- Good for: WHERE created_at > '2024-01-01'
#
# -- Composite Index — Multiple columns
# CREATE INDEX idx_orders_user_status ON orders(user_id, status);
# -- Good for: WHERE user_id = 1 AND status = 'pending'
#
# -- Partial Index — Filtered
# CREATE INDEX idx_active_users ON users(email) WHERE is_active = TRUE;
# -- Good for: WHERE email = 'x' AND is_active = TRUE
#
# -- GIN Index — JSON/Array/Full-text
# CREATE INDEX idx_products_meta ON products USING GIN(metadata);
# -- Good for: WHERE metadata @> '{"color": "red"}'
#
# -- Expression Index
# CREATE INDEX idx_users_lower_email ON users(LOWER(email));
# -- Good for: WHERE LOWER(email) = 'user@example.com'

# EXPLAIN ANALYZE
# EXPLAIN ANALYZE SELECT * FROM orders
#   WHERE user_id = 1 AND status = 'pending'
#   ORDER BY created_at DESC LIMIT 10;
# -- Index Scan using idx_orders_user_status

@dataclass
class IndexType:
    index_type: str
    use_case: str
    example: str
    performance: str
    storage: str

index_types = [
    IndexType("B-tree", "Equality Range Sort", "WHERE age > 18 ORDER BY name", "ดีมาก", "ปานกลาง"),
    IndexType("Hash", "Equality only", "WHERE id = 123", "ดีมากสำหรับ =", "ต่ำ"),
    IndexType("GIN", "JSON Array Full-text", "WHERE data @> '{}'", "ดีสำหรับ contain", "สูง"),
    IndexType("GiST", "Geometry Range", "WHERE location <-> point", "ดีสำหรับ spatial", "สูง"),
    IndexType("BRIN", "Large sequential data", "WHERE created_at > date", "ดีมาก ขนาดเล็ก", "ต่ำมาก"),
    IndexType("Composite", "Multi-column queries", "WHERE a = 1 AND b = 2", "ดีมาก", "ปานกลาง"),
    IndexType("Partial", "Filtered subset", "WHERE status = 'active'", "ดีมาก ขนาดเล็ก", "ต่ำ"),
]

print("\n=== Index Types ===")
for i in index_types:
    print(f"  [{i.index_type}] Use: {i.use_case}")
    print(f"    Example: {i.example}")
    print(f"    Performance: {i.performance} | Storage: {i.storage}")

Best Practices

# === Field Design Best Practices ===

@dataclass
class BestPractice:
    category: str
    practice: str
    bad_example: str
    good_example: str

practices = [
    BestPractice("Naming", "ใช้ snake_case ชัดเจน", "fn, usr_nm, dt", "first_name, username, created_at"),
    BestPractice("Type", "ใช้ DECIMAL สำหรับเงิน", "FLOAT price = 19.99", "DECIMAL(10,2) price = 19.99"),
    BestPractice("Type", "ใช้ VARCHAR กำหนดความยาว", "TEXT username", "VARCHAR(50) username"),
    BestPractice("Constraint", "ใส่ NOT NULL ถ้าต้องมี", "email VARCHAR(255)", "email VARCHAR(255) NOT NULL"),
    BestPractice("Constraint", "ใช้ CHECK validate", "status VARCHAR(20)", "status VARCHAR(20) CHECK(IN(...))"),
    BestPractice("Default", "ตั้ง Default ที่เหมาะสม", "is_active BOOLEAN", "is_active BOOLEAN DEFAULT TRUE"),
    BestPractice("Timestamp", "ใส่ created_at updated_at", "ไม่มี Timestamp", "created_at DEFAULT NOW()"),
    BestPractice("Index", "Index Field ที่ WHERE บ่อย", "ไม่มี Index", "CREATE INDEX idx_user_email"),
]

print("Best Practices:")
for p in practices:
    print(f"  [{p.category}] {p.practice}")
    print(f"    Bad: {p.bad_example}")
    print(f"    Good: {p.good_example}")

anti_patterns = {
    "EAV (Entity-Attribute-Value)": "ยืดหยุ่นเกินไป Query ช้า ใช้ JSONB แทน",
    "Storing CSV in field": "ผิด Normal Form ใช้ Junction Table แทน",
    "Over-indexing": "Index ทุก Field ช้าเวลา INSERT UPDATE",
    "VARCHAR(MAX)": "เสีย Storage ใช้ VARCHAR(255) หรือตามจริง",
    "No Foreign Key": "ข้อมูลไม่ Consistent ใส่ FK เสมอ",
    "Reserved Words": "อย่าใช้ order user table เป็นชื่อ Field",
}

print(f"\n\nAnti-Patterns:")
for k, v in anti_patterns.items():
    print(f"  [{k}]: {v}")

เคล็ดลับ

• Type: เลือก Data Type เล็กที่สุดที่เพียงพอ
• NOT NULL: ใส่ NOT NULL ทุก Field ที่ต้องมีข้อมูล
• Index: Index Field ที่ WHERE JOIN ORDER BY บ่อย
• DECIMAL: ใช้ DECIMAL สำหรับเงิน ไม่ใช้ FLOAT
• Timestamp: ใส่ created_at updated_at ทุกตาราง

Database Field คืออะไร

คอลัมน์ตาราง เก็บข้อมูลประเภทเดียว Data Type VARCHAR INT DATE Constraints NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY DEFAULT

สรุป

Database Field Data Type Constraints NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY Index B-tree GIN Composite Schema Design Normalization Best Practices Production