One to one with two joins? Not...

[Maarkr]

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.

 

[Jason Lepack]

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

 

[Jamie Collins]

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,

--