For many people (mostly myself) this topic can cause a bit of confusion, understanding the differences between the tables, as well as understanding how links then form between each of them. This blog aims to clarify both of these points.
What are they?
Fact Tables store quantitative data, measures that are numeric. For example, sales or number of orders. They include foreign keys that are the basis of linking to dimension tables. Normally this is used as the basis of analysis purposes and decision making. For example, the "Book Info" table acts as the central table in the Bookshop data, where all other dimension tables come off from.
Dimension Tables provide more qualitative data, that give descriptive context for the facts. For example, product names and customer details. The main task is to store the information about a business and its process. For example, the "Ratings" table holds the descriptive context for the ratings for the Books, linked on the field BookID to the "Book Info" table.
Separating data into fact and dimension tables is helpful when aiming to build efficient data models. This structure helps to reduce data redundancy, improve query performance, and makes it easier for people to understand and analyse the data.