Data modelling for analysis
In the SQL track you designed a database for daily operations: many normalised tables so nothing repeats. For analysis we design differently, in a simpler shape that makes reports fast: the star schema:
| Fact table | In the centre. Each row is a measurable event: a sale, a shipment, a payment. It holds numbers (quantity, amount) plus keys to the dimensions. Very long: millions of rows. |
| Dimension tables | Around it. They describe who, what, where and when: customer, product, branch, date. They hold text we filter and group by. Relatively short. |
| Grain | What does one fact row represent? “One invoice line” or “one day's total per branch”? The first decision in any model. |
The date table is a crucial dimension: one row per day, with month, quarter, year, weekday and whether it's in Ramadan. Then “this Ramadan versus last” is one click away.
Watch outSame idea as the ERDs you learned: dimensions link to facts one-to-many (one product, many sales). You can draw a star schema in ERD Studio.