Logical Data Model - Clients

View previous topic View next topic Go down

Logical Data Model - Clients

Post  tim_goodsell on Tue Oct 18, 2011 7:24 pm

Hi

I am trying to create a logical data model for the following scenario

A client can over time be one or more types but at one time period only one (Member or Beneficiary )
A client is a of type "Member" if the client has one or more current "Member Accounts"

The primary key of a Client is Client ID
The Primary Key of the member account is Member Id and Fund

What should my data logical model look like

Regards

Tim




tim_goodsell

Posts : 49
Join date : 2010-09-21

View user profile

Back to top Go down

Re: Logical Data Model - Clients

Post  ngalemmo on Tue Oct 18, 2011 8:49 pm

I would create a single client dimension and a bridge. The bridge would contain the member ID, member accout ID, and relationship type, with an effective and expiration date value, and a current flag. The member ID would reference the 'parent' client and the member account ID would reference the children client IDs.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Logical Data Model - Clients

Post  BoxesAndLines on Wed Oct 19, 2011 9:13 am

A logical model or a dimensional model? There's really no such thing as a logical dimensional model, even though Kimball Group folks occasionally use the term. Here's what I came up with based on your post.

avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

CLIENT - MEMBERSHIPS LOGICAL DATA MODEL

Post  tim_goodsell on Thu Oct 20, 2011 6:16 pm

Thanks for feedback chaps

The requirements have firmed up a bit

At a moment in time a CLIENT can be either a "Member" and a "Beneficiary", just a "Member", just a "Beneficiary", or "Neither"

A CLIENT is a of type "Member" if he has one or more active "Member Accounts" during the time period. A CLIENT can therefore be a "Member" many times (just over different non-overlapping time periods). I suppose you can say a CLIENT has many MEMBERSHIPS, each membership is over a different time period

A "Member Account" is unique to a MEMBERSHIP


The primary keys for the entities are:

CLIENT ( CLIENT ID)
MEMBERSHIP (CLIENT ID, START DATE )
MEMBER ACCOUNT ( PK: FUND ID, MEMBER ID, FK : CLIENT ID)

The requiremets for "Beneficiary" are still to be established.

Can you please suggest a logical model for the above

Regards

Tim






tim_goodsell

Posts : 49
Join date : 2010-09-21

View user profile

Back to top Go down

Re: Logical Data Model - Clients

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