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 |
#11
|
|||
|
|||
Autonumber as Primary Key and Relationships
Hi Jeff,
Makes sense to me. Depending on the situation, I could easily end up with something similar. Clifford Bass "Jeff Boyce" wrote: Cliff In the very-specific environment I faced, I needed to track who-where-in what role, so I used a single identifier for each valid combination (i.e., a "contextID"), and kept Individual, Organization, and Position tables. Then, since 'contexts' could share an address (think small town city hall), or a phone number (think very small town) and, theoretically, an email address, I kept Address, Phone and Email tables. Finally, to handle the many-to-many, I created junction tables between the contexts and the address, phone and email records. A real pain, and definitely not your father's CRM system, but absolutely required to handle the complexities of the environment. Regards Jeff Boyce Microsoft Office/Access MVP |
#12
|
|||
|
|||
Autonumber as Primary Key and Relationships
Hi,
I might suggest the following instead; subject to modification/refinement as needed and subject to it not being overkill to your situation. Again, one table for people that would include all of the service members, the spouses and the dependents. An address table, a telephone table and some junction tables (people/spouse, people/dependents, people/address and people/telephone). tblPeople Person_ID (autonumber, primary key) Last_Name First_Name Rank other person-specific information tblAddresses Address_ID (autonumber, primary key) Address City etc. tblTelephones Telephone_ID (autonumber, primary key) Telephone_Type Telephone_Number maybe other info such if it can accept text messages tblPeople_Spouses Person_ID (long int, connected to tblPeople.Person_ID, part of primary key) Spouse_ID (long int, connected to tblPeople.Person_ID, part of primary key) other spouse-specific information tblPeople_Dependents Person_ID (long int, connected to tblPeople.Person_ID, part of primary key) Dependent_ID (long int, connected to tblPeople.Person_ID, part of primary key) other dependent-specific information tblPeople_Addresses Person_ID (long int, connected to tblPeople.Person_ID, part of primary key) Address_ID (long int, connected to tblAddresses.Address_ID, part of primary key) other information specific to this person/address combination if needed tblPeople_Telephones Person_ID (long int, connected to tblPeople.Person_ID, part of primary key) Telephone_ID (long int, connected to tblTelephones.Telephone_ID, part of primary key) other information specific to this person/telephone combination if needed However if you want to keep your current design: Unless you are allowing for polygamists and such, you only need the SM_ID in the spouse table (one person has one spouse). However for the dependent table you would need need an additional field in your primary key so that you can have multiple dependents for each service number. If I were doing it, I probably would add an integer dependent number field that would start with 1 and go up for each service member. So service member 1 might have dependents numbered 1 and 2; and service member 2 might have dependents numbered 1, 2, 3 and 4. This is easy enough to automate in a form so the user would not have to enter the number. Hope that helps, Clifford Bass "cmc096" wrote: Gentlemen, thanks for all the input. I think I am on the right track now. I am going to set my table up like this: Parent Table tbl_Service_Member_Info SM_ID (autonumber_PK) L_Name F_Name Rank Address Phone Child Table tbl_Spouse_Info SM_ID (long int, PK) L_Name F_Name Address Phone tbl_Dependent_Info SM_ID (long int, PK) L_Name F_Name Address Phone My question is with this design will it allow me to have a one to many relationship with the Parent tbl (Service_Member_Info) to the Child table (Dependent_Info)? Also, will one service member then be linked to the spouce and dependent tables using the autonumber to link them and track for instance multiple dependents? |
#13
|
|||
|
|||
Autonumber as Primary Key and Relationships
If I send a screen shot of the tables in the relationship view can someone
help me out with linking them together? "John W. Vinson" wrote: On Wed, 8 Jul 2009 08:34:02 -0700, cmc096 wrote: Another question. When I am creating the relationships it is only showing me a one-to-one in the relationship type when joining the PK autonumber to a PK long int field, is there something else I need to do? Yes. Give each child table ITS OWN PK. You *cannot* use the SM_ID as the primary key of the child table since a primary key is, by definition, unique - you can only have one child record for a give SM_ID because you're (erroneously) using it as the primary key. It's a foreign key which should be indexed but not unique. -- John W. Vinson [MVP] |
#14
|
|||
|
|||
Autonumber as Primary Key and Relationships
Hi,
Not really anything to the actual setup of the relationships once you have decided on the table structures. Just click and drag from one table's field to the other table's field. So you would click on the tbl_Service_Member's SM_ID and drag that to the tbl_Spouse_Info's SM_ID. You can add other fields if needed for relations that require multiple fields. Double-click the linking line to set some of the link's properties. Clifford Bass "cmc096" wrote: If I send a screen shot of the tables in the relationship view can someone help me out with linking them together? |
|
Thread Tools | |
Display Modes | |
|
|