14 Replies Latest reply on Mar 4, 2010 11:16 AM by comment_1

    Design Recommendation

    dvassallo

      Title

      Design Recommendation

      Post

      I am working on a database for a restaurant to keep track of certificates that it receives from its vendors. The certificates contain different lines of business (insurance) that have policy start/end dates (typical policy is 1 year term). I need to develop a database to store and track the certificates on file for each vendor and run lists of certificate that have a line of business that expires within the next 30 days, and a list of those that have expired. I am having trouble conceiving of what my tables should be...other than the fact that I know that I will have a table for vendors with a primary key of vendorID....I don't know how to approach the certificate best so that I can achieve what I need. Again, the certificate comes from a vendor...it lists lines of insurance (General Liability, Auto, Workers Compensation, etc.) and each line of business for each certificate has a start/end date. If one line of insurance expires then the restaurant needs an entire new certificate from the vendor. I will also need to store the historical/expired certificates for archival purposes so I presume I will need something like an active/inactive attribute on my certificate table so they do not get included in my expired reports. Any ideas or suggestions are greatly appreciated. I am new to database design.  

        • 1. Re: Design Recommendation
          philmodjunk

          Seems like two fairly simple tables are required:

           

          Table1: Certificates

          VendorID (number)

          Certificate Description (Text)

          Start (date)

          End (date)

          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")

           

          Table2: Vendors

          VendorID (Serial number)

          VendorName (text)

          //Vendor contact fields

           

          You'd link your records by Vendors::VendorID = Certificates::VendorID

          • 2. Re: Design Recommendation
            comment_1

            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?

            • 3. Re: Design Recommendation
              dvassallo

              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.

              • 4. Re: Design Recommendation
                comment_1

                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.

                • 5. Re: Design Recommendation
                  dvassallo

                  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?

                   

                  Thanks again. 

                  • 6. Re: Design Recommendation
                    comment_1

                    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.



                    • 7. Re: Design Recommendation
                      dvassallo

                      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.

                      • 8. Re: Design Recommendation
                        dvassallo

                        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.

                        • 9. Re: Design Recommendation
                          philmodjunk

                          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.

                          • 10. Re: Design Recommendation
                            comment_1

                            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?

                            • 11. Re: Design Recommendation
                              dvassallo

                              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.

                              • 12. Re: Design Recommendation
                                comment_1

                                OK. Let me outline two possible solutions (there are more, but these seems to be the easiest to implement):

                                1.
                                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).


                                2.
                                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.









                                • 13. Re: Design Recommendation
                                  dvassallo

                                  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?

                                   

                                  Thanks again.

                                  • 14. Re: Design Recommendation
                                    comment_1

                                     


                                    dvass wrote:

                                    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.