I have been reading the recent posts on relationship designations and earlier posts about many-to-many relationships (including the sample db) to see if I can determine how to set up my db correctly but I am at a loss. I have two tables thus far, each with information for individuals (represented by an ID field) and their residences (represented by a residence number field). Each individual may have multiple residences, which leads me to believe I should set the indexing for ID and residence number to be okay for duplicates. However, when I try to set up the relationship between these tables (or between a junction table and these two, for that matter), I am told the relationship type is indeterminate. I have tried setting both fields as primary keys, and also set both fields to not be primary keys, and I am told of this indeterminate relationship either way. Any suggestions for how I ought to format this???
relationships/multiple primary keys?
- Thread starter ehdoh
- Start date
- Local time
- Today, 14:34
- Joined
- Feb 19, 2002
- Messages
- 47,191
You need a Person table and an address table:
tblPerson:
PersonID (autonumber primary key)
FirstName
LastName
....
tblAddress:
AddressID (autonumber primary key)
PersonID (long integer foreign key to tblPerson)
Address1
Address2
City
State
Zip
.....
tblPerson:
PersonID (autonumber primary key)
FirstName
LastName
....
tblAddress:
AddressID (autonumber primary key)
PersonID (long integer foreign key to tblPerson)
Address1
Address2
City
State
Zip
.....
neileg
AWF VIP
- Local time
- Today, 19:34
- Joined
- Dec 4, 2002
- Messages
- 5,975
Pat's answer is fine if you have a one (person) to many (residences) relationship. I suspect you have a many to many relationship if you can have more than one person to one residence. So you will need a junction table.
Now Pat knows a heck of a lot more about many to many relationships than me, and I suggest you look at her example in the Sample Database forum.
Now Pat knows a heck of a lot more about many to many relationships than me, and I suggest you look at her example in the Sample Database forum.
- Local time
- Today, 14:34
- Joined
- Feb 19, 2002
- Messages
- 47,191
ehdoh did say that he had many address for one person. If in fact he has many people to a single address also, then he does need a many-to-many relationship which requires a junction table.
Actually, in this particular database we are not entering anything to identify a person as an individual other than the person's unique ID which is not an autonumber ID but a unique ID we have created and need to use to ultimately link all databases of information on this cohort. Thus I don't see how I could create a person table unless that table had only the ID field. Does it make sense to have a table with just one field?
If I created the person table with just the id field, and had a separate table (address, as you suggest) with all the residence info (address info as well as exposure info) I believe it would be appropriate to set up as a one to many relationship. For each id, there would be the potential of many residences, but there would only be one ID per person in the person table.
Does this sound logical?
If I created the person table with just the id field, and had a separate table (address, as you suggest) with all the residence info (address info as well as exposure info) I believe it would be appropriate to set up as a one to many relationship. For each id, there would be the potential of many residences, but there would only be one ID per person in the person table.
Does this sound logical?
Also, the residence ID is something we create -- and it is replicated across people. For example, an individual may only have residence R1 or may have multiple residences R1, R2, R3, R4, R5, etc. The residence numbering scheme starts over for each individual.
Initially I thought the residence number would need to be a second primary key, at least in the way I originally conceptualized the tables/relationships. But I think if I set up the one to many relationship described in my previous post (implementing the suggestion of the person and residence table) there should only be a primary key based on the person's ID.
Initially I thought the residence number would need to be a second primary key, at least in the way I originally conceptualized the tables/relationships. But I think if I set up the one to many relationship described in my previous post (implementing the suggestion of the person and residence table) there should only be a primary key based on the person's ID.
neileg
AWF VIP
- Local time
- Today, 19:34
- Joined
- Dec 4, 2002
- Messages
- 5,975
Is there a reason behind creating a table just to hold the person ID, unless you are importing this data, or linking from another database. Are you?
But you have not answered the question about whether this is a one to many or a many to many relationship. Logic suggests that a house may be the residence of more than one person in your dataset, either concurrently or at a different time. This suggests a many to many relationship. However, there may be reasons why you want to create a new residence record for each person, even if it is actually the same house. Now I'm not saying that one way is correct and the other isn't, but its not clear from what you have said so far, which scenario applies.
But you have not answered the question about whether this is a one to many or a many to many relationship. Logic suggests that a house may be the residence of more than one person in your dataset, either concurrently or at a different time. This suggests a many to many relationship. However, there may be reasons why you want to create a new residence record for each person, even if it is actually the same house. Now I'm not saying that one way is correct and the other isn't, but its not clear from what you have said so far, which scenario applies.
Sorry for not having been clear about the relationship set-up (I am still feeling fuzzy about this myself, particularly with the suggestion to explore a new set-up from that which I originally conceptualized)...
You are indeed correct in assuming that multiple people can share the same residence. However, I should point out that this possibility is compounded because we have not collected street address information but only city, state, post code/zip. The more unique information that we do have about each residence is the years of residence in a particular location and whether a residence was a second home or primary. I've attached a page from our questionnaire to give you a better idea of the type of data I'll be working with.
We do need a residence record for each person, even if at the same location, with same exposure info, etc.
As far as linking databases goes, ultimately this will be done through SAS (which can read Access tables directly).
Based on the information provided, I would greatly appreciate advice on how best to set up the tables/relationships for this database. I'll also be curious to hear if you think the person table should still be created (albeit as a one-variable table).
Cheers,
E.
You are indeed correct in assuming that multiple people can share the same residence. However, I should point out that this possibility is compounded because we have not collected street address information but only city, state, post code/zip. The more unique information that we do have about each residence is the years of residence in a particular location and whether a residence was a second home or primary. I've attached a page from our questionnaire to give you a better idea of the type of data I'll be working with.
We do need a residence record for each person, even if at the same location, with same exposure info, etc.
As far as linking databases goes, ultimately this will be done through SAS (which can read Access tables directly).
Based on the information provided, I would greatly appreciate advice on how best to set up the tables/relationships for this database. I'll also be curious to hear if you think the person table should still be created (albeit as a one-variable table).
Cheers,
E.
neileg
AWF VIP
- Local time
- Today, 19:34
- Joined
- Dec 4, 2002
- Messages
- 5,975
Much clearer, now.
So you are going to have a series of records relating to one ID# but no other information on the ID# There will be at least one record, up to a theoretically unlimited number. I believe this will be a one off input against that ID#
What will be essential is that the ID# is input only once for each questionnaire so that there are no discrepancies and no needless rekeying.
So far as I can see, the ID# is either going to be input into a one field table, or would be entered into a text box on your input form. Either way, this ID# will be stored on each record of your residence table.
I can’t see any compelling reason for the person table, so I wouldn’t bother. At any time you can create this ID# data with a query on the residence table.
So you are going to have a series of records relating to one ID# but no other information on the ID# There will be at least one record, up to a theoretically unlimited number. I believe this will be a one off input against that ID#
What will be essential is that the ID# is input only once for each questionnaire so that there are no discrepancies and no needless rekeying.
So far as I can see, the ID# is either going to be input into a one field table, or would be entered into a text box on your input form. Either way, this ID# will be stored on each record of your residence table.
I can’t see any compelling reason for the person table, so I wouldn’t bother. At any time you can create this ID# data with a query on the residence table.
Hi Neil,
Just to make sure I am understanding you correctly -- if I have the ID entered in a textbox on the form, are you saying I should be able to use one table wherein a new record is created for each residence number of each person?
Cheers,
Elaine
Just to make sure I am understanding you correctly -- if I have the ID entered in a textbox on the form, are you saying I should be able to use one table wherein a new record is created for each residence number of each person?
Cheers,
Elaine
neileg
AWF VIP
- Local time
- Today, 19:34
- Joined
- Dec 4, 2002
- Messages
- 5,975
Yes.
I think I would have a main form with a text box for the ID#
I'd have a subform for the details for each residence and point the ID# field at the text box.
I might do some or all of the following to help data entry and or accuracy
- Create a multi field index on ID# and Residence number to prevent duplicates
- Use DMax([Residence Number])+1 to generate the residence number
- Prompt the user to change the ID# after each residence record is created
Good luck
- Local time
- Today, 14:34
- Joined
- Feb 19, 2002
- Messages
- 47,191
- ID's created to have meaning cause problems. How would you handle deleting R3 in the above example? Would it cause any stress for your users if the addresses were numbered 1,2,4,5?
An autonumber is far mor appropriate for the primary key of the address table. The user never sees the autonumber, you simply sort the addresses by it and they show up in the order in which they were entered. The foreign key would be the UniqueID you described as coming from "somewhere else". You don't need to create a new person table if you already have an existing table that serves the same purpose. You would use the UniqueID to join your existing table with the address table.
