Inkdown
Start writing

Study

70 files·12 subfolders

Shared Workspace

Study
AI eng

03-database-design

Shared from "Study" on Inkdown

03 - Database Design & Normalization

The Goal of Database Design

Good database design ensures:

  • Data Integrity: No duplication, no inconsistencies
  • Efficiency: Fast queries, minimal storage
  • Scalability: Can grow without redesign
  • Maintainability: Easy to understand and modify

The Design Process

Plain text

Entity-Relationship (ER) Diagrams

Visual representation of data structure:

basic-ques
core
Revision w/ Whiteboard
CN Basics - 1
CN Basics - 2
DNS
Event loop
programming-language-concepts.md
zero-language-explanation.md
DB
Quick
databases-deep-dive.md
01-introduction.md
02-relational-databases.md
03-database-design.md
04-indexing.md
05-transactions-acid.md
06-nosql-databases.md
07-query-optimization.md
08-replication-ha.md
09-sharding-partitioning.md
10-caching-strategies.md
11-cap-theorem.md
12-connection-pooling.md
13-backup-recovery.md
14-monitoring.md
15-database-selection.md
README.md
JS
core topics
Event loop
Merlin Backend
01-Orchestration.md
02-DeepResearch.md
03-Search.md
04-Scraping.md
05-Streaming.md
06-MultiProviderLLM.md
07-MemoryAndContext.md
08-ErrorHandling.md
09-RateLimiting.md
10-TaskQueue.md
11-SecurityAndAuth.md
Orchestration-2nd-draft
Mobile
Build Alternative
Bundling
metro-bundler-deep-dive.md
OpenAI Agents Python
00_OVERVIEW.md
01_AGENT_SYSTEM.md
02_RUNNER_SYSTEM.md
03_TOOL_SYSTEM.md
04_ITEMS_SYSTEM.md
05_GUARDRAILS.md
06_HANDOFFS.md
07_MEMORY_SESSIONS.md
08_MODEL_PROVIDERS.md
09_SANDBOX_SYSTEM.md
10_TRACING.md
11_RUN_STATE.md
12_CONTEXT.md
13_LIFECYCLE_HOOKS.md
14_CONFIGURATION.md
15_ERROR_HANDLING.md
16_STREAMING.md
17_EXTENSIONS.md
18_MCP_INTEGRATION.md
19_BEST_PRACTICES.md
20_ARCHITECTURE_PATTERNS.md
opencode-study
context-handling
core
Python
Alembic
Basics
sqlalchemy - fastapi
SQLAlchemy overview
tweets
system_design_for_agentic_apps.md
Agent Loop
Plain text

Relationships Explained

1. One-to-One (1:1)

Each record in Table A relates to exactly one record in Table B.

Sql
2. One-to-Many (1:M)

Each record in Table A relates to many records in Table B.

Sql
3. Many-to-Many (M:N)

Records in Table A relate to many in Table B, and vice versa.

Sql

Normalization

Normalization is the process of organizing data to minimize redundancy and dependency.

Normal Forms Hierarchy
Plain text
0NF - Unnormalized (The Mess)
Plain text
1NF - First Normal Form

Rules:

  1. Atomic values (no multi-valued attributes)
  2. No repeating groups
  3. Each row uniquely identifiable
Plain text
2NF - Second Normal Form

Rules:

  1. Must be in 1NF
  2. No partial dependencies (non-key attributes must depend on entire key)

When needed: Only for tables with composite keys.

Plain text
3NF - Third Normal Form

Rules:

  1. Must be in 2NF
  2. No transitive dependencies (non-key attributes depend only on key)
Plain text
Higher Normal Forms (Briefly)

BCNF (Boyce-Codd Normal Form): Stricter version of 3NF

  • Every determinant is a candidate key

4NF: No multi-valued dependencies

Plain text

5NF: No join dependencies (very rare to need)

Denormalization: When to Break the Rules

Sometimes you intentionally add redundancy for performance.

When to Denormalize:
  1. Read-heavy, rarely updated data
  2. Complex joins are too slow
  3. Aggregation queries are frequent
  4. Real-time requirements
Common Denormalization Patterns:
Sql

Design Patterns

1. Soft Deletes
Sql
2. Audit Logging
Sql
3. Multi-tenancy Patterns

Separate Databases:

Plain text

Separate Schemas:

Sql

Shared Schema with Tenant ID:

Sql
4. Handling Time Zones
Sql
5. Handling Large Text
Sql

Design Checklist

Before finalizing your schema:

  • All tables have primary keys
  • Foreign keys have indexes
  • Appropriate data types chosen
  • Constraints defined (NOT NULL, CHECK, etc.)
  • Normalized to at least 3NF
  • Identified denormalization needs
  • Soft deletes implemented if needed
  • Audit fields added
  • Multi-tenancy considered
  • Timezone handling planned
  • Growth estimates made (will BIGINT be needed?)

Next: Indexing Deep Dive