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
|
|||
|
|||
One-To-One Tables
"Steve" schreef in bericht ... Okay, Add another table to my suggested tables ... TblAgreementRequirement AgreementRequirementID AgreementID RequirementID RequirementMet (Yes/No) The RequirementMet field is your validation. You don't need a validation table. Steve Get lost $teve, No-one wants you here... no-one needs you here... OP look at http://home.tiscali.nl/arracom/whoissteve.html Arno R |
#22
|
|||
|
|||
One-To-One Tables
John, Jerry, Steve:
I THINK I'VE GOT IT!! It came to me last night driving home from work. I need the Agreements table and the Requirements table in a many-to-many relationship, with the Certificates table serving as the junction table. But since the Requirements table will actually do double duty as providing both insurance requirement values and certificate offering values, I will refer to it as the Insurance Parameters table instead. TblAgreements AgreementID Agreement fields TblInsParameters InsParameterID InsParameterDescrip Other fields, if any TblCertificates CertificateID AgreementID InsRequirementID (same as InsParameterID) InsOfferingID (same as InsParameterID) So if the InsOfferingID and the InsRequirementID match, then the Certificate is valid for that one particular parameter. Now, it is not clear from my reference books whether a junction table must use a composite primary key consisting of values that match the primary keys from the two joined tables, or, whether you are free to use a separate primary key. I hope the latter is the case, because I've grown prejudiced against composite keys. I wasn't sure where in the thread to put this posting, so I put it here in hopes you all find it. Thanks for your suggestions, and I would appreciate any further suggestions, corrections or advice you may have to offer on this plan of attack. I'm sure as I get on with this there will be plenty of unforeseen obstacles to negotiate. --OBP "John W. Vinson" wrote: 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] |
#23
|
|||
|
|||
One-To-One Tables
On Thu, 27 Aug 2009 07:42:06 -0700, oldblindpew
wrote: Now, it is not clear from my reference books whether a junction table must use a composite primary key consisting of values that match the primary keys from the two joined tables, or, whether you are free to use a separate primary key. I hope the latter is the case, because I've grown prejudiced against composite keys. You can indeed put in an autonumber primary key as a surrogate; I would recommend putting a unique Index on the combination of the two fields to prevent inadvertant duplicates. Glad you got it working. -- John W. Vinson [MVP] |
#24
|
|||
|
|||
One-To-One Tables
Well, it isn't working yet, but at least I have a plan, thanks largely to
you. If you hadn't replied to my original question, I would probably still be thinking of this like a spreadsheet, which might have proven functional, but not optimal. I will pay close attention to your advice about the Index, also. Thanks again and again! -OBP "John W. Vinson" wrote: On Thu, 27 Aug 2009 07:42:06 -0700, oldblindpew wrote: Now, it is not clear from my reference books whether a junction table must use a composite primary key consisting of values that match the primary keys from the two joined tables, or, whether you are free to use a separate primary key. I hope the latter is the case, because I've grown prejudiced against composite keys. You can indeed put in an autonumber primary key as a surrogate; I would recommend putting a unique Index on the combination of the two fields to prevent inadvertant duplicates. Glad you got it working. -- John W. Vinson [MVP] |
#25
|
|||
|
|||
One-To-One Tables
=?Utf-8?B?b2xkYmxpbmRwZXc=?=
wrote in : Now, it is not clear from my reference books whether a junction table must use a composite primary key consisting of values that match the primary keys from the two joined tables, or, whether you are free to use a separate primary key. I hope the latter is the case, because I've grown prejudiced against composite keys. I would never use a surrogate key in a join table unless there were tables related to the PK of the join table. For instance, if you have a junction table between person and employer and you need to have multiple titles for each of the records in the junction table, then you'd need to store the junction table's PK in the storing the position titles. tblPerson PK PersonID tblCompany PK CompanyID tblPersonCompany PK PersonID+CompanyID, FK PersonID, FK CompanyID tblTitle PK TitleID, FK PersonID+CompanyID versus tblPerson PK PersonID tblCompany PK CompanyID tblPersonCompany PK PersonCompanyID, FK PersonID, FK CompanyID tblTitle PK TitleID, FK PersonCompanyID But this is what I consider an unusual case. It's very seldom that you need a many-to-one relationship beween a child table and have a junction table be the parent record. I had to strain to come up with such a situation, and I've encountered it only a few times in my 15+ years of database application design. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#26
|
|||
|
|||
One-To-One Tables
Hi, David, and thanks for your reply.
Side-issue: Person-to-Company sounds like a many-to-one relationship. I thought the purpose of join tables was to handle many-to-many relationships. Main issue: I like the concept of composite keys, but I read somewhere on the internet from one published expert that composite keys should be avoided because they create problems. My own experience is that even though Access knows that a composite key is two fields that sometimes act as one, Access apparently does not provide ready-made support for this reality. For example, I like to use a list box as a navigation tool to allow the user to pick a record, but this won't work with composite keys because the list box is incapable of passing the value of more than one field. Another question: In your first example, does your junction table actually store PersonID and CompanyID twice, once as PK and again as FK? Thanks, OBP "David W. Fenton" wrote: =?Utf-8?B?b2xkYmxpbmRwZXc=?= wrote in : Now, it is not clear from my reference books whether a junction table must use a composite primary key consisting of values that match the primary keys from the two joined tables, or, whether you are free to use a separate primary key. I hope the latter is the case, because I've grown prejudiced against composite keys. I would never use a surrogate key in a join table unless there were tables related to the PK of the join table. For instance, if you have a junction table between person and employer and you need to have multiple titles for each of the records in the junction table, then you'd need to store the junction table's PK in the storing the position titles. tblPerson PK PersonID tblCompany PK CompanyID tblPersonCompany PK PersonID+CompanyID, FK PersonID, FK CompanyID tblTitle PK TitleID, FK PersonID+CompanyID versus tblPerson PK PersonID tblCompany PK CompanyID tblPersonCompany PK PersonCompanyID, FK PersonID, FK CompanyID tblTitle PK TitleID, FK PersonCompanyID But this is what I consider an unusual case. It's very seldom that you need a many-to-one relationship beween a child table and have a junction table be the parent record. I had to strain to come up with such a situation, and I've encountered it only a few times in my 15+ years of database application design. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#27
|
|||
|
|||
One-To-One Tables
I thought you were withdrawing from further response.
I remember seeing the posting you have copied here, but it isn't in this thread. "Steve" wrote: Again, here is my response ......... Actually you don't have one-to-one relationships! You have a one-to-many relationship between Agreements and Requirements. You have a one-to-many relationship between Requirements and Certificates. You have a one-to-many relationship between Requirements and Validation Checks. Your tables should look like: TblAgreement AgreementID Agreement Fields TblRequirement RequirementID AgreementID Requirement Fields TblValidationCheck ValidationCheckID RequirementID Validation Check Fields This is just a basic outline since you gave no details about agreements, requirements and validation checks. If you provide more detail, I will expand upon the above. Steve "oldblindpew" wrote in message ... Nope. Steve said I got three initial responses to my question, one from Jerry, one from Steve and one from John. I see only Jerry's and John's. --OBP "Duane Hookom" wrote: I think $teve was referring to an earlier thread from the same OP. -- Duane Hookom Microsoft Access MVP "John W. Vinson" wrote: On Wed, 26 Aug 2009 11:32:47 -0400, "Steve" wrote: You got three initial responses, Jerry, myself (Steve) and John. I think you may have propagation problems, Steve: your response did not make it to my server (and probably not to oldblindpew's either). -- John W. Vinson [MVP] |
#28
|
|||
|
|||
One-To-One Tables
=?Utf-8?B?b2xkYmxpbmRwZXc=?=
wrote in : Side-issue: Person-to-Company sounds like a many-to-one relationship. I thought the purpose of join tables was to handle many-to-many relationships. Person-to-Company is very often a many-to-many because a person can have a position at more than one company/organization. I could be both president and CEO of ACME Corp., and also on the board of InfiniCorp, and then a partner in PrivateVentures, LLC. What I've just described is N:N for person/company, and 1:N for a person's position(s) within each company. Main issue: I like the concept of composite keys, but I read somewhere on the internet from one published expert that composite keys should be avoided because they create problems. They do. They repeat a whole lot of data, which means you lose the efficiency of Jet/ACE when retrieving the index pages, and then only the needed data pages. If most of the data is in the foreign key, Jet/ACE may not need to retrieve anything but the index, but the number of index pages isn't going to be much larger for the same number of rows, so I'd think that those composite keys would cause a lot more data to be pulled across the Internet. I don't use a composite key for any table whose PK is the foreign key in another table. That is, if the table is not the parent in a relationship with another table, a composite key is fine, particular if it's a composite key of foreign keys, as in a many-to-many join table. My own experience is that even though Access knows that a composite key is two fields that sometimes act as one, Access apparently does not provide ready-made support for this reality. For example, I like to use a list box as a navigation tool to allow the user to pick a record, but this won't work with composite keys because the list box is incapable of passing the value of more than one field. Well, not as a bound column, but you can still get the data in the other columns. I think you use ItemData(i).Column -- I always have to look it up, to be honest. But for a bound listbox, it doesn't work well. I don't bind many listboxes, but I certainly use lots of bound combo boxes, and the problem with composite keys is identical there. Another question: In your first example, does your junction table actually store PersonID and CompanyID twice, once as PK and again as FK? No. I was describing the INDEXES not the fields. The table has only the two fields, and there's a composite PK made up of the two fields, and each field is individually a FK. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#29
|
|||
|
|||
One-To-One Tables
David,
Thank you for your clear and practical answers. I have reexamined the question of reading data from various columns in a list box selection. I had thought before that this could only be accomplished for a multiselect list box, and obviously, selecting more than one record to navigate to would be undesirable. However, it appears one can read any column in a list box selection by using the list box's Column property. I haven't tested it yet, though. As for the main topic of this thread, I have about come full circle back to my original plan of using 1:1 tables for insurance information. This is due mostly to the nature of the data: Insurance requirements are not all the same kind of thing, and I don't think it would do to put them all in one master table, like state name abbreviations. Also, I have many Agreements in my Agreements table, but not all of them have Insurance, so this is a classic example where a separate 1:1 table is justified. Some may argue that store inventory items are all different, but they all go in one table. My Agreement is just an Order, and my Insurance Requirements just the Order Detail. Maybe, but store items share a common set of fields: Item No, Description, Quantity, Unit of Measure, Unit Price, Shipping Weight. Insurance requirements, in contrast, can be Y/N, Dollar amounts, Dates, or Numeric values. I think I'll just have to go ahead and find out the hard way. One thing about Access is that so far, with my simple application, it hasn't been too hard to make changes. --OBP "David W. Fenton" wrote: =?Utf-8?B?b2xkYmxpbmRwZXc=?= wrote in : Side-issue: Person-to-Company sounds like a many-to-one relationship. I thought the purpose of join tables was to handle many-to-many relationships. Person-to-Company is very often a many-to-many because a person can have a position at more than one company/organization. I could be both president and CEO of ACME Corp., and also on the board of InfiniCorp, and then a partner in PrivateVentures, LLC. What I've just described is N:N for person/company, and 1:N for a person's position(s) within each company. Main issue: I like the concept of composite keys, but I read somewhere on the internet from one published expert that composite keys should be avoided because they create problems. They do. They repeat a whole lot of data, which means you lose the efficiency of Jet/ACE when retrieving the index pages, and then only the needed data pages. If most of the data is in the foreign key, Jet/ACE may not need to retrieve anything but the index, but the number of index pages isn't going to be much larger for the same number of rows, so I'd think that those composite keys would cause a lot more data to be pulled across the Internet. I don't use a composite key for any table whose PK is the foreign key in another table. That is, if the table is not the parent in a relationship with another table, a composite key is fine, particular if it's a composite key of foreign keys, as in a many-to-many join table. My own experience is that even though Access knows that a composite key is two fields that sometimes act as one, Access apparently does not provide ready-made support for this reality. For example, I like to use a list box as a navigation tool to allow the user to pick a record, but this won't work with composite keys because the list box is incapable of passing the value of more than one field. Well, not as a bound column, but you can still get the data in the other columns. I think you use ItemData(i).Column -- I always have to look it up, to be honest. But for a bound listbox, it doesn't work well. I don't bind many listboxes, but I certainly use lots of bound combo boxes, and the problem with composite keys is identical there. Another question: In your first example, does your junction table actually store PersonID and CompanyID twice, once as PK and again as FK? No. I was describing the INDEXES not the fields. The table has only the two fields, and there's a composite PK made up of the two fields, and each field is individually a FK. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
#30
|
|||
|
|||
One-To-One Tables
=?Utf-8?B?b2xkYmxpbmRwZXc=?=
wrote in : As for the main topic of this thread, I have about come full circle back to my original plan of using 1:1 tables for insurance information. This is due mostly to the nature of the data: Insurance requirements are not all the same kind of thing, and I don't think it would do to put them all in one master table, like state name abbreviations. Also, I have many Agreements in my Agreements table, but not all of them have Insurance, so this is a classic example where a separate 1:1 table is justified. I used to be big on supertypes and subtypes, but found that the outer joins needed to display all of them in a single list (or UNION) were an unacceptable performance drain. Now, with server-side views doing the joins, it might not be as bad, but with Jet/ACE, it can be a complete killer. That said, I just implemented a Customer History table that has foreign keys on 3 different tables and does exactly what I just described. It is performing just fine -- dunno why, but maybe I'm just better at it than I used to be. The perfect 1:1 scenario is when you create a basic record and then some records then eventually reach a state when a bunch of other fields need to be filled out, but not all of the basic records reach that point. In a case management database for a psychiatric clinic, the 1:1 side table recording the demographics, and were not filled out until the case was closed. That was a perfect application of 1:1, as cases that never closed but were simply abandoned did not have the demographics. It also made it easy to deal with the demographic data as separate from the information specific to the individual, which was good for confidentiality. But I haven't encountered that many such situations. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |
Thread Tools | |
Display Modes | |
|
|