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 with two joins? Not...
I thought I could build two tables, each including identical fields of Group
and Office_Sym, and create two simple joins in a query, but when I build the query I can't edit any info in it. One table is a Personnel table and the other is Office space table. One-to-one I thought. Should I skip the Group & Office_Sym double join and do a simple fkOffice_Space in Personnel to the Office_Space_ID? I did it originally because I had the data to build the tables and thought it would be an easy link. I've never done a double join before and thought it would be simple. |
#2
|
|||
|
|||
One to one with two joins? Not...
So you have two tables, for clarity sake I have used autonumbers for
the ids: office_spaces: office_space_id - autonumber - PK info about office spaces personnel: personnel_id - autonumber - PK info about personnel Now there are three ways you can go about this. 1) You can add a foreign key to personnel_id in office_spaces, making sure it is unique. 2) You can add a foreign key to office_space_id in personnel, making sure it is unique. 3) My suggested method: office_space_assignment: office_space_id - number - unique - FK to office_space personnel_id - number - unique - FK to personnel By defining these fields as unique you guarantee that an office_space can only be occupied by one personnel, and that each personnel can only occupy one office space. The reason I don't choose 1 or 2 is the fact that neither is truly an attribute of the other. Rather they are both included in assignment. Cheers, Jason Lepack On Aug 8, 2:04 pm, Maarkr wrote: I thought I could build two tables, each including identical fields of Group and Office_Sym, and create two simple joins in a query, but when I build the query I can't edit any info in it. One table is a Personnel table and the other is Office space table. One-to-one I thought. Should I skip the Group & Office_Sym double join and do a simple fkOffice_Space in Personnel to the Office_Space_ID? I did it originally because I had the data to build the tables and thought it would be an easy link. I've never done a double join before and thought it would be simple. |
#3
|
|||
|
|||
One to one with two joins? Not...
On 8 Aug, 19:19, Jason Lepack wrote:
there are three ways you can go about this. 1) You can add a foreign key to personnel_id in office_spaces, making sure it is unique. 2) You can add a foreign key to office_space_id in personnel, making sure it is unique. 3) My suggested method: office_space_assignment: office_space_id - number - unique - FK to office_space personnel_id - number - unique - FK to personnel By defining these fields as unique you guarantee that an office_space can only be occupied by one personnel, and that each personnel can only occupy one office space. The reason I don't choose 1 or 2 is the fact that neither is truly an attribute of the other. Rather they are both included in assignment. There's a basic design principle that a table either models an entity type (excepting 'special' table types: lookup, auxiliary, etc) or a relationship involving entities but not both. Therefore, I concur your suggested third way. Jamie, -- |
Thread Tools | |
Display Modes | |
|
|