|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
|
|
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
http://mvp.support.microsoft.com/
Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
Microsoft Registered Partner
https://partner.microsoft.com/
"Roger Bell" <RogerBell[ at ]discussions.microsoft.com> wrote in message
news:1A696DC0-7B71-4F27-A55F-555031AF369C[ at ]microsoft.com...
[Quoted Text] > 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 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
Jeff Boyce wrote:
[Quoted Text] > 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 > http://mvp.support.microsoft.com/> > Microsoft IT Academy Program Mentor > http://microsoftitacademy.com/> > Microsoft Registered Partner > https://partner.microsoft.com/> > > "Roger Bell" <RogerBell[ at ]discussions.microsoft.com> wrote in message > news:1A696DC0-7B71-4F27-A55F-555031AF369C[ at ]microsoft.com... > > 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
|
|
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
"chris.nebinger[ at ]gmail.com" wrote:
[Quoted Text] > 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 > > > > Jeff Boyce wrote: > > 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 > > http://mvp.support.microsoft.com/> > > > Microsoft IT Academy Program Mentor > > http://microsoftitacademy.com/> > > > Microsoft Registered Partner > > https://partner.microsoft.com/> > > > > > "Roger Bell" <RogerBell[ at ]discussions.microsoft.com> wrote in message > > news:1A696DC0-7B71-4F27-A55F-555031AF369C[ at ]microsoft.com... > > > 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 > > |
|
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
http://mvp.support.microsoft.com/
Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
Microsoft Registered Partner
https://partner.microsoft.com/
"Roger Bell" <RogerBell[ at ]discussions.microsoft.com> wrote in message
news:60B789E6-9EFD-4032-B86A-DC153F56F80F[ at ]microsoft.com...
[Quoted Text] > 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
>
>
> "chris.nebinger[ at ]gmail.com" wrote:
>
> > 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
> >
> >
> >
> > Jeff Boyce wrote:
> > > 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
> > > http://mvp.support.microsoft.com/
> > >
> > > Microsoft IT Academy Program Mentor
> > > http://microsoftitacademy.com/
> > >
> > > Microsoft Registered Partner
> > > https://partner.microsoft.com/
> > >
> > >
> > > "Roger Bell" <RogerBell[ at ]discussions.microsoft.com> wrote in message
> > > news:1A696DC0-7B71-4F27-A55F-555031AF369C[ at ]microsoft.com...
> > > > 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
> >
> >
|
|
Thanks At least I know where I stand
"Jeff Boyce" wrote:
[Quoted Text] > 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 > http://mvp.support.microsoft.com/> > Microsoft IT Academy Program Mentor > http://microsoftitacademy.com/> > Microsoft Registered Partner > https://partner.microsoft.com/> > > > "Roger Bell" <RogerBell[ at ]discussions.microsoft.com> wrote in message > news:60B789E6-9EFD-4032-B86A-DC153F56F80F[ at ]microsoft.com... > > 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 > > > > > > "chris.nebinger[ at ]gmail.com" wrote: > > > > > 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 > > > > > > > > > > > > Jeff Boyce wrote: > > > > 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 > > > > http://mvp.support.microsoft.com/> > > > > > > > Microsoft IT Academy Program Mentor > > > > http://microsoftitacademy.com/> > > > > > > > Microsoft Registered Partner > > > > https://partner.microsoft.com/> > > > > > > > > > > > "Roger Bell" <RogerBell[ at ]discussions.microsoft.com> wrote in message > > > > news:1A696DC0-7B71-4F27-A55F-555031AF369C[ at ]microsoft.com... > > > > > 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 > > > > > > > > |
|
I would be happy to take a look at whatever you send me.
Chris
Roger Bell wrote:
[Quoted Text] > Thanks At least I know where I stand > > > "Jeff Boyce" wrote: > > > 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 > > http://mvp.support.microsoft.com/> > > > Microsoft IT Academy Program Mentor > > http://microsoftitacademy.com/> > > > Microsoft Registered Partner > > https://partner.microsoft.com/> > > > > > > > "Roger Bell" <RogerBell[ at ]discussions.microsoft.com> wrote in message > > news:60B789E6-9EFD-4032-B86A-DC153F56F80F[ at ]microsoft.com... > > > 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 > > > > > > > > > "chris.nebinger[ at ]gmail.com" wrote: > > > > > > > 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 > > > > > > > > > > > > > > > > Jeff Boyce wrote: > > > > > 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 > > > > > http://mvp.support.microsoft.com/> > > > > > > > > > Microsoft IT Academy Program Mentor > > > > > http://microsoftitacademy.com/> > > > > > > > > > Microsoft Registered Partner > > > > > https://partner.microsoft.com/> > > > > > > > > > > > > > > "Roger Bell" <RogerBell[ at ]discussions.microsoft.com> wrote in message > > > > > news:1A696DC0-7B71-4F27-A55F-555031AF369C[ at ]microsoft.com... > > > > > > 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 > > > > > > > > > > > >
|
|
Ok Many thanks Chris
Will zip and send with cover note
Regards
Roger
"chris.nebinger[ at ]gmail.com" wrote:
[Quoted Text] > I would be happy to take a look at whatever you send me. > > > Chris > > Roger Bell wrote: > > Thanks At least I know where I stand > > > > > > "Jeff Boyce" wrote: > > > > > 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 > > > http://mvp.support.microsoft.com/> > > > > > Microsoft IT Academy Program Mentor > > > http://microsoftitacademy.com/> > > > > > Microsoft Registered Partner > > > https://partner.microsoft.com/> > > > > > > > > > > > "Roger Bell" <RogerBell[ at ]discussions.microsoft.com> wrote in message > > > news:60B789E6-9EFD-4032-B86A-DC153F56F80F[ at ]microsoft.com... > > > > 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 > > > > > > > > > > > > "chris.nebinger[ at ]gmail.com" wrote: > > > > > > > > > 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 > > > > > > > > > > > > > > > > > > > > Jeff Boyce wrote: > > > > > > 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 > > > > > > http://mvp.support.microsoft.com/> > > > > > > > > > > > Microsoft IT Academy Program Mentor > > > > > > http://microsoftitacademy.com/> > > > > > > > > > > > Microsoft Registered Partner > > > > > > https://partner.microsoft.com/> > > > > > > > > > > > > > > > > > "Roger Bell" <RogerBell[ at ]discussions.microsoft.com> wrote in message > > > > > > news:1A696DC0-7B71-4F27-A55F-555031AF369C[ at ]microsoft.com... > > > > > > > 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 > > > > > > > > > > > > > > > > > > |
|
|