Dimensional Modelling

Question

Am trying to build a dimension for Customer Preferences sourcing from Multiple tables.

Example of Source Tables are as below:

I already got Dim Customer built and now i have to design Customer Preference Dimension. The new dimension will be SCD1; always having customers latest preferences. But a given customer can have multiple combinations.

Now my question : Is it good to design dimension for each Preference table are should i get all the Preference attributes of a customer into one single Dimension Table as below:

Columns highlighted will make a unique preference of a customer. *Customer can have multiple preferences.

What if in future business introduces some more preferences and want to include in dimension. Then I have to bring those attributes in above table and also the keys which make unique.

Is it better to do individual dimensions per preference or merge all into one big dimension as above.

Suggestions please.

Edit:

Further to my reading I understand that I have to go with designing a bridge table between my Customer Dim and other Customer Preference Dimensions.

What am I planning is to create CustomerPreference Dimension for each preference type with all combinations. And map these in a bridge table where can have many-to-many customer-preference relations.

Example as below

Is this the right way to do or there any best practices.

[or] is this the right way?

Edit: 27-03-2013

As per suggestions of Pondlife am going with Snowflake approach as below:

Answer 1

You haven't said anything about your fact table, which may be a key factor in a decision. If the preferences apply only to the customers and are completely unrelated to the fact, then you could use a snowflake model to add a customer preferences table linked only to the customer dimension.

But you mentioned "individual dimensions per preference", which suggests that the preferences might possibly be related directly to the fact. In that case, if you have a limited number of possible preferences per fact (this isn't really clear from the information you gave) a mini-dimension that contains all possible combinations of the preferences might be a better option.

If this isn't helpful, I suggest that you clarify the following points:

1. What the fact table represents
2. If the preferences are linked to the fact or to the customer
3. If one customer (or fact?) has up to 3 preferences (marketing, platform and genre), or an undefined number (e.g. 3 marketing preferences, 10 platform preferences, 2 genre preferences)

Answer 2

You've got the dreaded "Entity-Attribute-Value" model for customer preferences, since developers think that it makes their lives "easier" by not having to make a DB change when a new preference is added. Of course it makes your life much harder.

I am working in a similar situation currently, and have found the easiest way to model customer preferences is with a attribute bridge table as you have found out yourself, especially when it's truly many to many.

The real question that needs to be asked is what kind of analysis are going to be done on these preferences? If you're just going to be filtering customers based on preference, then going to a fact with that set of customers, the many-many bridge should do the trick.