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 |
#21
|
|||
|
|||
Avoiding One to One Tables
Gina and Fred,
Yes, I hear you. Naming is a problem because structure is a problem. With the spreadsheet approach, I knew I needed a Requirements table, a Certificates table, and a Validation table. Once I departed from this and tried to normalize, it led to confusion about what my entities were and therefore what they should be named. It doesn't help that there seems to be a lack of good candidates for names. I think that insurance elements, or entities or "things", outside the context of any specific Agreement or Certificate, should be called Insurance Parameters, and should go in a table of Insurance Parameters with one record for each parameter. Joining a Parameter with an Agreement and adding a "Required Value" field gives you an Insurance Requirement, so the first impluse was to call the join table an Insurance Requirements table. However, adding a "Provided Value" field makes this same join table begin to look like a collection of Certificate of Insurance items, with built-in references back to the Required Value imposed by the Agreement. So my second impulse was to call the join table a Certificates table. This was also somewhat motivated by difficulty in getting people to understand what I meant by "Requirements" as an entity. More recently I have been thinking of it as a "Validation" table, because each record ultimately brings together a Requirement and a Certificate offering, to see if they agree. -Pew "Gina Whipp" wrote: Just peeking in... Thank you Fred! Peanut Gallery... Certificates = Requirements??? Using the same term is VERY important. Could be one of the reasons we are all getting confused and have to keep asking more questions. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Fred" wrote in message ... Hello Pew, A couple of notes - this is a complex application with a lot a good ideas in a lot of threads. I really am not able to spend the time to absorb them all. Gina is an overall Access Goddess. You can't go wrong by listening to her. Just make sure you communicate cleqrly by defining and consistently using your you-specific terms. My strength is structure, and heavy use of Access in things that I run (companies) or rund data for (organizations). I'm not a developer. First, there's one area of confusion. Now you said: joins these together. You suggested calling it "Requirements"; I called it "Certificates". This conflicts with what I think that you said previously (and which I was going by) which was the each agreement/subcontract has ONE certificate and many requirements. In my method, you only combined coverage into the same requirement record when the TYPE matched. Otherwise they are seperate until reconciled or combined. Dates of coverage can be added fields in both....just use consistend definitions. Again, I think that in this case going by the book (normalizing) is the best way to serve YOU and YOUR NEEDS. Nothing to do with making it easy for Access or being a purist. |
#22
|
|||
|
|||
Avoiding One to One Tables
Hello Gina,
On a lot of structure questions in the forum the situation is that someone is so close to or so expert with their business or process that they can get by with using the same noun to refer to, what is (in DB structure) multiple different types of entities, or to in essence use (in DB structure terms) definitions that can vary. Works fine until one needs to create a DB structure while trying to use those "nouns", or rigorously communicate the situation to this forum. Not sure whether or not such is the case here. Fred |
#23
|
|||
|
|||
Avoiding One to One Tables
Fred,
Good Point! Also, not sure... -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Fred" wrote in message ... Hello Gina, On a lot of structure questions in the forum the situation is that someone is so close to or so expert with their business or process that they can get by with using the same noun to refer to, what is (in DB structure) multiple different types of entities, or to in essence use (in DB structure terms) definitions that can vary. Works fine until one needs to create a DB structure while trying to use those "nouns", or rigorously communicate the situation to this forum. Not sure whether or not such is the case here. Fred |
#24
|
|||
|
|||
Avoiding One to One Tables
Hello Pew,
Didn't know that you posted after I wrote but before I posted the last one. I think that my previously recommended nsuranceRequirementsAndOrInsuranceItemsProvided table (of course, shorten that name) is the same as your "validations" table idea. Sincerely, Fred |
#25
|
|||
|
|||
Avoiding One to One Tables
On Tue, 15 Sep 2009 14:34:05 -0700, oldblindpew wrote:
Gina and Fred, Yes, I hear you. Naming is a problem because structure is a problem. With the spreadsheet approach, I knew I needed a Requirements table, a Certificates table, and a Validation table. Once I departed from this and tried to normalize, it led to confusion about what my entities were and therefore what they should be named. It doesn't help that there seems to be a lack of good candidates for names. I think that insurance elements, or entities or "things", outside the context of any specific Agreement or Certificate, should be called Insurance Parameters, and should go in a table of Insurance Parameters with one record for each parameter. Joining a Parameter with an Agreement and adding a "Required Value" field gives you an Insurance Requirement, so the first impluse was to call the join table an Insurance Requirements table. However, adding a "Provided Value" field makes this same join table begin to look like a collection of Certificate of Insurance items, with built-in references back to the Required Value imposed by the Agreement. So my second impulse was to call the join table a Certificates table. This was also somewhat motivated by difficulty in getting people to understand what I meant by "Requirements" as an entity. More recently I have been thinking of it as a "Validation" table, because each record ultimately brings together a Requirement and a Certificate offering, to see if they agree. -Pew "Gina Whipp" wrote: Just peeking in... Thank you Fred! Peanut Gallery... Certificates = Requirements??? Using the same term is VERY important. Could be one of the reasons we are all getting confused and have to keep asking more questions. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Fred" wrote in message ... Hello Pew, A couple of notes - this is a complex application with a lot a good ideas in a lot of threads. I really am not able to spend the time to absorb them all. Gina is an overall Access Goddess. You can't go wrong by listening to her. Just make sure you communicate cleqrly by defining and consistently using your you-specific terms. My strength is structure, and heavy use of Access in things that I run (companies) or rund data for (organizations). I'm not a developer. First, there's one area of confusion. Now you said: joins these together. You suggested calling it "Requirements"; I called it "Certificates". This conflicts with what I think that you said previously (and which I was going by) which was the each agreement/subcontract has ONE certificate and many requirements. In my method, you only combined coverage into the same requirement record when the TYPE matched. Otherwise they are seperate until reconciled or combined. Dates of coverage can be added fields in both....just use consistend definitions. Again, I think that in this case going by the book (normalizing) is the best way to serve YOU and YOUR NEEDS. Nothing to do with making it easy for Access or being a purist. This thread has been going on a long time, so I thought I would add my 2 cents worth: CREATE TABLE Firms ( firm_no LONG NOT NULL, firm_name TEXT(50) NOT NULL, PRIMARY KEY (firm_no) ); CREATE TABLE Agreements ( agreement_no LONG NOT NULL, firm_no LONG NOT NULL REFERENCES Firms (firm_no), agreement_date DATETIME NOT NULL, start_by_date DATETIME NOT NULL, finish_by_date DATETIME NOT NULL, PRIMARY KEY (agreement_no) ); CREATE TABLE [Insurance Options Master List] ( option_no LONG NOT NULL, option_name TEXT(50) NOT NULL, PRIMARY KEY (option_no) ); CREATE TABLE [Agreement Options] ( agreement_no LONG NOT NULL REFERENCES Agreements (agreement_no), option_no LONG NOT NULL REFERENCES [Insurance Options Master List] (option_no), coverage_amount CURRENCY NOT NULL, PRIMARY KEY (agreement_no, option_no) ); CREATE TABLE [Insurance Policies] ( policy_no LONG NOT NULL, firm_no LONG NOT NULL REFERENCES Firms (firm_no), issuer_name TEXT(50) NOT NULL, effective_date DATETIME, termination_date DATETIME, PRIMARY KEY (policy_no, firm_no) ); CREATE TABLE [Policy Options] ( policy_no LONG NULL, firm_no LONG NOT NULL, FOREIGN KEY (policy_no, firm_no) REFERENCES [Insurance Policies] (policy_no, firm_no), option_no LONG NOT NULL REFERENCES [Insurance Options Master List] (option_no), coverage_amount CURRENCY NOT NULL, PRIMARY KEY (policy_no, firm_no, option_no) ); |
#26
|
|||
|
|||
Avoiding One to One Tables
Hello Michael,
Thanks for your suggestions. I'm sorry this has become such a bother, and I appreciate your input. It looks like you are introducing the term "Options" vs my "Parameters", which has the advantage of being shorter. You are also first to suggest a separate table for Policies, although I think for my purposes, Policies and Certificates can be viewed as the same thing. A Certificate summarizes the coverage provided by a Policy. The advantage of a separate table for Certificates/Policies is that it is a parent table, where one can store stuff that doesn't seem to fit well into a detail/join table, such as expiration dates. You are suggesting two join tables, one for Agreements/Options, and another for Policies/Options. This would put what I've called Insurance Requirements in the first table, and Insurance Offerings in the second. I'm a little fuzzy on how to compare the two, for validation. Also you seem to assume that all insurance options are quanitified as dollar amounts (coverage_amount), whereas I've been looking at insurance options as representing four different data types. This last point is, I think, right at the heart of my normalization dilemma. I've been considering trying to reduce the number of data types by treating everything except dates as integers. I'm fuzzy again on how my validation procedure would know what to do with these integer values, in other words, whether to check for exact match, or to check for greater-than-or-equal-to. I suppose I could assume that regardless of structure, there will always be a way somehow to write whatever procedure is needed, but I've been burned by this before. I've altered structure in the past only to find that I had to devise entirely different code in VBA to restore functionality. I'm probably going to have to shelve this project again to work on other things for a while. This news may come as a relief to us all. Thanks again, everyone. --Pew "Michael Gramelspacher" wrote: On Tue, 15 Sep 2009 14:34:05 -0700, oldblindpew wrote: Gina and Fred, Yes, I hear you. Naming is a problem because structure is a problem. With the spreadsheet approach, I knew I needed a Requirements table, a Certificates table, and a Validation table. Once I departed from this and tried to normalize, it led to confusion about what my entities were and therefore what they should be named. It doesn't help that there seems to be a lack of good candidates for names. I think that insurance elements, or entities or "things", outside the context of any specific Agreement or Certificate, should be called Insurance Parameters, and should go in a table of Insurance Parameters with one record for each parameter. Joining a Parameter with an Agreement and adding a "Required Value" field gives you an Insurance Requirement, so the first impluse was to call the join table an Insurance Requirements table. However, adding a "Provided Value" field makes this same join table begin to look like a collection of Certificate of Insurance items, with built-in references back to the Required Value imposed by the Agreement. So my second impulse was to call the join table a Certificates table. This was also somewhat motivated by difficulty in getting people to understand what I meant by "Requirements" as an entity. More recently I have been thinking of it as a "Validation" table, because each record ultimately brings together a Requirement and a Certificate offering, to see if they agree. -Pew "Gina Whipp" wrote: Just peeking in... Thank you Fred! Peanut Gallery... Certificates = Requirements??? Using the same term is VERY important. Could be one of the reasons we are all getting confused and have to keep asking more questions. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Fred" wrote in message ... Hello Pew, A couple of notes - this is a complex application with a lot a good ideas in a lot of threads. I really am not able to spend the time to absorb them all. Gina is an overall Access Goddess. You can't go wrong by listening to her. Just make sure you communicate cleqrly by defining and consistently using your you-specific terms. My strength is structure, and heavy use of Access in things that I run (companies) or rund data for (organizations). I'm not a developer. First, there's one area of confusion. Now you said: joins these together. You suggested calling it "Requirements"; I called it "Certificates". This conflicts with what I think that you said previously (and which I was going by) which was the each agreement/subcontract has ONE certificate and many requirements. In my method, you only combined coverage into the same requirement record when the TYPE matched. Otherwise they are seperate until reconciled or combined. Dates of coverage can be added fields in both....just use consistend definitions. Again, I think that in this case going by the book (normalizing) is the best way to serve YOU and YOUR NEEDS. Nothing to do with making it easy for Access or being a purist. This thread has been going on a long time, so I thought I would add my 2 cents worth: CREATE TABLE Firms ( firm_no LONG NOT NULL, firm_name TEXT(50) NOT NULL, PRIMARY KEY (firm_no) ); CREATE TABLE Agreements ( agreement_no LONG NOT NULL, firm_no LONG NOT NULL REFERENCES Firms (firm_no), agreement_date DATETIME NOT NULL, start_by_date DATETIME NOT NULL, finish_by_date DATETIME NOT NULL, PRIMARY KEY (agreement_no) ); CREATE TABLE [Insurance Options Master List] ( option_no LONG NOT NULL, option_name TEXT(50) NOT NULL, PRIMARY KEY (option_no) ); CREATE TABLE [Agreement Options] ( agreement_no LONG NOT NULL REFERENCES Agreements (agreement_no), option_no LONG NOT NULL REFERENCES [Insurance Options Master List] (option_no), coverage_amount CURRENCY NOT NULL, PRIMARY KEY (agreement_no, option_no) ); CREATE TABLE [Insurance Policies] ( policy_no LONG NOT NULL, firm_no LONG NOT NULL REFERENCES Firms (firm_no), issuer_name TEXT(50) NOT NULL, effective_date DATETIME, termination_date DATETIME, PRIMARY KEY (policy_no, firm_no) ); CREATE TABLE [Policy Options] ( policy_no LONG NULL, firm_no LONG NOT NULL, FOREIGN KEY (policy_no, firm_no) REFERENCES [Insurance Policies] (policy_no, firm_no), option_no LONG NOT NULL REFERENCES [Insurance Options Master List] (option_no), coverage_amount CURRENCY NOT NULL, PRIMARY KEY (policy_no, firm_no, option_no) ); |
#27
|
|||
|
|||
Avoiding One to One Tables
OBP,
Not a relief... I myself was enjoying the exchange. Hopefully, we didn't *scare* you off! -- Gina Whipp "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 ... Hello Michael, Thanks for your suggestions. I'm sorry this has become such a bother, and I appreciate your input. It looks like you are introducing the term "Options" vs my "Parameters", which has the advantage of being shorter. You are also first to suggest a separate table for Policies, although I think for my purposes, Policies and Certificates can be viewed as the same thing. A Certificate summarizes the coverage provided by a Policy. The advantage of a separate table for Certificates/Policies is that it is a parent table, where one can store stuff that doesn't seem to fit well into a detail/join table, such as expiration dates. You are suggesting two join tables, one for Agreements/Options, and another for Policies/Options. This would put what I've called Insurance Requirements in the first table, and Insurance Offerings in the second. I'm a little fuzzy on how to compare the two, for validation. Also you seem to assume that all insurance options are quanitified as dollar amounts (coverage_amount), whereas I've been looking at insurance options as representing four different data types. This last point is, I think, right at the heart of my normalization dilemma. I've been considering trying to reduce the number of data types by treating everything except dates as integers. I'm fuzzy again on how my validation procedure would know what to do with these integer values, in other words, whether to check for exact match, or to check for greater-than-or-equal-to. I suppose I could assume that regardless of structure, there will always be a way somehow to write whatever procedure is needed, but I've been burned by this before. I've altered structure in the past only to find that I had to devise entirely different code in VBA to restore functionality. I'm probably going to have to shelve this project again to work on other things for a while. This news may come as a relief to us all. Thanks again, everyone. --Pew "Michael Gramelspacher" wrote: On Tue, 15 Sep 2009 14:34:05 -0700, oldblindpew wrote: Gina and Fred, Yes, I hear you. Naming is a problem because structure is a problem. With the spreadsheet approach, I knew I needed a Requirements table, a Certificates table, and a Validation table. Once I departed from this and tried to normalize, it led to confusion about what my entities were and therefore what they should be named. It doesn't help that there seems to be a lack of good candidates for names. I think that insurance elements, or entities or "things", outside the context of any specific Agreement or Certificate, should be called Insurance Parameters, and should go in a table of Insurance Parameters with one record for each parameter. Joining a Parameter with an Agreement and adding a "Required Value" field gives you an Insurance Requirement, so the first impluse was to call the join table an Insurance Requirements table. However, adding a "Provided Value" field makes this same join table begin to look like a collection of Certificate of Insurance items, with built-in references back to the Required Value imposed by the Agreement. So my second impulse was to call the join table a Certificates table. This was also somewhat motivated by difficulty in getting people to understand what I meant by "Requirements" as an entity. More recently I have been thinking of it as a "Validation" table, because each record ultimately brings together a Requirement and a Certificate offering, to see if they agree. -Pew "Gina Whipp" wrote: Just peeking in... Thank you Fred! Peanut Gallery... Certificates = Requirements??? Using the same term is VERY important. Could be one of the reasons we are all getting confused and have to keep asking more questions. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Fred" wrote in message ... Hello Pew, A couple of notes - this is a complex application with a lot a good ideas in a lot of threads. I really am not able to spend the time to absorb them all. Gina is an overall Access Goddess. You can't go wrong by listening to her. Just make sure you communicate cleqrly by defining and consistently using your you-specific terms. My strength is structure, and heavy use of Access in things that I run (companies) or rund data for (organizations). I'm not a developer. First, there's one area of confusion. Now you said: joins these together. You suggested calling it "Requirements"; I called it "Certificates". This conflicts with what I think that you said previously (and which I was going by) which was the each agreement/subcontract has ONE certificate and many requirements. In my method, you only combined coverage into the same requirement record when the TYPE matched. Otherwise they are seperate until reconciled or combined. Dates of coverage can be added fields in both....just use consistend definitions. Again, I think that in this case going by the book (normalizing) is the best way to serve YOU and YOUR NEEDS. Nothing to do with making it easy for Access or being a purist. This thread has been going on a long time, so I thought I would add my 2 cents worth: CREATE TABLE Firms ( firm_no LONG NOT NULL, firm_name TEXT(50) NOT NULL, PRIMARY KEY (firm_no) ); CREATE TABLE Agreements ( agreement_no LONG NOT NULL, firm_no LONG NOT NULL REFERENCES Firms (firm_no), agreement_date DATETIME NOT NULL, start_by_date DATETIME NOT NULL, finish_by_date DATETIME NOT NULL, PRIMARY KEY (agreement_no) ); CREATE TABLE [Insurance Options Master List] ( option_no LONG NOT NULL, option_name TEXT(50) NOT NULL, PRIMARY KEY (option_no) ); CREATE TABLE [Agreement Options] ( agreement_no LONG NOT NULL REFERENCES Agreements (agreement_no), option_no LONG NOT NULL REFERENCES [Insurance Options Master List] (option_no), coverage_amount CURRENCY NOT NULL, PRIMARY KEY (agreement_no, option_no) ); CREATE TABLE [Insurance Policies] ( policy_no LONG NOT NULL, firm_no LONG NOT NULL REFERENCES Firms (firm_no), issuer_name TEXT(50) NOT NULL, effective_date DATETIME, termination_date DATETIME, PRIMARY KEY (policy_no, firm_no) ); CREATE TABLE [Policy Options] ( policy_no LONG NULL, firm_no LONG NOT NULL, FOREIGN KEY (policy_no, firm_no) REFERENCES [Insurance Policies] (policy_no, firm_no), option_no LONG NOT NULL REFERENCES [Insurance Options Master List] (option_no), coverage_amount CURRENCY NOT NULL, PRIMARY KEY (policy_no, firm_no, option_no) ); |
Thread Tools | |
Display Modes | |
|
|