Mysql – Type of Foreign Keys for a Dimensional Model Fact Table

dimensional-modelingfacttableMySQLrdbms

I've been reading about dimensional models, and in particular fact tables, but I haven't really seen one in action (i.e. the actual content), just diagrams of the table structures and column names

From my reading it sounds like you only put the primary (i.e. primary auto incremented) keys from the dimension tables into the fact table, and not any of the actual dimensional information in the table

Is this accurate? The example I have in my mind looks like this:

+---------+------------+-------------------+
| date_fk | product_fk | units_sold (fact) |
+---------+------------+-------------------+
|  3      | 12         |  1154             |
|  3      | 50         |  484              |
+---------+------------+-------------------+

Where date_fk is the primary auto incremented key for the dimDate dimension table, product_fk is the primary auto incremented key for the dimProduct table, and units_sold is the actual fact, the number of units sold that day for that product

Please confirm that this is the correct method; my dimension tables also uses numeric natural keys so it's tempting to use those instead of the adjacent primary keys to avoid having to do JOINs for every query, however I am committed to using dimensional modelling so I need to do it right

Best Answer

Yes, that is the correct method.