If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Insurance Certificates Database
Things have slowed down in my department, so I'm back to working on my
Contracts Administration database. When I left off, I was mired in designing tables for insurance certificates, unable to decide how far to go with normalizing. Under-normalizing results in large spreadsheet-like tables, which I am reliably informed are anathema to Access. On the other hand, I believe it is possible to over-normalize almost anything until the data is so scattered into many interrelated tables that performance is compromised, not to mention the unguessed sorrows to be had in writing VBA procedures under such conditions. I believe the path forward must lie more in understanding our business needs, what we want our application to be capable of doing, rather than slavishly following the mantra of Normalize, Normalize, Normalize. I have searched this site without much success, however, does anyone out there have any experience designing an ADB for tracking insurance certificates for compliance with subcontract requirements? Thanks, Pew |
#2
|
|||
|
|||
Insurance Certificates Database
If you'll provide a description of your table structure and relationships,
folks here may be able to offer more specific suggestions. .... and even if someone has a DB for "tracking insurance certificates for compliance ...", you'll still need to deconstruct what they've done to make sure their situation and yours are reasonably matched! Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "oldblindpew" wrote in message ... Things have slowed down in my department, so I'm back to working on my Contracts Administration database. When I left off, I was mired in designing tables for insurance certificates, unable to decide how far to go with normalizing. Under-normalizing results in large spreadsheet-like tables, which I am reliably informed are anathema to Access. On the other hand, I believe it is possible to over-normalize almost anything until the data is so scattered into many interrelated tables that performance is compromised, not to mention the unguessed sorrows to be had in writing VBA procedures under such conditions. I believe the path forward must lie more in understanding our business needs, what we want our application to be capable of doing, rather than slavishly following the mantra of Normalize, Normalize, Normalize. I have searched this site without much success, however, does anyone out there have any experience designing an ADB for tracking insurance certificates for compliance with subcontract requirements? Thanks, Pew |
#3
|
|||
|
|||
Insurance Certificates Database
Thanks for reply.
Insurance certificates generally appear as a fairly standardized form, known as the Acord form. To the user, this certificate looks like one entity, with all its fields belonging in one table, but I have been warned over and over that Access cannot handle this kind of approach. (I still don't understand why it is better to store a reference to a table containing a value, rather than just storing the value itself. It seems we are using three fields and two tables to do what could be done by one field in one table. And if the same value appears in different records, so what? Computers are supposed to have plenty of muscle for this sort of thing). Anyway, each Firm for which we require insurance will have zero, one or more Certificates, and each Certificate describes one or more Policies. Therefore we need tables for Firms, Certificates, and Policies, with one-to-many relationships. FIRMS PK IdFirm Firm fields... CERTS PK IdCert FK IdFirm Cert fields... POLICIES PK IdPolicy FK IdCert Policy fields... The Cert fields and Policy fields seem to fall into two categories. For lack of better terms I will refer to them as the Definite and the Indefinite. Definite Cert Fields: FK ProducerCode (Id for the insurance agency that produces the certifcate) Cert Date (date certificate was produced or printed) FK CertHolderCode (Id for the firm holding the certificate, which should be us) Indefinite Cert Fields: NOCDays (the number of days Notice of Cancellation) NOCChange (do we also receive Notice of Material Change in coverage?) NOCStrikeEndeav (are the words "Endeavor to" stricken from the NOC?) NOCStrikeFailure (is the Failure clause stricken from the NOC?) Future Fields... (unknown) The Definite Cert fields unquestionably go in the Cert table, but the Indefinite ones seem more subject to both repeating values, and to growth or change in the list of fields, and therefore may belong in a separate many-to-many table. On the other hand, the Acord form is pretty stable, and in the unlikely event of change, how bad would it be to just add more fields to the Certs table? Another question right here. Notice the Certs table has foreign keys for three different firms, namely the Insured Firm, the Certificate Producer, and the Certificate Holder. Right now, I have all firms, regardless of type, in a single table. How can I have multiple foreign keys back to one common table? Won't this confuse the daylights out of Access? I notice in the Northwind database there are separate tables for Suppliers and Customers. I thought I was doing right to put all my firms in one table. Was this a mistake? Definite Policy Fields: FK InsurerCode (Id for the insurance firm issuing the policy) FK PolicyTypeCode (Id for Policy Type, ie GL, Auto, Excess, Workers Comp, etc) Policy Number PolicyDateEffective PolicyDateExpires Indefinite Policy Fields: Type... Basis... Scope... Limits... Endorsements... As was the case with Certs information, Indefinite Policy Fields are subject to repeating values and to variable and indeterminate field lists. Different types of policies have different lists of fields, so there has to be some way to prevent inappropriate associations. For example, an Additional Insured Endorsement might apply to a General Liability policy, but not to an Excess Liability Policy. At this point, all our fields seem fixed, but who knows what changes the future may bring? Again the question is whether to take a chance a cram all presently known fields into the Policies table, or set up a many-to-many relationship between the Policies table and a PolicyDetails table. Another, less normalized, approach would be to create separate tables for each type of policy, and just put the appropriate fields in each table. This would solve the problem of inappropriate associations between Policies and Policy Details, but it would be less normalized and less flexible. For example if the need arose to begin requiring a new type of policy for some or all of our subcontractors, this would mean creating an entirely new table. This why I asked about others with experience in this same area, because the "right" structure depends on what you plan on doing with the data. I wanted to compare notes with someone else to see how they approached the task. Please note that capturing the information from the Acord form is barely one third of the task. We also have to specify our insurance requirements in detail for each subcontractor. Most insurance requirements will be the same from one subcontract to the next, but any of them may differ, so each subcontract must have its own set of required values. The subcontractor's insurance certificates are then checked against those required values. If the certificate is non-compliant, we want to report precisely which parameters are at fault. Thanks, Pew "Jeff Boyce" wrote: If you'll provide a description of your table structure and relationships, folks here may be able to offer more specific suggestions. .... and even if someone has a DB for "tracking insurance certificates for compliance ...", you'll still need to deconstruct what they've done to make sure their situation and yours are reasonably matched! Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "oldblindpew" wrote in message ... Things have slowed down in my department, so I'm back to working on my Contracts Administration database. When I left off, I was mired in designing tables for insurance certificates, unable to decide how far to go with normalizing. Under-normalizing results in large spreadsheet-like tables, which I am reliably informed are anathema to Access. On the other hand, I believe it is possible to over-normalize almost anything until the data is so scattered into many interrelated tables that performance is compromised, not to mention the unguessed sorrows to be had in writing VBA procedures under such conditions. I believe the path forward must lie more in understanding our business needs, what we want our application to be capable of doing, rather than slavishly following the mantra of Normalize, Normalize, Normalize. I have searched this site without much success, however, does anyone out there have any experience designing an ADB for tracking insurance certificates for compliance with subcontract requirements? Thanks, Pew . |
#4
|
|||
|
|||
Insurance Certificates Database
Wow!
Great explanation! (it'll take me a while to digest it all and respond... please be patient) Anyone else with experience/direction here, jump in! Regards Jeff Boyce Microsoft Access MVP "oldblindpew" wrote in message ... Thanks for reply. Insurance certificates generally appear as a fairly standardized form, known as the Acord form. To the user, this certificate looks like one entity, with all its fields belonging in one table, but I have been warned over and over that Access cannot handle this kind of approach. (I still don't understand why it is better to store a reference to a table containing a value, rather than just storing the value itself. It seems we are using three fields and two tables to do what could be done by one field in one table. And if the same value appears in different records, so what? Computers are supposed to have plenty of muscle for this sort of thing). Anyway, each Firm for which we require insurance will have zero, one or more Certificates, and each Certificate describes one or more Policies. Therefore we need tables for Firms, Certificates, and Policies, with one-to-many relationships. FIRMS PK IdFirm Firm fields... CERTS PK IdCert FK IdFirm Cert fields... POLICIES PK IdPolicy FK IdCert Policy fields... The Cert fields and Policy fields seem to fall into two categories. For lack of better terms I will refer to them as the Definite and the Indefinite. Definite Cert Fields: FK ProducerCode (Id for the insurance agency that produces the certifcate) Cert Date (date certificate was produced or printed) FK CertHolderCode (Id for the firm holding the certificate, which should be us) Indefinite Cert Fields: NOCDays (the number of days Notice of Cancellation) NOCChange (do we also receive Notice of Material Change in coverage?) NOCStrikeEndeav (are the words "Endeavor to" stricken from the NOC?) NOCStrikeFailure (is the Failure clause stricken from the NOC?) Future Fields... (unknown) The Definite Cert fields unquestionably go in the Cert table, but the Indefinite ones seem more subject to both repeating values, and to growth or change in the list of fields, and therefore may belong in a separate many-to-many table. On the other hand, the Acord form is pretty stable, and in the unlikely event of change, how bad would it be to just add more fields to the Certs table? Another question right here. Notice the Certs table has foreign keys for three different firms, namely the Insured Firm, the Certificate Producer, and the Certificate Holder. Right now, I have all firms, regardless of type, in a single table. How can I have multiple foreign keys back to one common table? Won't this confuse the daylights out of Access? I notice in the Northwind database there are separate tables for Suppliers and Customers. I thought I was doing right to put all my firms in one table. Was this a mistake? Definite Policy Fields: FK InsurerCode (Id for the insurance firm issuing the policy) FK PolicyTypeCode (Id for Policy Type, ie GL, Auto, Excess, Workers Comp, etc) Policy Number PolicyDateEffective PolicyDateExpires Indefinite Policy Fields: Type... Basis... Scope... Limits... Endorsements... As was the case with Certs information, Indefinite Policy Fields are subject to repeating values and to variable and indeterminate field lists. Different types of policies have different lists of fields, so there has to be some way to prevent inappropriate associations. For example, an Additional Insured Endorsement might apply to a General Liability policy, but not to an Excess Liability Policy. At this point, all our fields seem fixed, but who knows what changes the future may bring? Again the question is whether to take a chance a cram all presently known fields into the Policies table, or set up a many-to-many relationship between the Policies table and a PolicyDetails table. Another, less normalized, approach would be to create separate tables for each type of policy, and just put the appropriate fields in each table. This would solve the problem of inappropriate associations between Policies and Policy Details, but it would be less normalized and less flexible. For example if the need arose to begin requiring a new type of policy for some or all of our subcontractors, this would mean creating an entirely new table. This why I asked about others with experience in this same area, because the "right" structure depends on what you plan on doing with the data. I wanted to compare notes with someone else to see how they approached the task. Please note that capturing the information from the Acord form is barely one third of the task. We also have to specify our insurance requirements in detail for each subcontractor. Most insurance requirements will be the same from one subcontract to the next, but any of them may differ, so each subcontract must have its own set of required values. The subcontractor's insurance certificates are then checked against those required values. If the certificate is non-compliant, we want to report precisely which parameters are at fault. Thanks, Pew "Jeff Boyce" wrote: If you'll provide a description of your table structure and relationships, folks here may be able to offer more specific suggestions. .... and even if someone has a DB for "tracking insurance certificates for compliance ...", you'll still need to deconstruct what they've done to make sure their situation and yours are reasonably matched! Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "oldblindpew" wrote in message ... Things have slowed down in my department, so I'm back to working on my Contracts Administration database. When I left off, I was mired in designing tables for insurance certificates, unable to decide how far to go with normalizing. Under-normalizing results in large spreadsheet-like tables, which I am reliably informed are anathema to Access. On the other hand, I believe it is possible to over-normalize almost anything until the data is so scattered into many interrelated tables that performance is compromised, not to mention the unguessed sorrows to be had in writing VBA procedures under such conditions. I believe the path forward must lie more in understanding our business needs, what we want our application to be capable of doing, rather than slavishly following the mantra of Normalize, Normalize, Normalize. I have searched this site without much success, however, does anyone out there have any experience designing an ADB for tracking insurance certificates for compliance with subcontract requirements? Thanks, Pew . |
#5
|
|||
|
|||
Insurance Certificates Database
I'll start working my way through, adding in comments (see below)...
"oldblindpew" wrote in message ... Thanks for reply. Insurance certificates generally appear as a fairly standardized form, known as the Acord form. To the user, this certificate looks like one entity, with all its fields belonging in one table, but I have been warned over and over that Access cannot handle this kind of approach. I suspect it isn't so much that Access cannot handle a "wide" table as it is making proper use of the tool. Access is a relational database, and its features/functions are optimized for well-normalized data. An analogy I'm fond of using is that you absolutely can drive nails with a chainsaw, but that doesn't mean it's the proper tool or a good idea! (I still don't understand why it is better to store a reference to a table containing a value, rather than just storing the value itself. It seems we are using three fields and two tables to do what could be done by one field in one table. And if the same value appears in different records, so what? Computers are supposed to have plenty of muscle for this sort of thing). Let's talk about person name ... one user enters "John Smith", another "John J.J. Smith", another "J. Smith" and another "J.J.J. Smith" ... and they all refer to the same human. And then John has his name legally changed to Jim. If you "store the value itself" in a table, you have little chance of connecting the dots and knowing those 4 (wait, 5!) people are all the same person. If you store John once in a Person table, with a PersonID field, then use the PersonID field in your 'other' table, you save the user data entry time (just pick John from the combobox), reduce the risk of having 5 entries that are all the same person, and make updating John's name to Jim quite simple ... go to the lookup table and change it once! Anyway, each Firm for which we require insurance will have zero, one or more Certificates, and each Certificate describes one or more Policies. Therefore we need tables for Firms, Certificates, and Policies, with one-to-many relationships. FIRMS PK IdFirm Firm fields... CERTS PK IdCert FK IdFirm Cert fields... POLICIES PK IdPolicy FK IdCert Policy fields... The Cert fields and Policy fields seem to fall into two categories. For lack of better terms I will refer to them as the Definite and the Indefinite. Definite Cert Fields: FK ProducerCode (Id for the insurance agency that produces the certifcate) Cert Date (date certificate was produced or printed) FK CertHolderCode (Id for the firm holding the certificate, which should be us) Indefinite Cert Fields: NOCDays (the number of days Notice of Cancellation) NOCChange (do we also receive Notice of Material Change in coverage?) NOCStrikeEndeav (are the words "Endeavor to" stricken from the NOC?) NOCStrikeFailure (is the Failure clause stricken from the NOC?) Future Fields... (unknown) The Definite Cert fields unquestionably go in the Cert table, but the Indefinite ones seem more subject to both repeating values, and to growth or change in the list of fields, and therefore may belong in a separate many-to-many table. On the other hand, the Acord form is pretty stable, and in the unlikely event of change, how bad would it be to just add more fields to the Certs table? Are you saying that Certificates have one-to-many "indefinite" fields? From your above description, your Definite and Indefinite Cert fields appear to be characterizing the Certificates (i.e., they are "attributes" of the certificates). Why would you need to change the number of attributes? Yes, you can always modify a table structure. Yes, spending the time before you finalize the application cuts down on how much later changing you have to do. In your situation, your "definite" fields for ProducerCode and CertHolderCode seem like excellent candidates for lookup tables -- if you add firms, you add them one place and do the lookup for these codes. But the [CertDate] and the [NOCDays] and the others seem like 'fill-in-the-blank' type fields. It would be tough to try to generate the list of all possible values, so why not just let folks fill in the appropriate values (or check the checkbox or ...)? Another question right here. Notice the Certs table has foreign keys for three different firms, namely the Insured Firm, the Certificate Producer, and the Certificate Holder. Right now, I have all firms, regardless of type, in a single table. How can I have multiple foreign keys back to one common table? Won't this confuse the daylights out of Access? A human looking at that might get confused, but Access won't. If you tell Access to use the row from [Firms] with ID = 17, it really won't matter whether you tell it to do that for [InsuredFirm] or [Producer] or [Holder] .... or for ALL THREE! I notice in the Northwind database there are separate tables for Suppliers and Customers. I thought I was doing right to put all my firms in one table. Was this a mistake? I'd keep them in one table. I suspect the reason for separate tables for suppliers and customers is because they have too many un-shared attributes. Yes, sure, they have names, addresses, etc. And if you wanted to get really ana..., er, rigorous about normalizing, you COULD create a [Business] table to hold all of them, and the common fields, then create "typeof" tables that hold the data specific to each typeof ... but you have to look in multiple places (or Access does) to pull it all together. Doable, but perhaps not cost/time effective. Definite Policy Fields: FK InsurerCode (Id for the insurance firm issuing the policy) FK PolicyTypeCode (Id for Policy Type, ie GL, Auto, Excess, Workers Comp, etc) Policy Number PolicyDateEffective PolicyDateExpires Indefinite Policy Fields: Type... Basis... Scope... Limits... Endorsements... As was the case with Certs information, Indefinite Policy Fields are subject to repeating values and to variable and indeterminate field lists. Different types of policies have different lists of fields, so there has to be some way to prevent inappropriate associations. For example, an Additional Insured Endorsement might apply to a General Liability policy, but not to an Excess Liability Policy. At this point, all our fields seem fixed, but who knows what changes the future may bring? Again the question is whether to take a chance a cram all presently known fields into the Policies table, or set up a many-to-many relationship between the Policies table and a PolicyDetails table. I'd suggest, if you're comfortable with the above analysis on Certs, use a similar approach here. Another, less normalized, approach would be to create separate tables for each type of policy, and just put the appropriate fields in each table. This would solve the problem of inappropriate associations between Policies and Policy Details, but it would be less normalized and less flexible. For example if the need arose to begin requiring a new type of policy for some or all of our subcontractors, this would mean creating an entirely new table. I generally recommend against doing this. It would involve a lot more maintenance than you've noted. You'd also have to modify queries, forms, reports, code, etc. This why I asked about others with experience in this same area, because the "right" structure depends on what you plan on doing with the data. I wanted to compare notes with someone else to see how they approached the task. I disagree. I believe the "right" structure depends on your domain, and NOT on the intended use. The "right" data, absolutely ...! Because you have queries and forms and reports, you absolutely do NOT need to make your table structure fit either your input or your output. Please note that capturing the information from the Acord form is barely one third of the task. We also have to specify our insurance requirements in detail for each subcontractor. Most insurance requirements will be the same from one subcontract to the next, but any of them may differ, so each subcontract must have its own set of required values. The subcontractor's insurance certificates are then checked against those required values. If the certificate is non-compliant, we want to report precisely which parameters are at fault. I'm not entirely clear from your description, but this sounds like it might be amenable to an approach that associates one-to-many attributes (i.e., "insurance requirements") with each subcontractor. If that's a reasonable statement, then look into using a main form/subform construction, or a "paired listbox" approach (see the query wizard in action). Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. Thanks, Pew |
#6
|
|||
|
|||
Insurance Certificates Database
I understand the basic idea of storing data once to ensure consistency and
facilitate maintenance. My parenthetical comment was written with more extreme cases in mind. For example, you could have "Zip Code" as a field in an address table, and let the user type the value, or you could normalize it by having a separate table of Zip Codes. There would then be a field in the Address table for "ZipCodeKey", and a separate table with fields for "ZipCodeKey" and "ZipCodeDescription". Thus two tables and three fields instead of one field in one table. Even though this is strict normalization, I doubt Zip Codes are often handled this way, if ever. This is analogous to my dilemma of whether to place my "Indefinite" fields directly in the Certs table, or list them separately in a CertsDetail table. I agree my Indefinite Cert Fields look like fixed attributes of the certificate, which would argue for leaving them in the Certs table. But as I have read elsewhere in this forum, some are strongly opposed to building a table based upon a questionnaire type form (and aren't all forms ulitimately questionnaires?) with one field per question. The reasons being 1) the tables become too large, and 2) as soon as you get done, someone will revise the form and add more questions and check boxes, and 3) it's just not normalized and Access won't like it. Originally, I wanted one big table for the entire Acord Form, treating each piece of data as an attribute of the insurance certificate (excepting firm ID references). When I mentioned a table with over a hundred fields, I was told "No Way, you need to normalize". Now you're sounding like it's okay NOT to normalize so long as your tables don't get too big. The Policies table presents more of a problem than the Certs table, because there are potentially more Policy attributes than Cert attributes. Also, as mentioned previously, different types of policies have different types of attributes. There is not a fixed set of attributes applicable to all policies, so it wouldn't make sense to have a fixed set of fields in the Policies table, unless you didn't mind a bunch of empty or inactive fields. I suggested having a different table for each type of policy to solve this problem, but you recommended against it. I did not make this completely clear before, but insurance requirements are determined by the subcontract language, so there would be one set of requirements per subcontract, but the actual policies are carried by the subcontractor, so there would be one set of policies in force for each subcontractor. The subcontractor's coverage needs to be sufficient for worst case, ie the most demanding set of requirements. Again, the requirements are attributable to a document, but the certificate is attributable to a firm. Thanks, Pew the many-to-many relationship between "Jeff Boyce" wrote: I'll start working my way through, adding in comments (see below)... "oldblindpew" wrote in message ... Thanks for reply. Insurance certificates generally appear as a fairly standardized form, known as the Acord form. To the user, this certificate looks like one entity, with all its fields belonging in one table, but I have been warned over and over that Access cannot handle this kind of approach. I suspect it isn't so much that Access cannot handle a "wide" table as it is making proper use of the tool. Access is a relational database, and its features/functions are optimized for well-normalized data. An analogy I'm fond of using is that you absolutely can drive nails with a chainsaw, but that doesn't mean it's the proper tool or a good idea! (I still don't understand why it is better to store a reference to a table containing a value, rather than just storing the value itself. It seems we are using three fields and two tables to do what could be done by one field in one table. And if the same value appears in different records, so what? Computers are supposed to have plenty of muscle for this sort of thing). Let's talk about person name ... one user enters "John Smith", another "John J.J. Smith", another "J. Smith" and another "J.J.J. Smith" ... and they all refer to the same human. And then John has his name legally changed to Jim. If you "store the value itself" in a table, you have little chance of connecting the dots and knowing those 4 (wait, 5!) people are all the same person. If you store John once in a Person table, with a PersonID field, then use the PersonID field in your 'other' table, you save the user data entry time (just pick John from the combobox), reduce the risk of having 5 entries that are all the same person, and make updating John's name to Jim quite simple ... go to the lookup table and change it once! Anyway, each Firm for which we require insurance will have zero, one or more Certificates, and each Certificate describes one or more Policies. Therefore we need tables for Firms, Certificates, and Policies, with one-to-many relationships. FIRMS PK IdFirm Firm fields... CERTS PK IdCert FK IdFirm Cert fields... POLICIES PK IdPolicy FK IdCert Policy fields... The Cert fields and Policy fields seem to fall into two categories. For lack of better terms I will refer to them as the Definite and the Indefinite. Definite Cert Fields: FK ProducerCode (Id for the insurance agency that produces the certifcate) Cert Date (date certificate was produced or printed) FK CertHolderCode (Id for the firm holding the certificate, which should be us) Indefinite Cert Fields: NOCDays (the number of days Notice of Cancellation) NOCChange (do we also receive Notice of Material Change in coverage?) NOCStrikeEndeav (are the words "Endeavor to" stricken from the NOC?) NOCStrikeFailure (is the Failure clause stricken from the NOC?) Future Fields... (unknown) The Definite Cert fields unquestionably go in the Cert table, but the Indefinite ones seem more subject to both repeating values, and to growth or change in the list of fields, and therefore may belong in a separate many-to-many table. On the other hand, the Acord form is pretty stable, and in the unlikely event of change, how bad would it be to just add more fields to the Certs table? Are you saying that Certificates have one-to-many "indefinite" fields? From your above description, your Definite and Indefinite Cert fields appear to be characterizing the Certificates (i.e., they are "attributes" of the certificates). Why would you need to change the number of attributes? Yes, you can always modify a table structure. Yes, spending the time before you finalize the application cuts down on how much later changing you have to do. In your situation, your "definite" fields for ProducerCode and CertHolderCode seem like excellent candidates for lookup tables -- if you add firms, you add them one place and do the lookup for these codes. But the [CertDate] and the [NOCDays] and the others seem like 'fill-in-the-blank' type fields. It would be tough to try to generate the list of all possible values, so why not just let folks fill in the appropriate values (or check the checkbox or ...)? Another question right here. Notice the Certs table has foreign keys for three different firms, namely the Insured Firm, the Certificate Producer, and the Certificate Holder. Right now, I have all firms, regardless of type, in a single table. How can I have multiple foreign keys back to one common table? Won't this confuse the daylights out of Access? A human looking at that might get confused, but Access won't. If you tell Access to use the row from [Firms] with ID = 17, it really won't matter whether you tell it to do that for [InsuredFirm] or [Producer] or [Holder] .... or for ALL THREE! I notice in the Northwind database there are separate tables for Suppliers and Customers. I thought I was doing right to put all my firms in one table. Was this a mistake? I'd keep them in one table. I suspect the reason for separate tables for suppliers and customers is because they have too many un-shared attributes. Yes, sure, they have names, addresses, etc. And if you wanted to get really ana..., er, rigorous about normalizing, you COULD create a [Business] table to hold all of them, and the common fields, then create "typeof" tables that hold the data specific to each typeof ... but you have to look in multiple places (or Access does) to pull it all together. Doable, but perhaps not cost/time effective. Definite Policy Fields: FK InsurerCode (Id for the insurance firm issuing the policy) FK PolicyTypeCode (Id for Policy Type, ie GL, Auto, Excess, Workers Comp, etc) Policy Number PolicyDateEffective PolicyDateExpires Indefinite Policy Fields: Type... Basis... Scope... Limits... Endorsements... As was the case with Certs information, Indefinite Policy Fields are subject to repeating values and to variable and indeterminate field lists. Different types of policies have different lists of fields, so there has to be some way to prevent inappropriate associations. For example, an Additional Insured Endorsement might apply to a General Liability policy, but not to an Excess Liability Policy. At this point, all our fields seem fixed, but who knows what changes the future may bring? Again the question is whether to take a chance a cram all presently known fields into the Policies table, or set up a many-to-many relationship between the Policies table and a PolicyDetails table. I'd suggest, if you're comfortable with the above analysis on Certs, use a similar approach here. Another, less normalized, approach would be to create separate tables for each type of policy, and just put the appropriate fields in each table. This would solve the problem of inappropriate associations between Policies and Policy Details, but it would be less normalized and less flexible. For example if the need arose to begin requiring a new type of policy for some or all of our subcontractors, this would mean creating an entirely new table. I generally recommend against doing this. It would involve a lot more maintenance than you've noted. You'd also have to modify queries, forms, reports, code, etc. This why I asked about others with experience in this same area, because the "right" structure depends on what you plan on doing with the data. I wanted to compare notes with someone else to see how they approached the task. I disagree. I believe the "right" structure depends on your domain, and NOT on the intended use. The "right" data, absolutely ...! Because you have queries and forms and reports, you absolutely do NOT need to make your table structure fit either your input or your output. Please note that capturing the information from the Acord form is barely one third of the task. We also have to specify our insurance requirements in detail for each subcontractor. Most insurance requirements will be the same from one subcontract to the next, but any of them may differ, so each subcontract must have its own set of required values. The subcontractor's insurance certificates are then checked against those required values. If the certificate is non-compliant, we want to report precisely which parameters are at fault. I'm not entirely clear from your description, but this sounds like it might be amenable to an approach that associates one-to-many attributes (i.e., "insurance requirements") with each subcontractor. If that's a reasonable statement, then look into using a main form/subform construction, or a "paired listbox" approach (see the query wizard in action). Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. Thanks, Pew . |
#7
|
|||
|
|||
Insurance Certificates Database
OBP,
Can't help it... after the tblZipCode comment had to jump in... Normalizing ZipCodes, that would not be the reason I have a tblCityStateZip. I have it to prevent *bogus* information. Do not confuse *normalization* with what I call *look-up* tables, which simply store values to keep the data *pure*. I don't know where you get the idea that building a database for a questionnaire is opposed. There is a sample survey that is constantly being recommended for review... http://www.rogersaccesslibrary.com/f...osts.asp?TID=3 I have a database where the options of 9 are stored in a table. If you choose to change and option you can do and that option once changed will go forward storing the new option. BUT if you look up the old *form* you will see the old option(s). That way I am not actually storing all that text, ie... A 1. ABC A 2. DEF A 3. GHI ....next month some one changes the option to... B 1. ABC B 2. DEF B 4. JKL ....so my joiner table looks like the below. A & B would be the PK in the Main table and FK in the Detail table. The numbers would be the Option PK and the letters represent the text. So when an Options text changes it add a field to my Options table thereby keeping my data normalized. A - 1 A - 2 A - 3 B - 1 B - 2 B - 4 I should also note, I do not see every form as a *questionnarie*, perhaps data entry form would be more accurate. IMHO, if your table has over 50 fields (and some would argue that's alot) it's not a database it's a flat file and you might as well use Excel. What is the point of using a powerful tool if not to take advantage of the power? If I'm never going to race why buy a race car? I might as well stick with my Jeep. I remember thinking about doing a database like this... a Client request. I turned them down and suggested they by the already established software and I even helped them select the one that best suited them. That is not to say Access can't handle this because it can, however, it will not be a *quick and dirty* task. It should be done modeling a flat file format. It should be normalized with look-up tables. And while the initial set-up will be interesting and time consuming the end result, if done properly, will be magnificant! No one here wants to *argue* with you and if you insist upon putting the data in a flat file then later you will have problems. Think of building your 2,000 SF house on a 1,000 SF foundation. The house will fall, not now and maybe not for many years but it's coming down. -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "oldblindpew" wrote in message ... I understand the basic idea of storing data once to ensure consistency and facilitate maintenance. My parenthetical comment was written with more extreme cases in mind. For example, you could have "Zip Code" as a field in an address table, and let the user type the value, or you could normalize it by having a separate table of Zip Codes. There would then be a field in the Address table for "ZipCodeKey", and a separate table with fields for "ZipCodeKey" and "ZipCodeDescription". Thus two tables and three fields instead of one field in one table. Even though this is strict normalization, I doubt Zip Codes are often handled this way, if ever. This is analogous to my dilemma of whether to place my "Indefinite" fields directly in the Certs table, or list them separately in a CertsDetail table. I agree my Indefinite Cert Fields look like fixed attributes of the certificate, which would argue for leaving them in the Certs table. But as I have read elsewhere in this forum, some are strongly opposed to building a table based upon a questionnaire type form (and aren't all forms ulitimately questionnaires?) with one field per question. The reasons being 1) the tables become too large, and 2) as soon as you get done, someone will revise the form and add more questions and check boxes, and 3) it's just not normalized and Access won't like it. Originally, I wanted one big table for the entire Acord Form, treating each piece of data as an attribute of the insurance certificate (excepting firm ID references). When I mentioned a table with over a hundred fields, I was told "No Way, you need to normalize". Now you're sounding like it's okay NOT to normalize so long as your tables don't get too big. The Policies table presents more of a problem than the Certs table, because there are potentially more Policy attributes than Cert attributes. Also, as mentioned previously, different types of policies have different types of attributes. There is not a fixed set of attributes applicable to all policies, so it wouldn't make sense to have a fixed set of fields in the Policies table, unless you didn't mind a bunch of empty or inactive fields. I suggested having a different table for each type of policy to solve this problem, but you recommended against it. I did not make this completely clear before, but insurance requirements are determined by the subcontract language, so there would be one set of requirements per subcontract, but the actual policies are carried by the subcontractor, so there would be one set of policies in force for each subcontractor. The subcontractor's coverage needs to be sufficient for worst case, ie the most demanding set of requirements. Again, the requirements are attributable to a document, but the certificate is attributable to a firm. Thanks, Pew the many-to-many relationship between "Jeff Boyce" wrote: I'll start working my way through, adding in comments (see below)... "oldblindpew" wrote in message ... Thanks for reply. Insurance certificates generally appear as a fairly standardized form, known as the Acord form. To the user, this certificate looks like one entity, with all its fields belonging in one table, but I have been warned over and over that Access cannot handle this kind of approach. I suspect it isn't so much that Access cannot handle a "wide" table as it is making proper use of the tool. Access is a relational database, and its features/functions are optimized for well-normalized data. An analogy I'm fond of using is that you absolutely can drive nails with a chainsaw, but that doesn't mean it's the proper tool or a good idea! (I still don't understand why it is better to store a reference to a table containing a value, rather than just storing the value itself. It seems we are using three fields and two tables to do what could be done by one field in one table. And if the same value appears in different records, so what? Computers are supposed to have plenty of muscle for this sort of thing). Let's talk about person name ... one user enters "John Smith", another "John J.J. Smith", another "J. Smith" and another "J.J.J. Smith" ... and they all refer to the same human. And then John has his name legally changed to Jim. If you "store the value itself" in a table, you have little chance of connecting the dots and knowing those 4 (wait, 5!) people are all the same person. If you store John once in a Person table, with a PersonID field, then use the PersonID field in your 'other' table, you save the user data entry time (just pick John from the combobox), reduce the risk of having 5 entries that are all the same person, and make updating John's name to Jim quite simple ... go to the lookup table and change it once! Anyway, each Firm for which we require insurance will have zero, one or more Certificates, and each Certificate describes one or more Policies. Therefore we need tables for Firms, Certificates, and Policies, with one-to-many relationships. FIRMS PK IdFirm Firm fields... CERTS PK IdCert FK IdFirm Cert fields... POLICIES PK IdPolicy FK IdCert Policy fields... The Cert fields and Policy fields seem to fall into two categories. For lack of better terms I will refer to them as the Definite and the Indefinite. Definite Cert Fields: FK ProducerCode (Id for the insurance agency that produces the certifcate) Cert Date (date certificate was produced or printed) FK CertHolderCode (Id for the firm holding the certificate, which should be us) Indefinite Cert Fields: NOCDays (the number of days Notice of Cancellation) NOCChange (do we also receive Notice of Material Change in coverage?) NOCStrikeEndeav (are the words "Endeavor to" stricken from the NOC?) NOCStrikeFailure (is the Failure clause stricken from the NOC?) Future Fields... (unknown) The Definite Cert fields unquestionably go in the Cert table, but the Indefinite ones seem more subject to both repeating values, and to growth or change in the list of fields, and therefore may belong in a separate many-to-many table. On the other hand, the Acord form is pretty stable, and in the unlikely event of change, how bad would it be to just add more fields to the Certs table? Are you saying that Certificates have one-to-many "indefinite" fields? From your above description, your Definite and Indefinite Cert fields appear to be characterizing the Certificates (i.e., they are "attributes" of the certificates). Why would you need to change the number of attributes? Yes, you can always modify a table structure. Yes, spending the time before you finalize the application cuts down on how much later changing you have to do. In your situation, your "definite" fields for ProducerCode and CertHolderCode seem like excellent candidates for lookup tables -- if you add firms, you add them one place and do the lookup for these codes. But the [CertDate] and the [NOCDays] and the others seem like 'fill-in-the-blank' type fields. It would be tough to try to generate the list of all possible values, so why not just let folks fill in the appropriate values (or check the checkbox or ...)? Another question right here. Notice the Certs table has foreign keys for three different firms, namely the Insured Firm, the Certificate Producer, and the Certificate Holder. Right now, I have all firms, regardless of type, in a single table. How can I have multiple foreign keys back to one common table? Won't this confuse the daylights out of Access? A human looking at that might get confused, but Access won't. If you tell Access to use the row from [Firms] with ID = 17, it really won't matter whether you tell it to do that for [InsuredFirm] or [Producer] or [Holder] .... or for ALL THREE! I notice in the Northwind database there are separate tables for Suppliers and Customers. I thought I was doing right to put all my firms in one table. Was this a mistake? I'd keep them in one table. I suspect the reason for separate tables for suppliers and customers is because they have too many un-shared attributes. Yes, sure, they have names, addresses, etc. And if you wanted to get really ana..., er, rigorous about normalizing, you COULD create a [Business] table to hold all of them, and the common fields, then create "typeof" tables that hold the data specific to each typeof ... but you have to look in multiple places (or Access does) to pull it all together. Doable, but perhaps not cost/time effective. Definite Policy Fields: FK InsurerCode (Id for the insurance firm issuing the policy) FK PolicyTypeCode (Id for Policy Type, ie GL, Auto, Excess, Workers Comp, etc) Policy Number PolicyDateEffective PolicyDateExpires Indefinite Policy Fields: Type... Basis... Scope... Limits... Endorsements... As was the case with Certs information, Indefinite Policy Fields are subject to repeating values and to variable and indeterminate field lists. Different types of policies have different lists of fields, so there has to be some way to prevent inappropriate associations. For example, an Additional Insured Endorsement might apply to a General Liability policy, but not to an Excess Liability Policy. At this point, all our fields seem fixed, but who knows what changes the future may bring? Again the question is whether to take a chance a cram all presently known fields into the Policies table, or set up a many-to-many relationship between the Policies table and a PolicyDetails table. I'd suggest, if you're comfortable with the above analysis on Certs, use a similar approach here. Another, less normalized, approach would be to create separate tables for each type of policy, and just put the appropriate fields in each table. This would solve the problem of inappropriate associations between Policies and Policy Details, but it would be less normalized and less flexible. For example if the need arose to begin requiring a new type of policy for some or all of our subcontractors, this would mean creating an entirely new table. I generally recommend against doing this. It would involve a lot more maintenance than you've noted. You'd also have to modify queries, forms, reports, code, etc. This why I asked about others with experience in this same area, because the "right" structure depends on what you plan on doing with the data. I wanted to compare notes with someone else to see how they approached the task. I disagree. I believe the "right" structure depends on your domain, and NOT on the intended use. The "right" data, absolutely ...! Because you have queries and forms and reports, you absolutely do NOT need to make your table structure fit either your input or your output. Please note that capturing the information from the Acord form is barely one third of the task. We also have to specify our insurance requirements in detail for each subcontractor. Most insurance requirements will be the same from one subcontract to the next, but any of them may differ, so each subcontract must have its own set of required values. The subcontractor's insurance certificates are then checked against those required values. If the certificate is non-compliant, we want to report precisely which parameters are at fault. I'm not entirely clear from your description, but this sounds like it might be amenable to an approach that associates one-to-many attributes (i.e., "insurance requirements") with each subcontractor. If that's a reasonable statement, then look into using a main form/subform construction, or a "paired listbox" approach (see the query wizard in action). Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. Thanks, Pew . |
#8
|
|||
|
|||
Insurance Certificates Database
Hello Pew,
This (and your name, and access comments ) sounds familiar to one we tried to help on in a lengthy exchange a few months back. If so, or maybe even if not, ....... Yours is a somewhat complex application in this area. If this is one that you are having difficulty with on a long term basis, or if your attempts to resolve it in this thread get bogged woen in complication, in addition to the above, may I suggest the following: You are really talking about two toolboxes here" Relational Database Design and Architecture systesm (which basically successfully runs the whole world's structure data systems) and Access, a related tool box. I wouldn't waste your time implying that the practices-for-success covered by these toolboxes have some "limitations" affecting your design. I think that that is confusing you. Start by refining a rigorous, complete enough, precise, unambiguous description of your real world process that you want to database, with sufficient detail in the areas relevant to databasing. To keep your self on track, do this without using any Access or DB terminology. You might use your 1/14/2010 11:38 AM PST post as the starting point, as it was an excellent start. That alone might guide you to the answers, but if not, you could include it in your post. Sounds slow and methodical, but for your situation it might provide a foundation for decisively moving forwards instead of spinning your wheels. |
#9
|
|||
|
|||
Insurance Certificates Database
I'm sorry, Gina. It was not my intention to irritate anyone, but to try to
better understand the practical limits of normalization. The zipcode illustration is one I have seen used before for this purpose. I never said or meant that anyone here opposes building a database for a questionnaire. I said there was opposition to building a spreadsheet-like table based on a questionnaire, with one question per field. I'm sure you make some good points about your "options of 9" table, but I couldn't follow any of it. I think it is accurate to say that all forms are in the broadest sense questionnaires, because they present a list of stated or implied questions to which the respondent replies with answers (data) in the form of text, numbers, checkmarks, etc. We did have a commercial certificate tracking application a while back, which was actually based on Access, but it didn't work very well for us and was not being aggressively updated and enhanced, so we let it go. This is not to say some other better application doesn't exist out there, but our management does not seem inclined to pursue that option at this time. Personally, I'm continually amazed at how quirky commercial business software can be, and am confident that I have designed and can design better interfaces, despite not being a programmer by trade. Learning Access and VBA is just hard, that's all, like learning a foreign language. If it was easy, one could get the answers from books instead of having to appeal to the kindness of volunteers such as yourself. I am definitely not into "quick and dirty". When have I ever said as much? I feel sorry for those who say they just want to quickly set up a simple database application. They should be told up front that there is no such thing. I don't understand your statement that my task should be "done modeling a flat file format". Was this a typo? Later you state that if I insist on putting the data in a flat file I will have problems. BTW, the whole point of this thread is that I accept the notion that flat tables are not viable. The point under discussion is HOW FAR TO GO in breaking the data down into various tables, ie normalization. Genius in design lies in knowing where to compromise. Again, I'm sorry to be such a bother and hard to teach. I appreciate your patience and help. --Pew "Gina Whipp" wrote: OBP, Can't help it... after the tblZipCode comment had to jump in... Normalizing ZipCodes, that would not be the reason I have a tblCityStateZip. I have it to prevent *bogus* information. Do not confuse *normalization* with what I call *look-up* tables, which simply store values to keep the data *pure*. I don't know where you get the idea that building a database for a questionnaire is opposed. There is a sample survey that is constantly being recommended for review... http://www.rogersaccesslibrary.com/f...osts.asp?TID=3 I have a database where the options of 9 are stored in a table. If you choose to change and option you can do and that option once changed will go forward storing the new option. BUT if you look up the old *form* you will see the old option(s). That way I am not actually storing all that text, ie... A 1. ABC A 2. DEF A 3. GHI ....next month some one changes the option to... B 1. ABC B 2. DEF B 4. JKL ....so my joiner table looks like the below. A & B would be the PK in the Main table and FK in the Detail table. The numbers would be the Option PK and the letters represent the text. So when an Options text changes it add a field to my Options table thereby keeping my data normalized. A - 1 A - 2 A - 3 B - 1 B - 2 B - 4 I should also note, I do not see every form as a *questionnarie*, perhaps data entry form would be more accurate. IMHO, if your table has over 50 fields (and some would argue that's alot) it's not a database it's a flat file and you might as well use Excel. What is the point of using a powerful tool if not to take advantage of the power? If I'm never going to race why buy a race car? I might as well stick with my Jeep. I remember thinking about doing a database like this... a Client request. I turned them down and suggested they by the already established software and I even helped them select the one that best suited them. That is not to say Access can't handle this because it can, however, it will not be a *quick and dirty* task. It should be done modeling a flat file format. It should be normalized with look-up tables. And while the initial set-up will be interesting and time consuming the end result, if done properly, will be magnificant! No one here wants to *argue* with you and if you insist upon putting the data in a flat file then later you will have problems. Think of building your 2,000 SF house on a 1,000 SF foundation. The house will fall, not now and maybe not for many years but it's coming down. -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "oldblindpew" wrote in message ... I understand the basic idea of storing data once to ensure consistency and facilitate maintenance. My parenthetical comment was written with more extreme cases in mind. For example, you could have "Zip Code" as a field in an address table, and let the user type the value, or you could normalize it by having a separate table of Zip Codes. There would then be a field in the Address table for "ZipCodeKey", and a separate table with fields for "ZipCodeKey" and "ZipCodeDescription". Thus two tables and three fields instead of one field in one table. Even though this is strict normalization, I doubt Zip Codes are often handled this way, if ever. This is analogous to my dilemma of whether to place my "Indefinite" fields directly in the Certs table, or list them separately in a CertsDetail table. I agree my Indefinite Cert Fields look like fixed attributes of the certificate, which would argue for leaving them in the Certs table. But as I have read elsewhere in this forum, some are strongly opposed to building a table based upon a questionnaire type form (and aren't all forms ulitimately questionnaires?) with one field per question. The reasons being 1) the tables become too large, and 2) as soon as you get done, someone will revise the form and add more questions and check boxes, and 3) it's just not normalized and Access won't like it. Originally, I wanted one big table for the entire Acord Form, treating each piece of data as an attribute of the insurance certificate (excepting firm ID references). When I mentioned a table with over a hundred fields, I was told "No Way, you need to normalize". Now you're sounding like it's okay NOT to normalize so long as your tables don't get too big. The Policies table presents more of a problem than the Certs table, because there are potentially more Policy attributes than Cert attributes. Also, as mentioned previously, different types of policies have different types of attributes. There is not a fixed set of attributes applicable to all policies, so it wouldn't make sense to have a fixed set of fields in the Policies table, unless you didn't mind a bunch of empty or inactive fields. I suggested having a different table for each type of policy to solve this problem, but you recommended against it. I did not make this completely clear before, but insurance requirements are determined by the subcontract language, so there would be one set of requirements per subcontract, but the actual policies are carried by the subcontractor, so there would be one set of policies in force for each subcontractor. The subcontractor's coverage needs to be sufficient for worst case, ie the most demanding set of requirements. Again, the requirements are attributable to a document, but the certificate is attributable to a firm. Thanks, Pew the many-to-many relationship between "Jeff Boyce" wrote: I'll start working my way through, adding in comments (see below)... "oldblindpew" wrote in message ... Thanks for reply. Insurance certificates generally appear as a fairly standardized form, known as the Acord form. To the user, this certificate looks like one entity, with all its fields belonging in one table, but I have been warned over and over that Access cannot handle this kind of approach. I suspect it isn't so much that Access cannot handle a "wide" table as it is making proper use of the tool. Access is a relational database, and its features/functions are optimized for well-normalized data. An analogy I'm fond of using is that you absolutely can drive nails with a chainsaw, but that doesn't mean it's the proper tool or a good idea! (I still don't understand why it is better to store a reference to a table containing a value, rather than just storing the value itself. It seems we are using three fields and two tables to do what could be done by one field in one table. And if the same value appears in different records, so what? Computers are supposed to have plenty of muscle for this sort of thing). Let's talk about person name ... one user enters "John Smith", another "John J.J. Smith", another "J. Smith" and another "J.J.J. Smith" ... and they all refer to the same human. And then John has his name legally changed to Jim. If you "store the value itself" in a table, you have little chance of connecting the dots and knowing those 4 (wait, 5!) people are all the same person. If you store John once in a Person table, with a PersonID field, then use the PersonID field in your 'other' table, you save the user data entry time (just pick John from the combobox), reduce the risk of having 5 entries that are all the same person, and make updating John's name to Jim quite simple ... go to the lookup table and change it once! Anyway, each Firm for which we require insurance will have zero, one or more Certificates, and each Certificate describes one or more Policies. Therefore we need tables for Firms, Certificates, and Policies, with one-to-many relationships. FIRMS PK IdFirm Firm fields... CERTS PK IdCert FK IdFirm Cert fields... POLICIES PK IdPolicy FK IdCert Policy fields... The Cert fields and Policy fields seem to fall into two categories. For lack of better terms I will refer to them as the Definite and the Indefinite. Definite Cert Fields: FK ProducerCode (Id for the insurance agency that produces the certifcate) Cert Date (date certificate was produced or printed) FK CertHolderCode (Id for the firm holding the certificate, which should be us) Indefinite Cert Fields: NOCDays (the number of days Notice of Cancellation) NOCChange (do we also receive Notice of Material Change in coverage?) NOCStrikeEndeav (are the words "Endeavor to" stricken from the NOC?) NOCStrikeFailure (is the Failure clause stricken from the NOC?) Future Fields... (unknown) The Definite Cert fields unquestionably go in the Cert table, but the Indefinite ones seem more subject to both repeating values, and to growth or change in the list of fields, and therefore may belong in a separate many-to-many table. On the other hand, the Acord form is pretty stable, and in the unlikely event of change, how bad would it be to just add more fields to the Certs table? Are you saying that Certificates have one-to-many "indefinite" fields? From your above description, your Definite and Indefinite Cert fields appear to be characterizing the Certificates (i.e., they are "attributes" of the certificates). Why would you need to change the number of attributes? Yes, you can always modify a table structure. Yes, spending the time before you finalize the application cuts down on how much later changing you have to do. In your situation, your "definite" fields for ProducerCode and CertHolderCode seem like excellent candidates for lookup tables -- if you add firms, you add them one place and do the lookup for these codes. But the [CertDate] and the [NOCDays] and the others seem like 'fill-in-the-blank' type fields. It would be tough to try to generate the list of all possible values, so why not just let folks fill in the appropriate values (or check the checkbox or ...)? Another question right here. Notice the Certs table has foreign keys for three different firms, namely the Insured Firm, the Certificate Producer, and the Certificate Holder. Right now, I have all firms, regardless of type, in a single table. How can I have multiple foreign keys back to one common table? Won't this confuse the daylights out of Access? A human looking at that might get confused, but Access won't. If you tell Access to use the row from [Firms] with ID = 17, it really won't matter whether you tell it to do that for [InsuredFirm] or [Producer] or [Holder] .... or for ALL THREE! I notice in the Northwind database there are separate tables for Suppliers and Customers. I thought I was doing right to put all my firms in one table. Was this a mistake? I'd keep them in one table. I suspect the reason for separate tables for suppliers and customers is because they have too many un-shared attributes. |
#10
|
|||
|
|||
Insurance Certificates Database
See below ...
"oldblindpew" wrote in message ... I understand the basic idea of storing data once to ensure consistency and facilitate maintenance. My parenthetical comment was written with more extreme cases in mind. For example, you could have "Zip Code" as a field in an address table, and let the user type the value, or you could normalize it by having a separate table of Zip Codes. There would then be a field in the Address table for "ZipCodeKey", and a separate table with fields for "ZipCodeKey" and "ZipCodeDescription". Thus two tables and three fields instead of one field in one table. Even though this is strict normalization, I doubt Zip Codes are often handled this way, if ever. I use PostalCodes, not a postal code lookup table. This is analogous to my dilemma of whether to place my "Indefinite" fields directly in the Certs table, or list them separately in a CertsDetail table. I agree my Indefinite Cert Fields look like fixed attributes of the certificate, which would argue for leaving them in the Certs table. But as I have read elsewhere in this forum, some are strongly opposed to building a table based upon a questionnaire type form (and aren't all forms ulitimately questionnaires?) with one field per question. The reasons being 1) the tables become too large, and 2) as soon as you get done, someone will revise the form and add more questions and check boxes, and 3) it's just not normalized and Access won't like it. Access is optimized for normalized data. If you feed it 'sheet data, you and Access both have to work harder to come up with workarounds, plus you have the potential for a lot of maintenance work if the questions/columns have to be changed. Originally, I wanted one big table for the entire Acord Form, treating each piece of data as an attribute of the insurance certificate (excepting firm ID references). When I mentioned a table with over a hundred fields, I was told "No Way, you need to normalize". Now you're sounding like it's okay NOT to normalize so long as your tables don't get too big. If I gave that impression, I was mis-speaking. I value normalization in Access tables because it makes it easier for Access to work with the data. If you have a table with over 30 fields, chances are good that it isn't normalized. The Policies table presents more of a problem than the Certs table, because there are potentially more Policy attributes than Cert attributes. Also, as mentioned previously, different types of policies have different types of attributes. There is not a fixed set of attributes applicable to all policies, so it wouldn't make sense to have a fixed set of fields in the Policies table, unless you didn't mind a bunch of empty or inactive fields. I suggested having a different table for each type of policy to solve this problem, but you recommended against it. Remember, you are there, and we are not. You know your domain much better than we do. If you have different Policy types, each with their own unique attributes, then by all means model your structure on that reality! I did not make this completely clear before, but insurance requirements are determined by the subcontract language, so there would be one set of requirements per subcontract, but the actual policies are carried by the subcontractor, so there would be one set of policies in force for each subcontractor. The subcontractor's coverage needs to be sufficient for worst case, ie the most demanding set of requirements. Again, the requirements are attributable to a document, but the certificate is attributable to a firm. I'm having trouble visualizing the relationships among the entities. Care to clarify? Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. |
|
Thread Tools | |
Display Modes | |
|
|