desining customer Dimesnion

Hi All,

I design customer dimensions, but in our concept we have two types of customer the customer as the owner and the customer as one member in customer group which we call it contract,and some times the contract is the owner One-To-one .
From this concept I have designed two dimension one called Dim_Customer and the other called Dim_Contract.
Each dimension have its separated Name and other information but they have a link with Customer_ID .Also each Dimension will be used in different fact for Example payment can not be don on Contract Level so customer dimension will be used ,and calls are on Contract level so Contract dimension will be used .

I have designed the DIM_Contract as :

1- DIM_Contract
Contract_KEY
Contrcat_ID
Customer_id
Customer_Key
FIRST_NAME
MIDDLE_NAME
LAST_NAME
BIRTHDATE
ZIP
COUNTRY_NAME
Occupation
CITY_NAME
First_Ceil_ID
Last_Ceil_ID
STATUS
..
..
.. etc

2-DIM_Customer
Customer_KEY
Customer_id
STATUS
Price_Group
Credit_Type_Name
Dealer
FIRST_NAME
MIDDLE_NAME
LAST_NAME
GENDER
BIRTHDATE
..
..
.. etc

Some attributes in both dimensions can be used and linked to other tables like STATUS, but I did not chose Snow-Flak design I chose the Star Schema design .
What do you think what is the best in this case to use Snow-Flak or start Schema where these two dimensions will handle up to 6 millions records?
Where I will create DIM_Status to be used in different facts.
Something else, based on the above explanation what do you think does this the best to design or you have a notes.

Regards,

If the STATUS column is something that would change quite often, and you would want to maintain a history of your customer records, then probably having it within a Type 2 SCD -Customer would make the dimension table grow huge. you could then think of creatinga mini-dimension out of it.
Also, I would have thought the CUSTOMER dimension would have been a more conformed one here. BAsically, you might need a bridge table to handle the mutivalued customer dimension here.

Thank you dwbi_rb.

For status within DIM_Contract the status will change rapidly,and this is way we have to use mini-dimension .For DIM_customer the changes will be rarely,and I am thinking not to use mini-dimension.
I am also thinking to remain the status of Dim_Contract as Curr_Status as type 1 ,what do you think here .
About the conformed, yes it is a conformed but as I have explained that some times the realation between contract and customer is One-customer can have many contract ,and the name and other attributes can be different .

Is their any suggested solution for conformed dimension .

Rgrds,

Mohammed

As I mentioned that it is a multi-valued dimension, one of the ways sugested by Kimball University is creating a bridge table -

CUST
------------
CUST_SK
CNAME
CDETAILS


CONTRACT
--------------
COT_SK
COT_NAME
COT_DESC


CUST_COT_BRIDGE
--------------------
COT_SK
CUST_SK

Also deciding on whether SCD should be handled using Type 1/2/3 is more based on the requirement.
If there is no need to maintain the history, then Type 1 should be OK. However, if you need to maintain a history,
Type 2 could come in play or Type 3, or even a hybrid solution. This would be a design prerogative based on your data
structure.