Logical Data Model - Clients

[tim_goodsell]

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

[ngalemmo]

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.

[BoxesAndLines]

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.

[tim_goodsell]

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

[Sponsored content]