Relationships

[Guest]

I am fairly new to setting up Relationships. Is it always advisable to have
an autonumber field in every table when setting relationships. I notice this
is the case with the Northwind Data Base. What are the benefits?
Secondly, is it fairly rare to use 1 to 1 and Many to Many relationships?.
Could you give me an example of Many to Many. I would assume you would use 1
to 1 where one individual, say one person, can only have one Tax file number.
Thanks

 

[Jeff Boyce]

Roger

Be aware that there is considerable fervor among the folks in the newsgroups
(tablesdbdesign, particularly) about the appropriateness/purity/necessity of
using Autonumber primary key fields. From a relational design standpoint,
your tables MUST have a primary key -- otherwise, there'd be no way to tell
one row from another.

But that key can be a single field or the combination of several, or totally
arbitrary (e.g., Autonumber) or a "natural" key (i.e., a "fact" about the
row that uniquely identifies it).

A 1-1 relationship is fairly rare in the 'groups. The two situations that
pop up involve either a security issue or, more commonly, a "subtype"
situation.

A m-m relationship is NOT directly possible in Access. To achieve this,
you'd need three tables. The two (m-m) tables, plus one more table that
sits "in the middle" between them, showing the valid combinations of rows
from the other two.

An example of this might be students and classes. One student can register
for many classes, and one class may contain many students. A third table
(?Registrations) contains StudentID and ClassID for each valid registration.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[chris.nebinger]

Jeff is spot on (I'm not British, I just love saying that!).

I'll elaborate on 1-1 relationships a little bit. In your example,
with one person having one tax id, that would be a field in the table.
There would be no reason to break that out to a different table.

A good example of a 1-1 need would be in the construction field. Say
you are writing an application to manage a companies construction
contracts. This company does both residential and commercial. One
contract can be either commercial or residential, but not both. Both
contracts have alot of the same information, but commercial contracts
require 50 additional fields that residential does not. Likewise,
residential contracts require 50 fields that commercial does not.
Assume they share 50 fields in common.

Now, you could have a Contracts table with 150 fields, or you could
have a Contracts Table with 50 fields (the common ones), a Commercial
with 50 fields (linked on ContractNumber), and a residential with 50
fields. The benefit to having 3 tables is that on any given contract,
50 fields are going unused, but still require storage space. By
breaking out the contract data, then you are actually using less space
by using 3 tables, especially as the number of records grows.

As far as Autonumbers go, I don't think it matters in practice one way
or another. If you have a existing unique identier (SSN, Contract
Number, Employee ID, etc.) then you could use that. You could also
create a AutoNumber, and save both. The main thing is not to use a
AutoNumber for an audit purpose, as you WILL have gaps in the sequence
number. An AutoNumber should be to guarntee uniqueness, and NOTHING
else. Too many people get wrapped into wanting to reset AutoNumbers,
start with a certain number, etc.

I do tend to avoid them for the simple reason of autonumbers make
merging data from two databases a pain. If there is an available
unique key, I try and use it. Well, I do tend to avoid SSN's, but that
is due to having mine stolen.


Chris Nebinger

 

[Guest]

Thanks (I'm not British for your constructive comments). Any chance I could
email to you (zipped) what I have done to see if i am on the right track?
Just let me know one way or the other?
Thanks

 

[Jeff Boyce]

Roger

The newsgroups have a convention -- what you post here gets replied to here.
That way, other folks who might be trying to figure the same issue out can
see what worked (and didn't).

Try describing what you've done -- you'll get more "eyes" on it...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Guest]

Thanks At least I know where I stand

Roger

The newsgroups have a convention -- what you post here gets replied to here.
That way, other folks who might be trying to figure the same issue out can
see what worked (and didn't).

Try describing what you've done -- you'll get more "eyes" on it...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[chris.nebinger]

I would be happy to take a look at whatever you send me.


Chris

 

[Guest]

Ok Many thanks Chris
Will zip and send with cover note
Regards
Roger