Help with data model design

Last Post 11 Sep 2008 01:50 PM by charbaugh. 0 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
charbaugh
New Member
New Member

--
11 Sep 2008 01:50 PM
I need help with a database design. Please give input if you can.

I work for a company that develops and sales insurance products (variable and fixed annuities). My company wholesales our products to insurance agents and insurance sales reps. We have two different distribution channels, Broker Dealer channel and NMO channel. BD (Broker Dealer) channel is very simple, we manage the BD's who have BD reps that work for them. So BD XYZ Securities, has rep John Smith, Jane Smith, and Joe Smith. The BD channel is very simple, Parent BD company, Child BD reps, one-to-many.

The NMO channel has a very complicated hierarchy. My company has agreements with NMO's and it's the NMO's job to recruit levels under them. I will layout an examples.

NMO can have multiple multi-level organisations underneath them. The organisations can be a company or an agent starting at the RMO level down. Most of the time anything after EMO are all agents. Think of multi-level marketing.

-NMO
--RMO_A1---RMO_B1
---EMO_A1-EMO_A2-EMO_A3, EMO_B1
----GA_A1-GA_A2,
----IGA_A1-IGA_B1, IGA_B2, IGA_B3
----SGA_B1, SGA_B2
----EGA_A1, EGA_A2

The commission percentage for the agent or organization is higher at the top of the hierarchy then the bottom.

Also, a person can be both a BD rep and a NMO agent or organization. So the "person" would have both hierarchies, one for the BD rep and one for the NMO agent.

I'm trying to design a model that can easily navigate from relationship to relationship without having duplicate BD reps and NMO agents.
You are not authorized to post a reply.

Acceptable Use Policy