Help with Relationship Diagram

[biiiink]

Hello

I'm pretty new to Filemaker.  I'm using FM 13 Advanced.  I've taken 2 courses on filemaker on Lynda.com.  I feel that I've got a good grasp on the application, but I'm certainly still new, and still learning

 

I am designing a database for my group insurance business.

 

I'm working on the Relationship Diagram, and I think I have most of it sorted out, but I've run into one problem.

 

I'll start by describing how the DB will work.

 

Each one of our Clients will have a minimum of one Policy.  Each Policy will be through an Insurance Carrier.  Each Policy will consist of the lines of benefits that the Client has chosen (ex. Life Insurance, Health, Dental, etc.)  Also each Policy will have a minimum of one Class.  Roughly 80% of all policies will only have one class. 18% will have 2-3 and 2% will have 4+.  Each Policy will have rates associated with them and a date that the policy renews.  Every time the Policy renews, the rates are likely to change.  99% of the time, the rates will be the same for every class in the policy, however, it is possible for each class to have its own rates.  We need the database to track the rate changes in the policy every year.  Also, the Clients are able to change the lines of benefits their Policy covers at any time (ex. changing the amount of coverage, adding new lines of benefits)

 

We also want the ability to take notes on our Clients and their policies.

I've attached A diagram of what I have so far.  The issues Im having is with the Policies/Renewals/Classes relationship.

 

The biggest problem i'm having is where to store the rates, so that they can be tracked each year, and can also be different for each class.

 

Any help/advice/suggestions would be greatly appreciated.

[GisMo]

It looks like you're off to the right start. Let's break it up into smaller pieces.

 

You have you Clients and Their Policies.

 

This is a One to Many Relatioship: Client->Policies

 

You have a Policy with Benefits. Again, One to Many Policy->Benefits

 

Also, you have  Policy and Classes. Again, One to Many. Policy->Classes

 

So in understanding your question of tracking rate changes, I think you could create an extra rate table which you connect to a Policy (Policy->Rates /One to Many). When you create the policy and lock in a rate, it get's stored in related table. Next year upon renewal say that the policy rate has increased, you would create a new record in the Rate table with the current date and the updated rate. Now, you will have a log of rate changes for any given policy. For that matter, anytime the policy is modified, you can adjust the rate and it will be stored in the rate table. You could also bring a long a note field with the rate to document why the rate was changed. eg: Renewal, Increased Coverage, etc..

 

I'm not sure why you have a renewals table. What does that do?

 

Another tip that I like to use for relationship graphs is work from Left to Right as best you can. For example, Put you Client table all the way to the left. From a Client, it's children are Notes, and Policies. Those would align vertically to the right of the client table. From There, you can put Benefits, Class, and Carrier tables connected to Policy in a 3rd Column to the right. I like to build relationships like these for each context that you may have. So you would have a Client Context and Maybe a Policy Context to start off. It also makes working the system and finding related tables easier.

 

Good luck!

[Chuck]

I think additional information is needed to help much. You say that a Policy will have at least one Class, and Classes can have different rates. You also say that the Policies have Renewals that these Renewals can be associated with a changes in the rate. So my question about this business logic, regardless of the database structure, is, exactly how is the rate determined? It can't be just by the associated Class since there could be more than one and the rates could differ for them. Is it by the latest Class? The highest rate in the linked Classes?

 

My guess would be that the rate field should be in the Classes table and that there's some way to specify which Class the rate should be taken from, perhaps from the Renewal record.

[biiiink]

Thanks for your replies

 

 

 

You have a Policy with Benefits. Again, One to Many Policy->Benefits

 

I currently have all of the possible lines of benefits as fields in the Policy table.  I believe that the Policy-<Benefits relationship is a many to many relationship.  One policy can have multiple benefits, and one benefit can be in multiple policies

 

 

 

I'm not sure why you have a renewals table. What does that do?

 

The Renewals table is basically what you were describing the Rates table to be

 

I'll give an example to try an illustrate where Im getting confused.  

 

Client 1 has a new policy in 2013 with 2 classes (Class A, and Class B. and each Class has a their own Health rate. (class A = $10/month.  Class B = $15/month)

In 2014 when their policy renews, Class A's rate is now $15/month and Class B's rate is now $20/month.

 

I need to be able to keep track of what each Class's rate was in 2013 and 2014.  My thinking is that if the rates are stored in the Classes Table, I'd need a new record for each class every time the policy renews, yet the only thing that is (likely) changing is the rates.  Currently, by my count, there will be roughly 80 fields (not including the rates fields) in the Classes table.  This seems like a lot of redundant data to me

...So my question about this business logic, regardless of the database structure, is, exactly how is the rate determined?

 

The rate is determined by the Carrier.  Rates are going to be manually entered into the database every year at renewal.