A logical, RDBMS-agnostic method for designing sound relational databases — the decisions a schema forces and how a disciplined logical-design methodology settles them, written so an agent applies them while designing or reviewing a schema. Each rule names a specific wrong default it corrects; there is no rule for things the model already gets right.
The whole method produces the logical structure first — the tables, fields, keys, relationships, and integrity rules an organization's information requires — deliberately independent of any particular RDBMS product or physical/performance concern. Followed faithfully, it yields fully normalized tables without treating normalization as a separate back-end phase.
When to Apply
- Designing a new relational schema from requirements
- Reviewing or refactoring an existing schema for structural soundness
- Resolving repeating groups, multivalued/multipart fields, or redundant data
- Choosing candidate, primary, and foreign keys for a table
- Modeling one-to-one, one-to-many, many-to-many, or self-referencing relationships
- Deciding deletion rules (restrict, cascade, nullify, deny, set default) and participation constraints
- Deciding where a constraint belongs — field spec, relationship, validation table, or application
- Diagnosing a schema that duplicates, loses, or corrupts data
This skill covers logical design. It does not cover SQL dialects, indexing, partitioning, query tuning, or analytical/dimensional (star-schema) modeling — those are physical/implementation concerns handled after the logical design is sound.
Rule Categories
| # | Category | Prefix | Covers |
|---|---|---|---|
| 1 | Design Process & Requirements | proc- |
Design logically before choosing an RDBMS, follow the sequence, drive from mission + analysis, normalization is built in |
| 2 | Table Structure | tbl- |
One subject per table, the ideal table, no reference fields, minimal redundancy |
| 3 | Field Design | fld- |
The ideal field, single values, atomic fields, no stored calculations, clear names |
| 4 | Keys | key- |
Candidate key elements, one primary key per table, foreign keys mirror primary keys, stable non-sensitive keys |
| 5 | Relationships | rel- |
Junction tables for many-to-many, foreign-key placement, deletion rules, participation, self-referencing |
| 6 | Data Integrity, Rules & Views | intg- |
The four integrity levels, field specifications, database-vs-application rules, validation tables, views |
| 7 | Antipatterns | anti- |
Flat-file, spreadsheet-as-database, RDBMS-driven design, when bending the rules is defensible |
| 8 | Terminology | term- |
Data vs information, nulls, core relational vocabulary |
Quick Reference
1. Design Process & Requirements
proc-follow-the-design-sequence— later steps depend on earlier ones; skipping steps yields poor integrityproc-design-logically-before-choosing-rdbms— design the logical structure with no RDBMS in mind; choose and implement afterwardproc-start-from-mission-and-objectives— a mission statement and objectives scope the database and reveal its subjectsproc-analyze-and-interview-before-designing— analyze the current system and interview users and management before inventing fieldsproc-normalization-is-built-in— the ideal-field/ideal-table/key guidelines yield normalized tables; normalization is not a separate phase
2. Table Structure
tbl-one-subject-per-table— each table represents exactly one subject (object or event)tbl-ideal-table-checklist— the six-point test for a sound table structuretbl-no-reference-fields— don't copy fields from another table for reporting conveniencetbl-minimize-redundant-data— FK-driven redundancy is fine; keep everything else to an absolute minimum
3. Field Design
fld-ideal-field-checklist— the test for a sound fieldfld-store-single-values-not-lists— a multivalued field becomes its own tablefld-keep-fields-atomic— decompose multipart/composite fields into one field per distinct itemfld-derive-dont-store-calculated-values— calculations belong in a view, not a stored fieldfld-clear-singular-field-names— one unambiguous, singular name per characteristic
4. Keys
key-candidate-key-elements— the test a field (or field set) must pass to be a candidate keykey-one-primary-key-per-table— exactly one non-null, unique, stable primary key per tablekey-foreign-key-mirrors-its-primary-key— same name, replica spec, values drawn from the referenced primary keykey-prefer-stable-non-sensitive-keys— a key value should rarely change and must not expose sensitive data
5. Relationships
rel-junction-table-for-many-to-many— resolve M:N with a linking table keyed on both foreign keysrel-foreign-key-on-the-many-side— the foreign key lives on the many side of a one-to-manyrel-deletion-rule-guards-orphans— every relationship gets a deletion rule; restrict by defaultrel-participation-encodes-constraints— mandatory/optional and degree (min,max) capture real business limitsrel-self-referencing-relationships— a table related to itself needs a distinctly named foreign key
6. Data Integrity, Rules & Views
intg-four-levels-of-data-integrity— table, field, relationship, and business-rule integrity togetherintg-field-specifications— pin down every field's general, physical, and logical elementsintg-database-vs-application-rules— enforce structural rules in the schema; conditional/derived rules in the applicationintg-validation-tables-for-allowed-values— a lookup table beats a hardcoded value listintg-views-for-derived-and-restricted-data— data, aggregate, and validation views instead of extra stored fields
7. Antipatterns
anti-flat-file-design— the throw-everything-in-one-table structure and how to spot itanti-spreadsheet-as-database— a spreadsheet layout is not a relational schemaanti-rdbms-driven-design— don't let the product you know dictate the designanti-bend-rules-only-deliberately— the only two defensible reasons to break the rules, and how to document it
8. Terminology
term-data-versus-information— data is stored; information is data made meaningful for presentationterm-null-is-unknown-not-zero— a null is a missing/unknown value, not zero or blankterm-relational-vocabulary— key is logical, index is physical; a view is not a table
How to Use
Read a reference file when its decision comes up. Each rule names the wrong default it corrects, then shows the canonical way (with an incorrect/correct contrast only where the wrong way is a real trap).
- Section definitions — category structure
- Rule template — for adding new rules
- AGENTS.md — auto-built table of contents across all rules
Reference Files
| File | Description |
|---|---|
| references/_sections.md | Category definitions and ordering |
| assets/templates/_template.md | Template for new rules |
| metadata.json | Version and source references |