Database Design — ERD থেকে Normalization

ERD

[Customer] 1 ─── M [Order]
 id (PK)              id (PK)
 name                 customer_id (FK)
 email                total

Cardinality

• 1:1 — User ↔ UserProfile
• 1:N — Customer ↔ Orders
• M:N — Student ↔ Course (junction)

Keys

Key	বর্ণনা
Primary	unique, NOT NULL
Foreign	another table reference
Composite	একাধিক column
Surrogate	auto-increment id
Natural	NID, ISBN

1NF

Atomic value, repeating group না।

❌ Phones: "0171xxx, 0181xxx"
✅ Separate UserPhones(UserId, Phone)

2NF

1NF + non-key পুরো PK-এর উপর depend।

❌ OrderItems(OrderId, ProductId, ProductName, Qty)
✅ Products(ProductId, ProductName) আলাদা

3NF

2NF + transitive dependency নেই।

❌ Employee(Id, Name, DeptId, DeptName)
✅ Department(DeptId, DeptName) আলাদা

BCNF

3NF strict — সব determinant must be candidate key।

Denormalization কখন?

Read-heavy reporting-এ JOIN cost বেশি। Trade-off: storage বেশি, write complex, read দ্রুত।

// Reporting table
OrderSummary(OrderId, CustomerName, Products, Total)

Best Practices

• Always created_at, updated_at
• Soft delete (deleted_at) prefer
• UUID vs auto-increment — distributed-এ UUID
• NULLABLE সাবধানে
• Index FK column
• Avoid storing computed values
• Naming: snake_case consistent