Boolean flag in fact table

All we need is an easy explanation of the problem, so here it is.

Imagine we have received the results of a health survey on daily consumption habits of 3 different items, like the following:

Id Date Age Country CigarettesPerDay CoffeesPerDay BeersPerDay
1 2021-12-31 35 US 0 3 0
2 2021-12-31 22 US 5 5 1
3 2021-12-31 53 US 3 4 0
11276 2021-12-31 44 France 3 4 0

I want to model this in a star schema model. In the fact table, I create foreign key relationships to date and item dimensions as well as a demographics dimension with country and age. I then sum up the number of respondents pr. demograhic group. If the number of respondents is above 100, I mark the group as being representative of the population. Finally I calculate the total and average consumption for each group.

DateId ItemId DemographicId NumberOfRespondents IsRepresentative TotalConsumption AverageConsumption
20211231 1 1 70 No 280 4
20211231 1 2 150 Yes 750 5
20211231 1 3 220 Yes 660 3
20211231 3 1000 1 No 0 0

For instance, there was 70 respondents from demographic 1 (e.g. country = US, age = 18). They have on average consumed 4 of item 1 (e.g. cigarettes).

Generally we should strive to hold only facts and foreign keys in the fact table. However I personally don’t think that a seperate dimension for the boolean flag provides any value. Can this flag be considered a generate dimension, or is it considered bad design to have it in the fact table?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

It’s not really an answer, but in lack of good ideas, I decided to put the flag in a dimension table after all.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply