Code
Design a database schema
Normalised tables, real constraints, seed data, and the queries to prove it works.
Fill it in
The entities and how they relate, in your own words.
Which dialect.
Realistic rows, so the queries return something.
Analytical queries that exercise the design.
Your prompt
Design a Postgres schema for this. [your what it stores] Normalise first, then denormalise deliberately if a query demands it, and say which you did and why. Write the constraints: primary keys, foreign keys with the right ON DELETE, NOT NULL, uniques and CHECKs. A constraint you leave out is a bug you will find in production, in data, months later. Index for the queries you are actually going to run, not for every column. Generate realistic seed data. Realistic means the distribution is lumpy: most customers order once, a few order constantly, and some rows are missing the optional fields. Write 10 analytical queries that exercise the design, run them against the seed data, and show the output. A schema nobody has queried is a guess.
Use Design a database schemaOpens with everything above already filled in.
Why this works
A constraint you leave out is a bug you find in production, in data, months later. This writes the keys, the cascades, the uniques and the CHECKs, then generates lumpy realistic seed data and actually runs analytical queries against it, because a schema nobody has queried is a guess.
Questions
- Which databases?
- Postgres, MySQL or SQLite.