Should I create a Factless Fact Table or Denormalise the table

database-designdimensional-modeling

I am creating a data warehouse from a store database and I have a question regarding the design of my dimensions and facts.

In the store database a table exists for Person, Person_Address and Person_Address_Type. These are linked by another tabled name Entity_Address_ID which links the three tables by their primary keys to give details on what a person's address is and what type of address it is.

My question is, should I create a dimension for all three tables, and a fact-less fact table to link them together or should I de-normalise my dimensions and add to each dimension a foreign key for the address and address type they are linked too?

Here is a very quick UML of what the current database looks like to provide clarification
UML of Problem

Best Answer

Merge them. Sometimes it's worthwhile to have a separate Addresses table, but generally it's simplest to make those fields of a single Customers dimension. Because addresses are nearly unique, you're not going to save many records by hiving them out to their own table.

If your Customers table is really large, and addresses change relatively often then moving Addresses out as a Type-4 SCD could be a worthwhile optimization, but a small one.

If there's interest in analyzing customers independently of their specific sales (e.g., "20% of our top-100 customers are international"), you can absolutely add a NumCustomers dummy column to this Customers table to turn it into a factless fact table.