1 Fact or 2, that is the question

[wonka]

Got a dilemma, scratching my head here over whether to make 1 or 2 fact tables. I keep going back and forth, so thought I'd go after your expert opinions.

Here are the basics...

Industry: Casino Gaming

Dimensions include CUSTOMER, GAME, CALENDAR, etc.

Topic of discussion is the "Gaming Activity" Fact table, which is split into 2 main categories: Slot machine play and Table Game play. Both have overlapping data: "Win/Loss", "Amount Played", "Average Bet", "Time Played" etc. but there are other fields that exist in only one group, such as "Credit Played" only applies to Table Games and "Handle Pulls" only applies to Slot Machine play.

So, do I build 2 Fact tables or 1? When I decide on one way, I quickly switch to the other option so I'm going in circles. I can make a case for both options.

Any suggestions? Thoughts? Comments? Recommendations?

Thanks in advance!

[ngalemmo]

I would go with 1. What is more important? Easily obtaining a full picture of a customer's action or saving disk space?

[BoxesAndLines]

If those are the only differences, I would go with one as well. The problem you will have (or users will have) is understanding which dimensions apply to which metrics. The dimensional data model offers no support here. If you have a BI tool then you should be able to manage these relationships. If not, you can implement views for each type to prevent folks from mixing dimensions that aren't applicable for a given metric.

[jgranden]

This is the super-type/sub-type design pattern.

three or more tables:
one to hold the facts that are common to across all types of gaming.
one to hold all the facts that are applicable to table games.
one to hold all the facts that are applicable to slots.
...
one to hold all the facts related to sports betting, etc.....


You duplicate the facts across the tables, so "Win/Loss", "Amount Played", "Average Bet", "Time Played" would be in all three tables.


If you had a very large number of disjoint metrics, multiple physical tables would make sense, otherwise 1 table plus some views.


Good "Luck"

[Sponsored content]