| Category | Principle | Why It’s Important | Example From Our Chat |
|---|---|---|---|
| Architecture | 1️⃣ Data Integrity Belongs in the Database | The database is the final source of truth and must prevent corruption. | UNIQUE(slug), UNIQUE(expression), normalized schema |
| Architecture | 2️⃣ Business Logic Belongs in the Application | Workflows, permissions, and domain rules should stay outside DB for flexibility. | We kept user rules out of triggers and procedures |
| Architecture | 3️⃣ Triggers Enforce Invariants, Not Business Rules | Triggers should guarantee consistency, not run workflows. | Slug auto-generation ✅ Payment logic ❌ |
| Architecture | 4️⃣ Stored Procedures Are Gateways | They centralize write logic but don’t replace integrity constraints. | sp_insert_idiom() handled slug + dedupe |
| Architecture | 5️⃣ Functions Are Pure Calculators | Reusable, deterministic, side‑effect free. | fn_make_slug() used in UPDATE and procedure |
| Data Processing | 6️⃣ Order of Transformation Matters | Improper ordering corrupts derived values. | Regex before accent removal broke slugs |
| Schema Management | 7️⃣ Schema Changes Must Be Controlled | ALTER TABLE can lock production data and cause downtime. | We added UNIQUE only after cleaning duplicates |
| Deployment | 8️⃣ Idempotency Is Critical | Scripts must be safe to re-run without corrupting data. | INSERT IGNORE and UNIQUE constraints |
| Resilience | 9️⃣ Defensive Design > Trusting the App | The DB must guard against accidental misuse. | Constraints + optional trigger safety net |
| System Design | 🔟 Clean Architecture Requires Boundaries | Clear separation reduces technical debt and confusion. | Separated: normalization, slug logic, insert workflow, constraints |
| Scaling Concern | What Changes at 10M Rows | Why It Matters | Architectural Decision |
|---|---|---|---|
| Primary Key | INT may overflow | 10M safe in INT, but future growth risk | Use BIGINT for id |
| Slug Lookup | Slug becomes main read path | Every page load = SELECT by slug | Keep UNIQUE INDEX on slug |
| Letter Column | Grouping becomes heavy | Frequent GROUP BY letter queries | Keep indexed letter column |
| Search | LIKE queries become slow | Full table scan at scale | Use FULLTEXT index or external search (Elasticsearch) |
| Insert Performance | Triggers/procedures called millions of times | Small inefficiencies multiply | Keep slug function deterministic and lightweight |
| Bulk Imports | Large batch loads slow down | Index updates expensive | Disable non-critical indexes during bulk load |
| Pagination | OFFSET becomes slow | OFFSET 1M is expensive | Use keyset pagination (WHERE id > ?) |
| Index Size | Indexes consume RAM | 10M slugs = large B-tree | Ensure enough buffer pool memory |
| Read Traffic | High read ratio | Dictionary = read-heavy system | Add read replicas |
| Caching | Hot idioms repeatedly accessed | Reduce DB load | Use Redis or application caching |
| Partitioning | Single table becomes large | Maintenance operations slower | Partition by first letter or hash |
| Search Ranking | Fulltext scoring needed | Users expect intelligent results | Move search to dedicated search engine |
| Writes vs Reads | Mostly reads | Different optimization strategy | Tune for read-heavy workload |
| Data Integrity | Constraint checks cost more | UNIQUE index lookup per insert | Keep constraints — integrity > micro performance |
| Migration Strategy | ALTER TABLE becomes expensive | Locks large tables | Use online DDL / rolling migrations |
Implementation mechanics
| Topic | Implementation | Why It’s Good | Why It Can Be Bad | Architectural Insight |
|---|---|---|---|---|
| Table Design | Created idioms table with slug, letter, meanings, etc. |
Normalized structure, scalable, searchable | Over-design early can slow iteration | Design schema for future growth, not just current data |
| Slug Column | Added slug VARCHAR(255) |
SEO-friendly URLs, fast lookup | Needs strict uniqueness & normalization | Slug is derived data → should be deterministic |
| UNIQUE(slug) | Added unique index | Prevents duplicates at DB level | Can block bulk updates if data dirty | DB should enforce integrity, not rely on app |
| UNIQUE(expression) | Added unique constraint | Prevents accidental duplicate idioms | May block legitimate variations | Decide whether expression uniqueness is a business rule |
| ALTER TABLE | Modified columns, added constraints | Schema evolves cleanly | Locks table in large datasets | Schema migrations must be version-controlled |
| Bulk INSERT | Inserted A–J idioms | Efficient data loading | Re-running caused duplicates | Use INSERT IGNORE or constraints for idempotency |
| Duplicate Cleanup | Used self-join DELETE | Cleaned data safely | Needs care with safe update mode | Always verify with SELECT before DELETE |
| Safe Update Mode | Encountered error 1175 | Prevents accidental full-table updates | Annoying during controlled updates | Safety features protect production systems |
| Slug Generation Order | Lowercase → remove accents → regex → trim | Produces correct slugs | Wrong order destroys characters | Data transformation order matters |
| Function (fn_make_slug) | Created reusable deterministic function | Can use in SELECT, UPDATE, procedures | Cannot modify tables | Functions = value calculators |
| Stored Procedure | Created sp_insert_idiom |
Encapsulates insert logic & dedupe | Only safe if always used | Procedure = controlled data gateway |
| Function vs Procedure | Separated computation from action | Clean responsibility separation | Misuse causes confusion | Function = value, Procedure = job |
| Trigger Discussion | Debated enabling/disabling | Protects DB from bad inserts | Hidden logic, harder debugging | Triggers enforce integrity, not business workflows |
| Feature Flag Trigger | Used session variable toggle | No redeploy needed | Session-scoped behavior | Elegant toggle without schema change |
| Business Logic in DB? | Differentiated data rules vs workflows | Data integrity belongs in DB | Business workflows do not | Layer separation is key architecture principle |
| Derived Fields (slug, letter) | Auto-generated | Prevents inconsistency | Must ensure deterministic logic | Derived data should not depend on user input |
| Data Integrity Strategy | UNIQUE + procedure + optional trigger | Layered protection | Overlapping logic if poorly designed | Defensive database design is professional |
| Idempotent Inserts | Used INSERT IGNORE |
Safe re-runs | May hide real errors | Scripts must be rerunnable safely |
| Architecture Philosophy | Discussed DB-centric vs App-centric | Clear separation improves maintainability | Mixing layers creates technical debt | DB = guardian of truth, App = business brain |
