One Big Table vs Star Schema
People think one big table is just a messy star schema. Both hold the same facts and details. A star schema keeps each detail table apart and joins them when you ask. One big table does the joins once, ahead of time, and keeps the result. The star is easier to change. The big table is easier to query.
One Big Table (OBT)
One wide table with all the details on each row. Queries need no joins.
One big table is a warehouse design where the facts and all their details sit in one wide table. The joins to customer, product and date tables are done once, when the table is built. Queries then read one table with no joins. Names and labels repeat on every row, so it takes more space. Columnar storage keeps that cost low.
Star Schema
One fact table in the middle. Each dimension table joins straight to it.
A star schema puts one fact table in the middle and dimension tables around it. Each dimension joins straight to the fact table with one key. Dimensions are not split further, so each one is a single wide table. Queries need only one join per dimension. Drawn out, it looks like a star.
In one line
- One Big Table (OBT)
- One wide table with all the details on each row. Queries need no joins.
- Star Schema
- One fact table in the middle. Each dimension table joins straight to it.