Clients with different attributes that are not all the same

View previous topic View next topic Go down

Clients with different attributes that are not all the same

Post  Brandon on Wed Oct 26, 2011 6:34 pm

I have been given the project of building a master client database. I work in a financial environment where we client/product/account information. For some types of client they have specific information related just to them, unique identifier, name, but they differ in the other information that relates to them. For example, Client A may have a 4 character unique identifer with no product information, Client B may have a 9 digit unique identifier with the possibilty of product information based upon a platform. Finally Client C has a 4 character unique identifier with production information. The product information is based upon a System/Prin/Agent (4 digits each) Each client represents a different platform, of which a client can be on multiple platforms.

What would be the best method for this situation? One dimension table could have lots of null values for all of this information. The clients do have some attributes in common such as name, address, phone, etc. They just have different ways of identifying themselves. Should I split the clients out into their own client table with a common key to a main client table. Dimension with a snowflake?
.

Brandon

Posts : 3
Join date : 2011-10-26

View user profile

Back to top Go down

Re: Clients with different attributes that are not all the same

Post  ngalemmo on Wed Oct 26, 2011 9:13 pm

If you are trying to do this in a dimensional model, I would do the following:

Represent a client with a primary client table with the common attributes and secondary type specific tables for attributes coming from particular client systems... in ER terms: a sub-type cluster. All tables would use the same client PK and there would only be rows for clients that are of that type.

Note that this is NOT a snowflake. The main and sub-type tables have the same PK. The relationship is not between them but rather between the fact and the table. If you have a query that filters on an attribute unique to a particular sub-type, it will only look at measures for dimensions of that sub-type (unless you do an outer join from the fact to the sub-type table).

The client/product relationship could be implemented as a bridge between client associated with the fact and the product dimension. Or if this information has wider user, implement as its own fact table.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Clients with different attributes that are not all the same

Post  Brandon on Thu Oct 27, 2011 11:07 am

Thank you. I may still have some questions if that is okay.

Brandon

Posts : 3
Join date : 2011-10-26

View user profile

Back to top Go down

Re: Clients with different attributes that are not all the same

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum