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 |
#11
|
|||
|
|||
Insurance Certificates Database
OBP,
Not irrated and sorry if my posting came off as such... it was more a less, I see warm clear blue water and I'm jumping in. I want to give you a better reply, with more depth, but work is calling so I will have to wait till a wee bit later! I also will try to explain myself clearer for the Options of 9 because I do believe it is important to what you want to accomplish... ....till my next post! 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'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. |
#12
|
|||
|
|||
Insurance Certificates Database
Let me preface by saying this thread is way, way, over my head. :-)
But I want to let you know how we issue our certificates of insurance. We are a special events firm insuring concerts, fairs, sporting events, etc. We have one table called Clients which contains both our insureds and the companies we may be issuing certificates to (certificate holders). Each record has a class field. So our insured would be classified as Customer. The certificate holder would be classified as Venue, etc. We then have a child table called Policies. This contains all of the policy info for a particular insured; carrier, policy number, limits, etc. This table is updated manually as policies are renewed. Finally we have a table called Certificates. When our insured requests a certificate, we create a record in this table which pulls in insured name/address info from Clients, venue info and additional insured wording from Clients, and policy info from the Policies table. We then complete it with any special wording (more additional insureds, any special conditions, etc.) the certificate holder may require. |
#13
|
|||
|
|||
Insurance Certificates Database
Thanks for chiming in.
You said you have "a child table called Policies". A table is a place for records that share a common set of fields. How do you handle the fact that different types of policies have different sets of descriptive fields? Do you 1) perhaps have separate child tables for GL, Auto, Excess, and Worker's Comp Policies? Or, 2) perhaps have one table for common policy information, and another table or tables for the messier bits of information? Or 3) maybe your Policies table is extremely wide and has fields for almost every bit of information on the Acord form? This last case is what I started out doing, and in fact had actually built the tables and forms for both the Insurance Required and Insurance Provided. I was exploring how to go about checking provided coverage for compliance with required coverage when I learned that my approach was not recommended. Is your application something homemade, or is it a commercial product or a trade association project? Thanks, Pew "Underwriting" wrote: Let me preface by saying this thread is way, way, over my head. :-) But I want to let you know how we issue our certificates of insurance. We are a special events firm insuring concerts, fairs, sporting events, etc. We have one table called Clients which contains both our insureds and the companies we may be issuing certificates to (certificate holders). Each record has a class field. So our insured would be classified as Customer. The certificate holder would be classified as Venue, etc. We then have a child table called Policies. This contains all of the policy info for a particular insured; carrier, policy number, limits, etc. This table is updated manually as policies are renewed. Finally we have a table called Certificates. When our insured requests a certificate, we create a record in this table which pulls in insured name/address info from Clients, venue info and additional insured wording from Clients, and policy info from the Policies table. We then complete it with any special wording (more additional insureds, any special conditions, etc.) the certificate holder may require. |
#14
|
|||
|
|||
Insurance Certificates Database
Pew,
We have one Policies record for each insured. It contains fields for the policies we provide (GL, Work Comp, Property, auto, etc.); carrier, policy number, limits, policy dates, etc. The messier bits of information, such as additional insured wording, special certificate wording, etc. is entered in the actual Certificate table. This info is either pulled automatically from the Certificate Holder's client record, or it can be entered manually for unusual circumstances. Our application was created in-house. We have been using/improving it since 1998. We keep 'insurance required' data (specific additional insured wording, minimum coverage requirements, etc.) with the Certificate Holder record. The 'insurance provided' info is what we input in the Policies table for that particular insured. "oldblindpew" wrote: Thanks for chiming in. You said you have "a child table called Policies". A table is a place for records that share a common set of fields. How do you handle the fact that different types of policies have different sets of descriptive fields? Do you 1) perhaps have separate child tables for GL, Auto, Excess, and Worker's Comp Policies? Or, 2) perhaps have one table for common policy information, and another table or tables for the messier bits of information? Or 3) maybe your Policies table is extremely wide and has fields for almost every bit of information on the Acord form? This last case is what I started out doing, and in fact had actually built the tables and forms for both the Insurance Required and Insurance Provided. I was exploring how to go about checking provided coverage for compliance with required coverage when I learned that my approach was not recommended. Is your application something homemade, or is it a commercial product or a trade association project? Thanks, Pew "Underwriting" wrote: Let me preface by saying this thread is way, way, over my head. :-) But I want to let you know how we issue our certificates of insurance. We are a special events firm insuring concerts, fairs, sporting events, etc. We have one table called Clients which contains both our insureds and the companies we may be issuing certificates to (certificate holders). Each record has a class field. So our insured would be classified as Customer. The certificate holder would be classified as Venue, etc. We then have a child table called Policies. This contains all of the policy info for a particular insured; carrier, policy number, limits, etc. This table is updated manually as policies are renewed. Finally we have a table called Certificates. When our insured requests a certificate, we create a record in this table which pulls in insured name/address info from Clients, venue info and additional insured wording from Clients, and policy info from the Policies table. We then complete it with any special wording (more additional insureds, any special conditions, etc.) the certificate holder may require. |
#15
|
|||
|
|||
Insurance Certificates Database
It sounds like we were taking a similar approach. My Insurance Certificates
table had 86 fields, one field for each parameter for each policy. If a new type of policy were to come on the scene, widespread changes would be required throughout the application. The cognoscenti refer to this as the spreadsheet approach or "committing Excel" (as committing a crime or suicide). I get the impression that the spreadsheet approach is both harder to operate and harder to revise, although I am not sure which of the two criteria is most dreaded. My intuition is that it takes more effort to build flexibility into any product, and there are usually performance tradeoffs too, as complexity increases. On the other hand, the folks on this forum have been at this a long time, and I'm sure their views are very well founded. Thanks, oldblindpew "Underwriting" wrote: Pew, We have one Policies record for each insured. It contains fields for the policies we provide (GL, Work Comp, Property, auto, etc.); carrier, policy number, limits, policy dates, etc. The messier bits of information, such as additional insured wording, special certificate wording, etc. is entered in the actual Certificate table. This info is either pulled automatically from the Certificate Holder's client record, or it can be entered manually for unusual circumstances. Our application was created in-house. We have been using/improving it since 1998. We keep 'insurance required' data (specific additional insured wording, minimum coverage requirements, etc.) with the Certificate Holder record. The 'insurance provided' info is what we input in the Policies table for that particular insured. "oldblindpew" wrote: Thanks for chiming in. You said you have "a child table called Policies". A table is a place for records that share a common set of fields. How do you handle the fact that different types of policies have different sets of descriptive fields? Do you 1) perhaps have separate child tables for GL, Auto, Excess, and Worker's Comp Policies? Or, 2) perhaps have one table for common policy information, and another table or tables for the messier bits of information? Or 3) maybe your Policies table is extremely wide and has fields for almost every bit of information on the Acord form? This last case is what I started out doing, and in fact had actually built the tables and forms for both the Insurance Required and Insurance Provided. I was exploring how to go about checking provided coverage for compliance with required coverage when I learned that my approach was not recommended. Is your application something homemade, or is it a commercial product or a trade association project? Thanks, Pew "Underwriting" wrote: Let me preface by saying this thread is way, way, over my head. :-) But I want to let you know how we issue our certificates of insurance. We are a special events firm insuring concerts, fairs, sporting events, etc. We have one table called Clients which contains both our insureds and the companies we may be issuing certificates to (certificate holders). Each record has a class field. So our insured would be classified as Customer. The certificate holder would be classified as Venue, etc. We then have a child table called Policies. This contains all of the policy info for a particular insured; carrier, policy number, limits, etc. This table is updated manually as policies are renewed. Finally we have a table called Certificates. When our insured requests a certificate, we create a record in this table which pulls in insured name/address info from Clients, venue info and additional insured wording from Clients, and policy info from the Policies table. We then complete it with any special wording (more additional insureds, any special conditions, etc.) the certificate holder may require. |
|
Thread Tools | |
Display Modes | |
|
|