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
|
|||
|
|||
One-To-One Tables
This may prove to be an open-ended question, but here goes. I'm anticipating
having three tables with one-to-one relationships back to another table. All of this information could therefore be placed in one massive table, but somehow I feel it would be more manageable if separated. The question is, should I key all three tables back to the main table, or should I key the main to the first, the first to the second, and the second to the third? Does it matter? More info: The main table is Agreements. The sub-tables are Requirements, Certificates, and Validations. Each Agreement imposes a set of Requirements. A Certificate lists what is being offered to meet those Requirements, and a Validation checks what is being offered against what is being required, field by field. So you could say Agreements result in Requirements, which result in Certificates, which result in Validations. Or, you could say for each Agreement there is a Requirement, a Certificate, and a Validation. There is truth in both statements as all of these are interrelated. I'm just afraid this will be another one of those cases where you have a choice, and it doesn't really seem to matter, but one day long afterwards you find out that despite all your caution you made the wrong choice. |
#2
|
|||
|
|||
One-To-One Tables
Seems to me if an Agreement imposes a set of Requirements, then you have a
One-to-Many relationship. Is this correct? Hopefully you aren't putting Requirements in a table with fields something like Req1, Req2, and so on. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "oldblindpew" wrote: This may prove to be an open-ended question, but here goes. I'm anticipating having three tables with one-to-one relationships back to another table. All of this information could therefore be placed in one massive table, but somehow I feel it would be more manageable if separated. The question is, should I key all three tables back to the main table, or should I key the main to the first, the first to the second, and the second to the third? Does it matter? More info: The main table is Agreements. The sub-tables are Requirements, Certificates, and Validations. Each Agreement imposes a set of Requirements. A Certificate lists what is being offered to meet those Requirements, and a Validation checks what is being offered against what is being required, field by field. So you could say Agreements result in Requirements, which result in Certificates, which result in Validations. Or, you could say for each Agreement there is a Requirement, a Certificate, and a Validation. There is truth in both statements as all of these are interrelated. I'm just afraid this will be another one of those cases where you have a choice, and it doesn't really seem to matter, but one day long afterwards you find out that despite all your caution you made the wrong choice. |
#3
|
|||
|
|||
One-To-One Tables
On Tue, 25 Aug 2009 10:59:01 -0700, oldblindpew
wrote: This may prove to be an open-ended question, but here goes. I'm anticipating having three tables with one-to-one relationships back to another table. One to one relationships are VERY rare. See below. All of this information could therefore be placed in one massive table, but somehow I feel it would be more manageable if separated. The question is, should I key all three tables back to the main table, or should I key the main to the first, the first to the second, and the second to the third? Does it matter? If the reality is that there is a "master" table and two "Child" tables, then relate each child to the master. If the third table logically relates to data in the second table, then cascade. It depends on the logical structure of the data. More info: The main table is Agreements. The sub-tables are Requirements, Certificates, and Validations. Each Agreement imposes a set of Requirements. But a one to one relationship implies that an Agreement can have zero or one Requirement, never more. Are you perhaps using one *FIELD* per requirement? If so you're not normalized. A Certificate lists what is being offered to meet those Requirements, and a Validation checks what is being offered against what is being required, field by field. Not record by record, using a one to many relationship? So you could say Agreements result in Requirements, which result in Certificates, which result in Validations. Or, you could say for each Agreement there is a Requirement, a Certificate, and a Validation. There is truth in both statements as all of these are interrelated. I'm just afraid this will be another one of those cases where you have a choice, and it doesn't really seem to matter, but one day long afterwards you find out that despite all your caution you made the wrong choice. Just be sure you don't have repeating groups, to start with! Perhaps you could post (real or hypothetical) sample data and indicate the field structures of your tables. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
One-To-One Tables
Thanks for your reply. I don't think the one-to-many is correct. It is true
that one Agreement imposes many requirements, however, the requirements consist of a standard set of categories. There will be a set group of fields making up the Requirements Table, with the values in those fields varying from one Agreement to the next. This is really about subcontractor's insurance, but to simplify, let's say we're talking about furnishing cars. One Agreement may require a Yellow, 2-door, Chevy. Another Agreement may require a Red, 4-door, Ford. The Requirements table would have fields for Color, Number of Doors, and Make. Field values would be dictated by each Agreement. A Certificate would be furnished describing the actual car being offered in fulfillment of the Agreement. The Validation would show how close a match was achieved between the Requirement and the Offering. "Jerry Whittle" wrote: Seems to me if an Agreement imposes a set of Requirements, then you have a One-to-Many relationship. Is this correct? Hopefully you aren't putting Requirements in a table with fields something like Req1, Req2, and so on. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "oldblindpew" wrote: This may prove to be an open-ended question, but here goes. I'm anticipating having three tables with one-to-one relationships back to another table. All of this information could therefore be placed in one massive table, but somehow I feel it would be more manageable if separated. The question is, should I key all three tables back to the main table, or should I key the main to the first, the first to the second, and the second to the third? Does it matter? More info: The main table is Agreements. The sub-tables are Requirements, Certificates, and Validations. Each Agreement imposes a set of Requirements. A Certificate lists what is being offered to meet those Requirements, and a Validation checks what is being offered against what is being required, field by field. So you could say Agreements result in Requirements, which result in Certificates, which result in Validations. Or, you could say for each Agreement there is a Requirement, a Certificate, and a Validation. There is truth in both statements as all of these are interrelated. I'm just afraid this will be another one of those cases where you have a choice, and it doesn't really seem to matter, but one day long afterwards you find out that despite all your caution you made the wrong choice. |
#5
|
|||
|
|||
One-To-One Tables
Thanks for your reply. See also my response to Jerry Whittle.
This is about Certificates of Insurance furnished by subcontractors. Much of my design is driven by the standard certificate form commonly seen in the industry. This certificate form has lots of fields on it, and in order to validate the certificate, each field has to be checked. My approach has been to create one table for the certificate information and another similar table to store the results of the validation, if ever I can get there. To check or validate the certificate, there must be requirements to check against. Originally I had a third table consisting of three records defining three standard sets of insurance requirements, because we took sort of a "Small, Medum, Large" approach to our requirements. Each Agreement pointed to one of these three standard sets of requirements. But since it is possible for any given agreement to modify or waive any given insurance parameter, it seems better to let each agreement have its own unique set of insurance requirements. Thus I arrive at three tables each relating back to the Agreements table in a one-to-one fashion. In order to ease the task of establishing the insurance requirements for each agreement, I'll need to figure out how to enable the user to copy a requirements record from one of the three standards, or from an existing agreement, and then make whatever modifications may be called for by the Agreement. I appreciate your taking time to consider this and offer advice on whether it matters how the tables are linked. Or that the whole effort would be better served by a totally different approach. --Thanks, OBP "John W. Vinson" wrote: On Tue, 25 Aug 2009 10:59:01 -0700, oldblindpew wrote: This may prove to be an open-ended question, but here goes. I'm anticipating having three tables with one-to-one relationships back to another table. One to one relationships are VERY rare. See below. All of this information could therefore be placed in one massive table, but somehow I feel it would be more manageable if separated. The question is, should I key all three tables back to the main table, or should I key the main to the first, the first to the second, and the second to the third? Does it matter? If the reality is that there is a "master" table and two "Child" tables, then relate each child to the master. If the third table logically relates to data in the second table, then cascade. It depends on the logical structure of the data. More info: The main table is Agreements. The sub-tables are Requirements, Certificates, and Validations. Each Agreement imposes a set of Requirements. But a one to one relationship implies that an Agreement can have zero or one Requirement, never more. Are you perhaps using one *FIELD* per requirement? If so you're not normalized. A Certificate lists what is being offered to meet those Requirements, and a Validation checks what is being offered against what is being required, field by field. Not record by record, using a one to many relationship? So you could say Agreements result in Requirements, which result in Certificates, which result in Validations. Or, you could say for each Agreement there is a Requirement, a Certificate, and a Validation. There is truth in both statements as all of these are interrelated. I'm just afraid this will be another one of those cases where you have a choice, and it doesn't really seem to matter, but one day long afterwards you find out that despite all your caution you made the wrong choice. Just be sure you don't have repeating groups, to start with! Perhaps you could post (real or hypothetical) sample data and indicate the field structures of your tables. -- John W. Vinson [MVP] |
#6
|
|||
|
|||
One-To-One Tables
On Tue, 25 Aug 2009 15:18:11 -0700, oldblindpew
wrote: Thanks for your reply. See also my response to Jerry Whittle. This is about Certificates of Insurance furnished by subcontractors. Much of my design is driven by the standard certificate form commonly seen in the industry. This certificate form has lots of fields on it, and in order to validate the certificate, each field has to be checked. My approach has been to create one table for the certificate information and another similar table to store the results of the validation, if ever I can get there. To check or validate the certificate, there must be requirements to check against. Originally I had a third table consisting of three records defining three standard sets of insurance requirements, because we took sort of a "Small, Medum, Large" approach to our requirements. Each Agreement pointed to one of these three standard sets of requirements. But since it is possible for any given agreement to modify or waive any given insurance parameter, it seems better to let each agreement have its own unique set of insurance requirements. Thus I arrive at three tables each relating back to the Agreements table in a one-to-one fashion. In order to ease the task of establishing the insurance requirements for each agreement, I'll need to figure out how to enable the user to copy a requirements record from one of the three standards, or from an existing agreement, and then make whatever modifications may be called for by the Agreement. I appreciate your taking time to consider this and offer advice on whether it matters how the tables are linked. Or that the whole effort would be better served by a totally different approach. --Thanks, OBP I think it WOULD be better served by a totally differnent approach - a normalized Access database. g It sounds very much to me like you have a very straightforward Many to Many relationship between Certificates and Requirements. Each certificate has many requirements; each requirement may occur on many Certificates. You're solving the problem using spreadsheet logic: one field (column) for each requirement. This is not ideal in Access! If your "Small" template has (say) seventeen requirements, then you could have seventeen *RECORDS* in a CertRequirements table, linked to this particular subcontractor's certificate, and to a table of all the possible requirements that might arise (seventeen of them from seventeen different records, in this instance). This would let you add new requirements, waive individual requirements, add modifying notes or comments to a requirement, etc. etc. -- John W. Vinson [MVP] |
#7
|
|||
|
|||
One-To-One Tables
You refused to respond back to my response so I am withdrawing from further
response to you!!!! Steve "oldblindpew" wrote in message ... This may prove to be an open-ended question, but here goes. I'm anticipating having three tables with one-to-one relationships back to another table. All of this information could therefore be placed in one massive table, but somehow I feel it would be more manageable if separated. The question is, should I key all three tables back to the main table, or should I key the main to the first, the first to the second, and the second to the third? Does it matter? More info: The main table is Agreements. The sub-tables are Requirements, Certificates, and Validations. Each Agreement imposes a set of Requirements. A Certificate lists what is being offered to meet those Requirements, and a Validation checks what is being offered against what is being required, field by field. So you could say Agreements result in Requirements, which result in Certificates, which result in Validations. Or, you could say for each Agreement there is a Requirement, a Certificate, and a Validation. There is truth in both statements as all of these are interrelated. I'm just afraid this will be another one of those cases where you have a choice, and it doesn't really seem to matter, but one day long afterwards you find out that despite all your caution you made the wrong choice. |
#8
|
|||
|
|||
One-To-One Tables
"Steve" schreef in bericht ... You refused to respond back to my response so I am withdrawing from further response to you!!!! Steve Calm down you idiot !! Remember: No-one wants you here.... no-one needs you here... But you are becoming famous nevertheless... The stats for http://home.tiscali.nl/arracom/whoissteve.html are still increasing daily... Arno R |
#9
|
|||
|
|||
One-To-One Tables
"Steve" wrote in message
... Actually you don't have one-to-one relationships! Don't you ever get tired of being wrong? |
#10
|
|||
|
|||
One-To-One Tables
I'm sorry, but I don't know who you are or what responses your are referring
to. --OBP "Steve" wrote: You refused to respond back to my response so I am withdrawing from further response to you!!!! Steve "oldblindpew" wrote in message ... This may prove to be an open-ended question, but here goes. I'm anticipating having three tables with one-to-one relationships back to another table. All of this information could therefore be placed in one massive table, but somehow I feel it would be more manageable if separated. The question is, should I key all three tables back to the main table, or should I key the main to the first, the first to the second, and the second to the third? Does it matter? More info: The main table is Agreements. The sub-tables are Requirements, Certificates, and Validations. Each Agreement imposes a set of Requirements. A Certificate lists what is being offered to meet those Requirements, and a Validation checks what is being offered against what is being required, field by field. So you could say Agreements result in Requirements, which result in Certificates, which result in Validations. Or, you could say for each Agreement there is a Requirement, a Certificate, and a Validation. There is truth in both statements as all of these are interrelated. I'm just afraid this will be another one of those cases where you have a choice, and it doesn't really seem to matter, but one day long afterwards you find out that despite all your caution you made the wrong choice. |
Thread Tools | |
Display Modes | |
|
|