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  

1-1 relationship



 
 
Thread Tools Display Modes
  #1  
Old October 17th, 2006, 09:07 PM posted to microsoft.public.access.tablesdbdesign
Phippsy
external usenet poster
 
Posts: 113
Default 1-1 relationship

Hi

I know that 1-1 relationships are uncommon but when you have them how do you
know which is the primary table. Also if you have a 1-many relationship I
presume that the primary table is the '1'. What about a many-many - could
either be the primary table and how do you know which it is?

Thanks
  #2  
Old October 17th, 2006, 11:39 PM posted to microsoft.public.access.tablesdbdesign
Graham Mandeno
external usenet poster
 
Posts: 593
Default 1-1 relationship

Hi Phippsy

If you double-click on a relationship line in the relationships window, the
Edit Relationships window will pop up. On the left is the "Table/Query" and
on the right is the "Related Table/Query". The "primary table" is the one
on the left.

There is no such thing as a many-to-many relationship. These must be
implemented artificially using a "junction table", which contains foreign
keys for both the tables you want to relate. You then set up a two
one-to-many relationships, between each of the primary tables and the
junction table.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Phippsy" wrote in message
...
Hi

I know that 1-1 relationships are uncommon but when you have them how do
you
know which is the primary table. Also if you have a 1-many relationship I
presume that the primary table is the '1'. What about a many-many - could
either be the primary table and how do you know which it is?

Thanks



  #3  
Old October 18th, 2006, 12:31 AM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default 1-1 relationship

What do YOU mean by "primary table"? It may not match my definition...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Phippsy" wrote in message
...
Hi

I know that 1-1 relationships are uncommon but when you have them how do
you
know which is the primary table. Also if you have a 1-many relationship I
presume that the primary table is the '1'. What about a many-many - could
either be the primary table and how do you know which it is?

Thanks



  #4  
Old October 18th, 2006, 01:25 AM posted to microsoft.public.access.tablesdbdesign
NetworkTrade
external usenet poster
 
Posts: 825
Default 1-1 relationship

1-1 is less common - yes...but not uncommon.

general employee record...and then proprietary info i.e. salary , social
security info, etc.... is a typical 1-1

lots of situations where a summary is all that is needed 95% of the
time...and then additional info occasionally...this is very suitable to a 1-1
.....particularly where the total set of info is too big for a single record.

Obviously they must share the same field ID...so neither/either is
inherently primary per se...in ones mind the table that is used most
frequently I tend to think of as the primary....


--
NTC


"Phippsy" wrote:

Hi

I know that 1-1 relationships are uncommon but when you have them how do you
know which is the primary table. Also if you have a 1-many relationship I
presume that the primary table is the '1'. What about a many-many - could
either be the primary table and how do you know which it is?

Thanks

  #5  
Old October 18th, 2006, 01:42 AM posted to microsoft.public.access.tablesdbdesign
John Vinson
external usenet poster
 
Posts: 4,033
Default 1-1 relationship

On Tue, 17 Oct 2006 17:25:02 -0700, NetworkTrade
wrote:

Obviously they must share the same field ID...so neither/either is
inherently primary per se...in ones mind the table that is used most
frequently I tend to think of as the primary....


Well, as implemented in Access (and SQL/Server, Oracle, MySQL, and
others), you're mistaken; even one to one relationships have
directionality. If you are going to enforce referential integrity,
there must be a record in the "primary" or "left hand" table, and
there may or may not be a record in the child table. Strictly
speaking, it's a "One to (Zero or one)" relationship.

John W. Vinson[MVP]
  #6  
Old October 18th, 2006, 02:36 PM posted to microsoft.public.access.tablesdbdesign
Phippsy
external usenet poster
 
Posts: 113
Default 1-1 relationship

Thank you all so much.
I suppose I am thinking of the primary table where referentioal integrity
has been set. Am I correct in thinking that the cascade only works one way -
ie from the 'Primary' (left hand) table. If you delete a record from the
'Primary' table it cascades through to the Child (Secondary) table but it
doesn't work if you delete a record from the Secondary table.

Debbie

"John Vinson" wrote:

On Tue, 17 Oct 2006 17:25:02 -0700, NetworkTrade
wrote:

Obviously they must share the same field ID...so neither/either is
inherently primary per se...in ones mind the table that is used most
frequently I tend to think of as the primary....


Well, as implemented in Access (and SQL/Server, Oracle, MySQL, and
others), you're mistaken; even one to one relationships have
directionality. If you are going to enforce referential integrity,
there must be a record in the "primary" or "left hand" table, and
there may or may not be a record in the child table. Strictly
speaking, it's a "One to (Zero or one)" relationship.

John W. Vinson[MVP]

  #7  
Old October 18th, 2006, 04:32 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default 1-1 relationship

You described "Cascading Delete". This is an OPTIONAL setting on the
referential integrity.

Yes, it is directional.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Phippsy" wrote in message
...
Thank you all so much.
I suppose I am thinking of the primary table where referentioal integrity
has been set. Am I correct in thinking that the cascade only works one
way -
ie from the 'Primary' (left hand) table. If you delete a record from the
'Primary' table it cascades through to the Child (Secondary) table but it
doesn't work if you delete a record from the Secondary table.

Debbie

"John Vinson" wrote:

On Tue, 17 Oct 2006 17:25:02 -0700, NetworkTrade
wrote:

Obviously they must share the same field ID...so neither/either is
inherently primary per se...in ones mind the table that is used most
frequently I tend to think of as the primary....


Well, as implemented in Access (and SQL/Server, Oracle, MySQL, and
others), you're mistaken; even one to one relationships have
directionality. If you are going to enforce referential integrity,
there must be a record in the "primary" or "left hand" table, and
there may or may not be a record in the child table. Strictly
speaking, it's a "One to (Zero or one)" relationship.

John W. Vinson[MVP]



  #8  
Old October 18th, 2006, 05:15 PM posted to microsoft.public.access.tablesdbdesign
Phippsy
external usenet poster
 
Posts: 113
Default 1-1 relationship

Yes thanks I realise that - I was just giving a for example!

"Jeff Boyce" wrote:

You described "Cascading Delete". This is an OPTIONAL setting on the
referential integrity.

Yes, it is directional.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Phippsy" wrote in message
...
Thank you all so much.
I suppose I am thinking of the primary table where referentioal integrity
has been set. Am I correct in thinking that the cascade only works one
way -
ie from the 'Primary' (left hand) table. If you delete a record from the
'Primary' table it cascades through to the Child (Secondary) table but it
doesn't work if you delete a record from the Secondary table.

Debbie

"John Vinson" wrote:

On Tue, 17 Oct 2006 17:25:02 -0700, NetworkTrade
wrote:

Obviously they must share the same field ID...so neither/either is
inherently primary per se...in ones mind the table that is used most
frequently I tend to think of as the primary....

Well, as implemented in Access (and SQL/Server, Oracle, MySQL, and
others), you're mistaken; even one to one relationships have
directionality. If you are going to enforce referential integrity,
there must be a record in the "primary" or "left hand" table, and
there may or may not be a record in the child table. Strictly
speaking, it's a "One to (Zero or one)" relationship.

John W. Vinson[MVP]




 




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 09:31 AM.


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