A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

One to one with two joins? Not...



 
 
Thread Tools Display Modes
  #1  
Old August 8th, 2007, 07:04 PM posted to microsoft.public.access.tablesdbdesign
Maarkr
external usenet poster
 
Posts: 240
Default 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  
Old August 8th, 2007, 07:19 PM posted to microsoft.public.access.tablesdbdesign
Jason Lepack
external usenet poster
 
Posts: 600
Default 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  
Old August 9th, 2007, 09:06 AM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:02 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.