Seems like two fairly simple tables are required:
Certificate Description (Text)
Description (text )
Status (can be a number field with 1 or 0 to mark active/inactive or a text field that stores the word "active" or "inactive")
VendorID (Serial number)
//Vendor contact fields
You'd link your records by Vendors::VendorID = Certificates::VendorID
The basic relationships look like a rather simple arrangement of three tables:
Vendors -< Certificates -< Policies
The complication here will be how to get, from the point-of-view of Vendors, the earliest policy expiry date of the latest certificate.
Question: do all certificates have the same types of policy?
Thanks to both of you for responding...'PhiModJunk': this was my initial design concept, two tables as you represented with the relationship as you defined. But then I started to think in greater detail about dealing with the fact that the certificates that come in for each vendor will need to be organized in such a way that the user can see that a "current" certificate is on file and see the history for the previous "policy terms" - I don't know if this changes the two table design or not ~ I guess that is my hang up. 'comment' brings up a valid point that has also concerned me in my design and that is that a certificate can have any variation of each of these policy types but only ONE of each....so a certificate can show coverage for General Liability and Auto or it can show coverage for General Liability and Auto and Workers Comp. The other annoyance is that the policies dont have to have the same start date and end date so GL can be 1/1/2010-2011 and WC can be 3/15/2010-2011 but they might be on the same certificate....then of course I need to evaluate each certificate to see if any ONE line is expired and flag that entire certificate as delinquent and notify the user that he/she needs to request a new one.
Thank you both so much for your thoughtful responses. I appreciate the help greatly! I am a systems engineer and I have some programming experience but there is something about relational databases that makes me spend a lot of time thinking about all the different scenarios and ways I can imagine constructing my tables.
How do you deal with the following scenario:
A vendor has a certificate with three policies, say General, Auto and Workers. The Auto policy has expired. The vendor supplies a new certificate with two policies: General and Workers.
Good point...if any one policy on the certificate expires - an entirely new certificate would be issued showing all policies. The only difference is that the expired policy would have the renewal date on it. Also the visual might help, here is a sample of a certificate: Sample Certificate
From what I understand it is important to avoid many to many relationships and when one occurs it is important to put a table in between to create a one to many relationship. I think I see where you are going with your question because by what I answered above there would be a many to many relationship between certificates and policies correct? One certificate has many policies but the same policy can appear on more than one certificate. What could be inserted between these two tables to resolve this? Is my thought process even in the right ballpark?
If there is a many-to-many relationship here, it is between Certificates and PolicyTypes - and it would be resolved by Policies acting as a join table:
Vendors -< Certificates -< Policies >- PolicyTypes
But you haven't really answered my question - which is not a Filemaker question, but a business one:
You say that "a certificate can have any variation of each of these policy types". What determines the types that a specific vendor is required to provide? Going by your description so far, you only check that all types of policies are still valid - but it seems there also need to be a record of the requirements, and a mechanism to check that a certificate meets them.
You say that "a certificate can have any variation of each of these policy types". Yes
What determines the types that a specific vendor is required to provide? Again, correct. There is "minimum coverage" which would involve a mechanism to check the certificates on record against a sort of "master certificate" to ensure that they have AT LEAST certain policy types with certain limits of insurance. Sorry I didn't explain the entire scope of the problem.
Going by your description so far, you only check that all types of policies are still valid - but it seems there also need to be a record of the requirements, and a mechanism to check that a certificate meets them. Yes, there will be a need to enter the minimum insurance requirements and have a mechanism in place to check that the certificates that have been collected meet these requirements. The requirements are that the certificate contains certain policy types and insurance limits. The requirements do not change - they remain constant and all vendors are subject to the same minimums.
For example: The minimum coverage that is acceptable might state that the vendor must have AT LEAST a General Liability policy with limits of 1,000,000/1,000,000,000 and Workers Compensation with a limit of 10,000,000,000.
Thanks PhilModJunk...would you mind taking a look at the thread again and checking out the details I have provided in my discussion with user 'Comment' - your input is greatly appreciated. Thank you again.
Seems like you and Comment are on the right track. When I read your initial post, I was uncertain if a Certificate listed more than one policy or not. My initial post assumed one policy per certificate. I see this is not the case so please disregard my original post.
Pardon my harping on this, but I need to be sure I understand this correctly:
The REQUIRED types of policy are the same for all vendors - but some vendors provide certificates with EXTRA policies? If so, shouldn't you be checking for the expiry of a required policy only? Why would you even bother to track those extra policies?
No I totally understand your questioning and I apologize for not being more clear.
You are correct, the required types of policies and coverage limits are the same for all vendors. The user does not care about the extranous information on the certificate. Even if the vendor is "lazy" and sends over a certificate that just shows all of their coverage...the user would ignore the other policies and just be concerned with entering information on the policies that are part of the insurance requirements. There is no need at all to record the extraneous data.
Again, I am sorry for not being more clear. I am working through the problem myself trying to account for any exceptions...but what you say in your last post is correct as I confirm above. Thank you again...you're help is BEYOND appreciated.
OK. Let me outline two possible solutions (there are more, but these seems to be the easiest to implement):
In the Certificates table, define a calculation field =
Min ( Policies::ExpiryDate )
Search this field to find certificates that have NOT expired, then GTRR [Match found set] to the Vendors table and do Show Omitted Only. These will be the vendors that do not a have a current certificate (you may need to constrain the found set to eliminate inactive vendors).
In the Vendors table, define a calculation field cLastCertificateID =
Last ( Certificates::CertificateID )
Define a relationship (using a new occurrence of the Policies table):
Vendors::cLastCertificateID = Policies 2::CertificateID
Now you can search the related field Policies 2::ExpiryDate.
Thank you again for your advice. I believe I understand and will work through it. I just have one final question...to revisit your point about the contruction of the relationships:
Vendors -< Certificates -< Policies >- PolicyTypes
Based on our discussion is this still the construction that you recommend? If so, what are the attributes of PolicyTypes...is it simply the Policy Types? And I presume I should have a primary key of PolicyTypeID which is a foreign key in the policies table?
is this still the construction that you recommend?
Yes, with PolicyTypes being optional - I believe a simple value list could be sufficient in this case.