From ERD to tables: design without duplicates
lesson~12 minflashcardsquizgameup to 50 pts
Why not one big table
The first idea is always the same: one table, everything in every row. The order, the customer's name, their e-mail, the product name, the price. It works right up to the moment Anna changes her e-mail.
| What happens | Name of the problem |
|---|---|
| You change the e-mail in 3 rows out of 5 and the database contradicts itself | update anomaly |
| You cannot store a new product until somebody orders it | insertion anomaly |
| Deleting a customer's only order deletes the customer too | deletion anomaly |
The relational model solves all three the same way: every fact is stored once, in one place, and tables refer to each other through keys.