1-1 relationship

[Phippsy]

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

 

[Graham Mandeno]

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.

 

[Jeff Boyce]

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

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[NetworkTrade]

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....

 

[John Vinson]

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]

 

[Phippsy]

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

 

[Jeff Boyce]

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]

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