Java Schema design
Normalisation, keys and constraints — and the judgement about when to denormalise on purpose rather than by accident.
4 concepts · 10 interview questions
What this topic covers
Every concept in schema design, and the questions each one gets asked as. Where a question links, it has a full write-up.
Normalisation
Organising columns so each fact is stored once, up to third normal form for most practical purposes.
- What are the normal forms, and where do you stop in practice?
- When would you denormalise on purpose?
Keys
A primary key identifies a row; a foreign key constrains a reference; a natural key carries meaning and a surrogate does not.
- Natural key or surrogate key?
- Should you use UUIDs as primary keys?
- What does a foreign key actually enforce, and what does it cost?
Constraints
NOT NULL, UNIQUE, CHECK and foreign keys push invariants into the database, where they hold regardless of which application writes.
- Should validation live in the application or the database?
- What happens to a UNIQUE constraint with NULL values?
Schema migrations
Versioned, ordered changes applied automatically, with backwards compatibility during the window when both versions are running.
- How do you change a schema with zero downtime?
- Flyway or Liquibase, and why version migrations at all?
- Why should a migration never be edited after it has run?