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
|
|||
|
|||
Composite Primary Key Design
I'm trying to figure out the best design for a new database for a composite
primary key (CPK) where one of the fields may be a NULL - a violation of a primary key. We will be using on SQL Server 2000 with an Access front end. Existing database is entirely in Access and we are upsizing. The CPK will be the most fundamental in the database. I am working on a life insurance database. Each case can have one or more insureds - if multiple insureds normally two but theoretically could be more. We assign a policy number to each case e.g. "VUL100000". If this case has more than one insured e.g a husband and wife - we currently append an "a" or a "b" to set up a unique key for each of the insureds. If the policy has only one insured, then a letter is not appended. So if VL100000 has two insureds and VL100001 has one insured the keys would be: PolicyNumber VUL100000a (for the husband or first insured) VUL100000b (for the wife or second insured) VUL100001 (no letter appended as it is a single insured The above works as Primary Key. But what we want to do is to leave the PolicyNumber alone and have a second field distinguish the record: PolicyNumber PolicyNumberAlpha VUL100000 a (for the husband or first insured) VUL100000 b (for the wife or second insured) VUL100001 (no letter appended as it is a single insured Since the field "PolicyNumberAlpha" can be blank or NULL it doesn't work as part of a CPK. I suppose one solution would be to assign a "z" to the single insured for the CPK. His policy number alone makes his record unique so assigning a suffix in the PolicyNumberAlpha field is unnecessary. What is the most efficient design to handle this? |
#2
|
|||
|
|||
Dkline wrote:
I'm trying to figure out the best design for a new database for a composite primary key (CPK) where one of the fields may be a NULL - a violation of a primary key. We will be using on SQL Server 2000 with an Access front end. Existing database is entirely in Access and we are upsizing. The CPK will be the most fundamental in the database. I am working on a life insurance database. Each case can have one or more insureds - if multiple insureds normally two but theoretically could be more. We assign a policy number to each case e.g. "VUL100000". If this case has more than one insured e.g a husband and wife - we currently append an "a" or a "b" to set up a unique key for each of the insureds. If the policy has only one insured, then a letter is not appended. So if VL100000 has two insureds and VL100001 has one insured the keys would be: PolicyNumber VUL100000a (for the husband or first insured) VUL100000b (for the wife or second insured) VUL100001 (no letter appended as it is a single insured The above works as Primary Key. But what we want to do is to leave the PolicyNumber alone and have a second field distinguish the record: PolicyNumber PolicyNumberAlpha VUL100000 a (for the husband or first insured) VUL100000 b (for the wife or second insured) VUL100001 (no letter appended as it is a single insured Since the field "PolicyNumberAlpha" can be blank or NULL it doesn't work as part of a CPK. I suppose one solution would be to assign a "z" to the single insured for the CPK. His policy number alone makes his record unique so assigning a suffix in the PolicyNumberAlpha field is unnecessary. What is the most efficient design to handle this? You need two tables (IMO). One for Policies and a related one for the InsuredPersons. There would be a one-to-many relationship between these two tables. Then every PolicyNumber can be associated with any number of InsuredPersons. Policy table would have a single-field PK consisting of the PolicyNumber and the InsuredPersons table would use a composite key of PolicyNumber and an additional field to identify the person. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#3
|
|||
|
|||
Rick - thanks for the reply.
I left out a crucial element. An insured can be in many policies. A policy can have many insureds. So I believe I will need a linking or union table. Mea culpa for leaving that vital information out. Don "Rick Brandt" wrote in message ... Dkline wrote: I'm trying to figure out the best design for a new database for a composite primary key (CPK) where one of the fields may be a NULL - a violation of a primary key. We will be using on SQL Server 2000 with an Access front end. Existing database is entirely in Access and we are upsizing. The CPK will be the most fundamental in the database. I am working on a life insurance database. Each case can have one or more insureds - if multiple insureds normally two but theoretically could be more. We assign a policy number to each case e.g. "VUL100000". If this case has more than one insured e.g a husband and wife - we currently append an "a" or a "b" to set up a unique key for each of the insureds. If the policy has only one insured, then a letter is not appended. So if VL100000 has two insureds and VL100001 has one insured the keys would be: PolicyNumber VUL100000a (for the husband or first insured) VUL100000b (for the wife or second insured) VUL100001 (no letter appended as it is a single insured The above works as Primary Key. But what we want to do is to leave the PolicyNumber alone and have a second field distinguish the record: PolicyNumber PolicyNumberAlpha VUL100000 a (for the husband or first insured) VUL100000 b (for the wife or second insured) VUL100001 (no letter appended as it is a single insured Since the field "PolicyNumberAlpha" can be blank or NULL it doesn't work as part of a CPK. I suppose one solution would be to assign a "z" to the single insured for the CPK. His policy number alone makes his record unique so assigning a suffix in the PolicyNumberAlpha field is unnecessary. What is the most efficient design to handle this? You need two tables (IMO). One for Policies and a related one for the InsuredPersons. There would be a one-to-many relationship between these two tables. Then every PolicyNumber can be associated with any number of InsuredPersons. Policy table would have a single-field PK consisting of the PolicyNumber and the InsuredPersons table would use a composite key of PolicyNumber and an additional field to identify the person. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#4
|
|||
|
|||
Use a linking (junction) table in a classic many-to-many relation
between people and policies. The bare requirements for the linking table are foreign keys to the base tables. If required by your business rules, you could add more fields than the bare linking requirements into the linking table -- e.g. date when new party was added to the policy, sub-policy letter ("a", "b", etc) On Wed, 29 Dec 2004 12:40:40 -0500, "Dkline" wrote: Rick - thanks for the reply. I left out a crucial element. An insured can be in many policies. A policy can have many insureds. So I believe I will need a linking or union table. Mea culpa for leaving that vital information out. Don "Rick Brandt" wrote in message ... Dkline wrote: I'm trying to figure out the best design for a new database for a composite primary key (CPK) where one of the fields may be a NULL - a violation of a primary key. We will be using on SQL Server 2000 with an Access front end. Existing database is entirely in Access and we are upsizing. The CPK will be the most fundamental in the database. I am working on a life insurance database. Each case can have one or more insureds - if multiple insureds normally two but theoretically could be more. We assign a policy number to each case e.g. "VUL100000". If this case has more than one insured e.g a husband and wife - we currently append an "a" or a "b" to set up a unique key for each of the insureds. If the policy has only one insured, then a letter is not appended. So if VL100000 has two insureds and VL100001 has one insured the keys would be: PolicyNumber VUL100000a (for the husband or first insured) VUL100000b (for the wife or second insured) VUL100001 (no letter appended as it is a single insured The above works as Primary Key. But what we want to do is to leave the PolicyNumber alone and have a second field distinguish the record: PolicyNumber PolicyNumberAlpha VUL100000 a (for the husband or first insured) VUL100000 b (for the wife or second insured) VUL100001 (no letter appended as it is a single insured Since the field "PolicyNumberAlpha" can be blank or NULL it doesn't work as part of a CPK. I suppose one solution would be to assign a "z" to the single insured for the CPK. His policy number alone makes his record unique so assigning a suffix in the PolicyNumberAlpha field is unnecessary. What is the most efficient design to handle this? You need two tables (IMO). One for Policies and a related one for the InsuredPersons. There would be a one-to-many relationship between these two tables. Then every PolicyNumber can be associated with any number of InsuredPersons. Policy table would have a single-field PK consisting of the PolicyNumber and the InsuredPersons table would use a composite key of PolicyNumber and an additional field to identify the person. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com ********************** remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security |
#5
|
|||
|
|||
So I could have a linking table with its own fields.
How does one build a form that makes sense to an end user - where they have to pair up the policy number on one side and the insured on the other? I'm building it and I have to be careful that I am pairing them up properly or is that an idicator of poor table design? "Jack MacDonald" wrote in message ... Use a linking (junction) table in a classic many-to-many relation between people and policies. The bare requirements for the linking table are foreign keys to the base tables. If required by your business rules, you could add more fields than the bare linking requirements into the linking table -- e.g. date when new party was added to the policy, sub-policy letter ("a", "b", etc) On Wed, 29 Dec 2004 12:40:40 -0500, "Dkline" wrote: Rick - thanks for the reply. I left out a crucial element. An insured can be in many policies. A policy can have many insureds. So I believe I will need a linking or union table. Mea culpa for leaving that vital information out. Don "Rick Brandt" wrote in message ... Dkline wrote: I'm trying to figure out the best design for a new database for a composite primary key (CPK) where one of the fields may be a NULL - a violation of a primary key. We will be using on SQL Server 2000 with an Access front end. Existing database is entirely in Access and we are upsizing. The CPK will be the most fundamental in the database. I am working on a life insurance database. Each case can have one or more insureds - if multiple insureds normally two but theoretically could be more. We assign a policy number to each case e.g. "VUL100000". If this case has more than one insured e.g a husband and wife - we currently append an "a" or a "b" to set up a unique key for each of the insureds. If the policy has only one insured, then a letter is not appended. So if VL100000 has two insureds and VL100001 has one insured the keys would be: PolicyNumber VUL100000a (for the husband or first insured) VUL100000b (for the wife or second insured) VUL100001 (no letter appended as it is a single insured The above works as Primary Key. But what we want to do is to leave the PolicyNumber alone and have a second field distinguish the record: PolicyNumber PolicyNumberAlpha VUL100000 a (for the husband or first insured) VUL100000 b (for the wife or second insured) VUL100001 (no letter appended as it is a single insured Since the field "PolicyNumberAlpha" can be blank or NULL it doesn't work as part of a CPK. I suppose one solution would be to assign a "z" to the single insured for the CPK. His policy number alone makes his record unique so assigning a suffix in the PolicyNumberAlpha field is unnecessary. What is the most efficient design to handle this? You need two tables (IMO). One for Policies and a related one for the InsuredPersons. There would be a one-to-many relationship between these two tables. Then every PolicyNumber can be associated with any number of InsuredPersons. Policy table would have a single-field PK consisting of the PolicyNumber and the InsuredPersons table would use a composite key of PolicyNumber and an additional field to identify the person. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com ********************** remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security |
#6
|
|||
|
|||
The typical interface is to have a "base" form that deals with the
policies. Within that form you create subform for managing the people. The subform is based on the junction table, and it displays (at a minimum), the foreign key to the people table, and is usually a datasheet or continuous form. End result: the form displays the data for a single policy with a list of the people who belong to that policy. Access manages the "pairing-up" automatically via the "Linking fields" properties of the subform. Typically, the "people" foreign key will be a combo box linked to the People table so that you deal with people's names, rather than their ID number. You could also build a complimentary form using the People table as the base table, with a subform showing the Policies that are related to that person. The thought process is identical. On Thu, 30 Dec 2004 13:12:05 -0500, "Dkline" wrote: So I could have a linking table with its own fields. How does one build a form that makes sense to an end user - where they have to pair up the policy number on one side and the insured on the other? I'm building it and I have to be careful that I am pairing them up properly or is that an idicator of poor table design? "Jack MacDonald" wrote in message .. . Use a linking (junction) table in a classic many-to-many relation between people and policies. The bare requirements for the linking table are foreign keys to the base tables. If required by your business rules, you could add more fields than the bare linking requirements into the linking table -- e.g. date when new party was added to the policy, sub-policy letter ("a", "b", etc) On Wed, 29 Dec 2004 12:40:40 -0500, "Dkline" wrote: Rick - thanks for the reply. I left out a crucial element. An insured can be in many policies. A policy can have many insureds. So I believe I will need a linking or union table. Mea culpa for leaving that vital information out. Don "Rick Brandt" wrote in message ... Dkline wrote: I'm trying to figure out the best design for a new database for a composite primary key (CPK) where one of the fields may be a NULL - a violation of a primary key. We will be using on SQL Server 2000 with an Access front end. Existing database is entirely in Access and we are upsizing. The CPK will be the most fundamental in the database. I am working on a life insurance database. Each case can have one or more insureds - if multiple insureds normally two but theoretically could be more. We assign a policy number to each case e.g. "VUL100000". If this case has more than one insured e.g a husband and wife - we currently append an "a" or a "b" to set up a unique key for each of the insureds. If the policy has only one insured, then a letter is not appended. So if VL100000 has two insureds and VL100001 has one insured the keys would be: PolicyNumber VUL100000a (for the husband or first insured) VUL100000b (for the wife or second insured) VUL100001 (no letter appended as it is a single insured The above works as Primary Key. But what we want to do is to leave the PolicyNumber alone and have a second field distinguish the record: PolicyNumber PolicyNumberAlpha VUL100000 a (for the husband or first insured) VUL100000 b (for the wife or second insured) VUL100001 (no letter appended as it is a single insured Since the field "PolicyNumberAlpha" can be blank or NULL it doesn't work as part of a CPK. I suppose one solution would be to assign a "z" to the single insured for the CPK. His policy number alone makes his record unique so assigning a suffix in the PolicyNumberAlpha field is unnecessary. What is the most efficient design to handle this? You need two tables (IMO). One for Policies and a related one for the InsuredPersons. There would be a one-to-many relationship between these two tables. Then every PolicyNumber can be associated with any number of InsuredPersons. Policy table would have a single-field PK consisting of the PolicyNumber and the InsuredPersons table would use a composite key of PolicyNumber and an additional field to identify the person. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com ********************** remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security ********************** remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security |
#7
|
|||
|
|||
Thanks for the reply. I'm struggling with the linking table/sub form.
I am under the impression that the linking table would have only the fields needed to pair up the records e.g. Policy # | InsuredID # Other fields could be added to this table where the data is unique to the pairing. That is not the case here so the linking table should be just the two fields. I guess what I'm missing as showing the rest of the record for each insured in the subform if the subform is based on the linking table which has only two fields. I'm sorry if I'm being dense but I'm still missing a piece to this puzzle. "Jack MacDonald" wrote in message ... The typical interface is to have a "base" form that deals with the policies. Within that form you create subform for managing the people. The subform is based on the junction table, and it displays (at a minimum), the foreign key to the people table, and is usually a datasheet or continuous form. End result: the form displays the data for a single policy with a list of the people who belong to that policy. Access manages the "pairing-up" automatically via the "Linking fields" properties of the subform. Typically, the "people" foreign key will be a combo box linked to the People table so that you deal with people's names, rather than their ID number. You could also build a complimentary form using the People table as the base table, with a subform showing the Policies that are related to that person. The thought process is identical. On Thu, 30 Dec 2004 13:12:05 -0500, "Dkline" wrote: So I could have a linking table with its own fields. How does one build a form that makes sense to an end user - where they have to pair up the policy number on one side and the insured on the other? I'm building it and I have to be careful that I am pairing them up properly or is that an idicator of poor table design? "Jack MacDonald" wrote in message . .. Use a linking (junction) table in a classic many-to-many relation between people and policies. The bare requirements for the linking table are foreign keys to the base tables. If required by your business rules, you could add more fields than the bare linking requirements into the linking table -- e.g. date when new party was added to the policy, sub-policy letter ("a", "b", etc) On Wed, 29 Dec 2004 12:40:40 -0500, "Dkline" wrote: Rick - thanks for the reply. I left out a crucial element. An insured can be in many policies. A policy can have many insureds. So I believe I will need a linking or union table. Mea culpa for leaving that vital information out. Don "Rick Brandt" wrote in message ... Dkline wrote: I'm trying to figure out the best design for a new database for a composite primary key (CPK) where one of the fields may be a NULL - a violation of a primary key. We will be using on SQL Server 2000 with an Access front end. Existing database is entirely in Access and we are upsizing. The CPK will be the most fundamental in the database. I am working on a life insurance database. Each case can have one or more insureds - if multiple insureds normally two but theoretically could be more. We assign a policy number to each case e.g. "VUL100000". If this case has more than one insured e.g a husband and wife - we currently append an "a" or a "b" to set up a unique key for each of the insureds. If the policy has only one insured, then a letter is not appended. So if VL100000 has two insureds and VL100001 has one insured the keys would be: PolicyNumber VUL100000a (for the husband or first insured) VUL100000b (for the wife or second insured) VUL100001 (no letter appended as it is a single insured The above works as Primary Key. But what we want to do is to leave the PolicyNumber alone and have a second field distinguish the record: PolicyNumber PolicyNumberAlpha VUL100000 a (for the husband or first insured) VUL100000 b (for the wife or second insured) VUL100001 (no letter appended as it is a single insured Since the field "PolicyNumberAlpha" can be blank or NULL it doesn't work as part of a CPK. I suppose one solution would be to assign a "z" to the single insured for the CPK. His policy number alone makes his record unique so assigning a suffix in the PolicyNumberAlpha field is unnecessary. What is the most efficient design to handle this? You need two tables (IMO). One for Policies and a related one for the InsuredPersons. There would be a one-to-many relationship between these two tables. Then every PolicyNumber can be associated with any number of InsuredPersons. Policy table would have a single-field PK consisting of the PolicyNumber and the InsuredPersons table would use a composite key of PolicyNumber and an additional field to identify the person. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com ********************** remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security ********************** remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security |
#8
|
|||
|
|||
See inline comments
On Fri, 31 Dec 2004 08:35:54 -0500, "Dkline" wrote: Thanks for the reply. I'm struggling with the linking table/sub form. I am under the impression that the linking table would have only the fields needed to pair up the records e.g. Policy # | InsuredID # Other fields could be added to this table where the data is unique to the pairing. That is not the case here so the linking table should be just the two fields. Correct - additional fields in the linking table must pertain only to the unique pairing of Policy# & Insured#. One such possible field that comes to mind is InsuredDate -- by placing this field in the linking table, it will allow each Insured person to have a unique date for their coverage (may or may not be applicable for your situation) I guess what I'm missing as showing the rest of the record for each insured in the subform if the subform is based on the linking table which has only two fields. Base the linking table on a query that joins the linking table with the third table. Alternatively, and if there is a lot of information from the third table that you want to display, you can create a second subform for the third table's information. It would sit on your main form in parallel with the original subform. It's a bit trickier to keep such a form "in sync" with the active data, but is feasible - place an unbound textbox on the main form - use the OnCurrent event of the subform to populate the textbox with the current value of the appropriate field e.g. Me.Parent![Text3] = Me![Insured#] - use the textbox as the MasterLinkingField into the secondary subform - hide the textbox I'm sorry if I'm being dense but I'm still missing a piece to this puzzle. Not at all. Some of this stuff is not immediately obvious. "Jack MacDonald" wrote in message .. . The typical interface is to have a "base" form that deals with the policies. Within that form you create subform for managing the people. The subform is based on the junction table, and it displays (at a minimum), the foreign key to the people table, and is usually a datasheet or continuous form. End result: the form displays the data for a single policy with a list of the people who belong to that policy. Access manages the "pairing-up" automatically via the "Linking fields" properties of the subform. Typically, the "people" foreign key will be a combo box linked to the People table so that you deal with people's names, rather than their ID number. You could also build a complimentary form using the People table as the base table, with a subform showing the Policies that are related to that person. The thought process is identical. On Thu, 30 Dec 2004 13:12:05 -0500, "Dkline" wrote: So I could have a linking table with its own fields. How does one build a form that makes sense to an end user - where they have to pair up the policy number on one side and the insured on the other? I'm building it and I have to be careful that I am pairing them up properly or is that an idicator of poor table design? "Jack MacDonald" wrote in message ... Use a linking (junction) table in a classic many-to-many relation between people and policies. The bare requirements for the linking table are foreign keys to the base tables. If required by your business rules, you could add more fields than the bare linking requirements into the linking table -- e.g. date when new party was added to the policy, sub-policy letter ("a", "b", etc) On Wed, 29 Dec 2004 12:40:40 -0500, "Dkline" wrote: Rick - thanks for the reply. I left out a crucial element. An insured can be in many policies. A policy can have many insureds. So I believe I will need a linking or union table. Mea culpa for leaving that vital information out. Don "Rick Brandt" wrote in message ... Dkline wrote: I'm trying to figure out the best design for a new database for a composite primary key (CPK) where one of the fields may be a NULL - a violation of a primary key. We will be using on SQL Server 2000 with an Access front end. Existing database is entirely in Access and we are upsizing. The CPK will be the most fundamental in the database. I am working on a life insurance database. Each case can have one or more insureds - if multiple insureds normally two but theoretically could be more. We assign a policy number to each case e.g. "VUL100000". If this case has more than one insured e.g a husband and wife - we currently append an "a" or a "b" to set up a unique key for each of the insureds. If the policy has only one insured, then a letter is not appended. So if VL100000 has two insureds and VL100001 has one insured the keys would be: PolicyNumber VUL100000a (for the husband or first insured) VUL100000b (for the wife or second insured) VUL100001 (no letter appended as it is a single insured The above works as Primary Key. But what we want to do is to leave the PolicyNumber alone and have a second field distinguish the record: PolicyNumber PolicyNumberAlpha VUL100000 a (for the husband or first insured) VUL100000 b (for the wife or second insured) VUL100001 (no letter appended as it is a single insured Since the field "PolicyNumberAlpha" can be blank or NULL it doesn't work as part of a CPK. I suppose one solution would be to assign a "z" to the single insured for the CPK. His policy number alone makes his record unique so assigning a suffix in the PolicyNumberAlpha field is unnecessary. What is the most efficient design to handle this? You need two tables (IMO). One for Policies and a related one for the InsuredPersons. There would be a one-to-many relationship between these two tables. Then every PolicyNumber can be associated with any number of InsuredPersons. Policy table would have a single-field PK consisting of the PolicyNumber and the InsuredPersons table would use a composite key of PolicyNumber and an additional field to identify the person. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com ********************** remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security ********************** remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security ********************** remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security |
#9
|
|||
|
|||
I now have the main form and the two subforms. I got ambitious and set an
onclick even so that when the user selects an Insured to be with the selected Policy, it puts the InsuredID into the linking table along with the PolicyNumber. Putting the values into the junction table works fine. What I'm stuck on is updating the forms on the screen. I make my selection in the InsuredsID field. Until I click on the other column [PolicyNumber] in the subform in which I select the linking InsuredID, the InsuredID field screen does not get updated. The other subform has a query to show what insured(s) are selected for the current policy. That doesn't update until I move the Main Form's record selector. I've tried DoCmd.Requery and everything else I can think of. How can I get these to refresh or repaint or requery? "Jack MacDonald" wrote in message ... See inline comments On Fri, 31 Dec 2004 08:35:54 -0500, "Dkline" wrote: Thanks for the reply. I'm struggling with the linking table/sub form. I am under the impression that the linking table would have only the fields needed to pair up the records e.g. Policy # | InsuredID # Other fields could be added to this table where the data is unique to the pairing. That is not the case here so the linking table should be just the two fields. Correct - additional fields in the linking table must pertain only to the unique pairing of Policy# & Insured#. One such possible field that comes to mind is InsuredDate -- by placing this field in the linking table, it will allow each Insured person to have a unique date for their coverage (may or may not be applicable for your situation) I guess what I'm missing as showing the rest of the record for each insured in the subform if the subform is based on the linking table which has only two fields. Base the linking table on a query that joins the linking table with the third table. Alternatively, and if there is a lot of information from the third table that you want to display, you can create a second subform for the third table's information. It would sit on your main form in parallel with the original subform. It's a bit trickier to keep such a form "in sync" with the active data, but is feasible - place an unbound textbox on the main form - use the OnCurrent event of the subform to populate the textbox with the current value of the appropriate field e.g. Me.Parent![Text3] = Me![Insured#] - use the textbox as the MasterLinkingField into the secondary subform - hide the textbox I'm sorry if I'm being dense but I'm still missing a piece to this puzzle. Not at all. Some of this stuff is not immediately obvious. "Jack MacDonald" wrote in message . .. The typical interface is to have a "base" form that deals with the policies. Within that form you create subform for managing the people. The subform is based on the junction table, and it displays (at a minimum), the foreign key to the people table, and is usually a datasheet or continuous form. End result: the form displays the data for a single policy with a list of the people who belong to that policy. Access manages the "pairing-up" automatically via the "Linking fields" properties of the subform. Typically, the "people" foreign key will be a combo box linked to the People table so that you deal with people's names, rather than their ID number. You could also build a complimentary form using the People table as the base table, with a subform showing the Policies that are related to that person. The thought process is identical. On Thu, 30 Dec 2004 13:12:05 -0500, "Dkline" wrote: So I could have a linking table with its own fields. How does one build a form that makes sense to an end user - where they have to pair up the policy number on one side and the insured on the other? I'm building it and I have to be careful that I am pairing them up properly or is that an idicator of poor table design? "Jack MacDonald" wrote in message m... Use a linking (junction) table in a classic many-to-many relation between people and policies. The bare requirements for the linking table are foreign keys to the base tables. If required by your business rules, you could add more fields than the bare linking requirements into the linking table -- e.g. date when new party was added to the policy, sub-policy letter ("a", "b", etc) On Wed, 29 Dec 2004 12:40:40 -0500, "Dkline" wrote: Rick - thanks for the reply. I left out a crucial element. An insured can be in many policies. A policy can have many insureds. So I believe I will need a linking or union table. Mea culpa for leaving that vital information out. Don "Rick Brandt" wrote in message ... Dkline wrote: I'm trying to figure out the best design for a new database for a composite primary key (CPK) where one of the fields may be a NULL - a violation of a primary key. We will be using on SQL Server 2000 with an Access front end. Existing database is entirely in Access and we are upsizing. The CPK will be the most fundamental in the database. I am working on a life insurance database. Each case can have one or more insureds - if multiple insureds normally two but theoretically could be more. We assign a policy number to each case e.g. "VUL100000". If this case has more than one insured e.g a husband and wife - we currently append an "a" or a "b" to set up a unique key for each of the insureds. If the policy has only one insured, then a letter is not appended. So if VL100000 has two insureds and VL100001 has one insured the keys would be: PolicyNumber VUL100000a (for the husband or first insured) VUL100000b (for the wife or second insured) VUL100001 (no letter appended as it is a single insured The above works as Primary Key. But what we want to do is to leave the PolicyNumber alone and have a second field distinguish the record: PolicyNumber PolicyNumberAlpha VUL100000 a (for the husband or first insured) VUL100000 b (for the wife or second insured) VUL100001 (no letter appended as it is a single insured Since the field "PolicyNumberAlpha" can be blank or NULL it doesn't work as part of a CPK. I suppose one solution would be to assign a "z" to the single insured for the CPK. His policy number alone makes his record unique so assigning a suffix in the PolicyNumberAlpha field is unnecessary. What is the most efficient design to handle this? You need two tables (IMO). One for Policies and a related one for the InsuredPersons. There would be a one-to-many relationship between these two tables. Then every PolicyNumber can be associated with any number of InsuredPersons. Policy table would have a single-field PK consisting of the PolicyNumber and the InsuredPersons table would use a composite key of PolicyNumber and an additional field to identify the person. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com ********************** remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security ********************** remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security ********************** remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Fixing up structure with primary, natural, surrogate, etc. | Fred Boer | Database Design | 11 | September 17th, 2004 05:53 PM |
Autonumber | Ally H. | General Discussion | 7 | August 27th, 2004 04:51 PM |
HELP!!! wrong design led to a too long query | Liat | Database Design | 6 | August 7th, 2004 08:29 PM |
Muliple primary key | Billy K | Database Design | 4 | May 31st, 2004 02:50 PM |
Need help with Tables Design and Relationships | Tom | Database Design | 24 | May 19th, 2004 06:51 PM |