Entity Relationship Diagram (ERD) defines the relationship between Fact and Dimension Tables. In the EDR, FK (Foreign Key) and PK (Primary Key) is highlighted.

Columns in the Fact Table which is the heart our data model are:

The relationship between tables is like this. This is a relationship and differs slightly from JOIN.

Cardinality: For most of Dimension Tables, Cardinality is Many for Fact Table to One for Dimension Table. For example, in dimCustomer, we have one row per customer ID while, each customer may have ordered multiple times (multiple rows) in factOrder table.

Referential Integrity: In ERD, it is said “All records will match unless otherwise stated”. Therefore select all records match for both Fact and Dimension Tables. This means all rows in FactOrder has at least a match in dimCustomer and vice versa. This is true for all dimension tables with the exception of dimPromo. In the relationship between FactOrders and dimPromo tables, there are order without promotion (null PromoId). It means not all rows in FactOrders have a match in dimPromo table. However, all rows in dimPromo have a match in FactOrders. That is why there is circle in ERD next to FactOrders table.

For all relationships except dimPromo: Many (all match)-to-One (all match)

For dimPromo: Many (some match) – to – One (all match)

For example, for dimCustomer:

However, for dimPromo, Referential Integrity differs a little bit: