Relational Modeling problem

Thu, 28 Mar 2013 20:10:08 GMT

I'm sure a similar question has been asked before, but I'm uncertain how to phrase the search to find an answer..

Here's my problem..

My database is relational. It contains four basic entities: Account, Customer, Officer, Relationship.

A Customer may have multiple Accounts, and an Account may have multiple Customers (primary and secondary account holders, authorized users, etc). For this reason, a mapping table is required (CustomerAccountMap).

A Relationship is a group of Customers. A Customer may belong to more than one Relationship. So there is another mapping table here (CustomerRelationshipMap).

Finally, for the last bit of complication, each entity has an officer assigned to it as the person responsible.

Some of the common questions that require answers are:

-How many Customers does an Officer have?

-How many Relationships is an Officer responsible for?

-What is an officers Accounts per Relationship ratio?

-What is a Customer's total balance for all accounts that they're primary?

-What's the total balance for a Relationship, including all customers and their accounts?

Here's a visual representation of the relational model:

My question is how can I model this in QlikView to answer those questions?

I've been able to model it such that I can answer questions about Accounts and Customers, or Accounts and Relationships. However if I try to bring Officers in I can't figure out how to connect it to any more than a single entity without the model destabilizing.

Is there a way to answer all of these questions in a single model?

Thanks in advance,

Alex

topic Relational Modeling problem in QlikView

Relational Modeling problem