One to one with two joins? Not...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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.
Click to expand...

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,

--
 
Back
Top