Data Modeling
ER modeling — entities, attributes, relationships, constraints, weak entities — and translating ER to relational tables.
Data modeling is the part of database work that happens before any code. Get the entities and relationships right and the SQL is straightforward; get them wrong and you'll spend years migrating around it. The ER model is the canonical vocabulary — entity sets, attributes, primary keys, relationship types, cardinality constraints — and every relational schema is some translation of an ER diagram.
For interview prep, practice on a small business domain (e.g., 'design the schema for a library / e-commerce site / ticketing system'). The pattern is the same: name the entities, the attributes, the keys, then the relationships with their cardinalities, then translate to tables. Five entities, two relationships, twenty minutes — anything more is usually scope creep.
Key concepts
- Entities are the nouns; relationships are the verbs; attributes are the adjectives
- Primary key choice is a contract — surrogate keys default to the right answer
- Cardinality constraints (1-1, 1-N, M-N) drive whether you need a junction table
- Weak entities exist; their identifier is composite and depends on the parent
- Translation rules: each entity → table; each M-N relationship → junction table; each 1-N relationship → FK on the many side
Reference template
// ER design walk-through
1. List the entities (concrete real-world things)
2. List attributes per entity (note primary key)
3. List relationships (degree, cardinality, participation)
4. Identify weak entities (no independent identity)
5. Translate to tables (apply the rules)
6. Add constraints (NOT NULL, UNIQUE, FK ON DELETE)Adapt to your problem; the structure is the load-bearing part.
Common pitfalls
- Putting business logic in keys — natural keys break when business rules change
- Premature denormalization — start in 3NF, denormalize only with a measurement
- Missing the M-N case — two FKs and forgetting the junction table is a classic
- Ignoring participation constraints —
ON DELETEsemantics are not optional
Related topics
Items (5)
- Data Models, Schemas, and Instances
Model = vocabulary. Schema = structure. Instance = data. The difference matters for migration, validation, and discussion.
Concept Foundational - Entity-Relationship Model — Entities, Attributes, Keys
Entities, attributes, primary keys; entity sets vs entity types; the smallest vocabulary to design a schema.
Concept Foundational - ER Relationships and Constraints
Relationship types, degree (binary / ternary), cardinality constraints, participation constraints, attributes on relationships.
Concept Intermediate - Weak Entities and Identifying Relationships
Entities whose identity depends on another entity, identifying keys, and where this pattern shows up in real schemas.
Concept Intermediate - Translating ER Diagrams to Relational Tables
The standard rules — each entity becomes a table, each relationship becomes a foreign key or junction table, weak entities collapse.
Building Block Intermediate