one to one relationship

[Vic]

Hi
I am working on a db that has just one table with 75 fields.
I'd like to split the table up. some of the fields can be put into separate
tables and be joined by one-to-many relationships.
But there is a lot of the fields that will only have one record.
For example the Customer is in the main table with address, phone etc and in
another table there will be the extra information for him like Registration,
or Date joined which will only ever be once. Is that when you would use
on-to-one?
Can you please tell me how to do a one-to-one relationship. In Help there
is a reference to it but there is no explanation or detail on how to.
In the relationship window I drag Customer ID to Customer ID but the 'Create
join' window just says 'one to many' at the bottom no matter what option I
use.
Thanks
V

 

[Guest]

Hi Vic,

you should first create the primary key and the other indexes in your
related tables, then create the relationships enforcing referential
integrity. Access will set 1-to-1 for you.

--
Luiz Cláudio C. V. Rocha
Coordenador de Projetos FórumAccess
São Paulo - Brasil
MVP Office
http://www.msmvps.com/officedev

 

[John Vinson]

Hi
I am working on a db that has just one table with 75 fields.
I'd like to split the table up. some of the fields can be put into separate
tables and be joined by one-to-many relationships.

That's certainly important to do - and may be all that you need to do.

But there is a lot of the fields that will only have one record.
For example the Customer is in the main table with address, phone etc and in
another table there will be the extra information for him like Registration,
or Date joined which will only ever be once. Is that when you would use
on-to-one?

If you're *certain* that these fields will never be repeated (could
someone join, resign, and join a second time? if so would that be two
registrations?), then I'd really recommend that you use a single
table. There's no major overhead in having Null fields - Access
doesn't waste disk space on them, regardless of the size of the field.

There's a fair bit of overhead linking one to one tables, and they're
needed only in certain circumstances. One common reason to use them is
what's called "Subclassing": if an Entity has more than one distinct
sets of KIND of attributes. For instance, an Insurance policy might
have optional coverage for any combination of home insurance, auto
insurance, business insurance, life insurance, etc.; it would make
sense in this case to have a master policy table with the fields in
common to all of them, related one-to-one to tables with the
particular fields needed for the specific coverages.

Can you please tell me how to do a one-to-one relationship. In Help there
is a reference to it but there is no explanation or detail on how to.
In the relationship window I drag Customer ID to Customer ID but the 'Create
join' window just says 'one to many' at the bottom no matter what option I
use.

If there is a unique index (such as a Primary Key) on the CustomerID
field in both tables, the relationship will be one to one. If there is
a unique index in only one of the tables, that table will be the "one"
side. If there is no such index in either table, the relationship will
be indefinite.

In your case I would suggest not splitting the unique fields. Sure,
normalize the fields that are validly "many" side fields; but if the
customer's [Date Joined] is properly a static nonrepeating attribute
of that customer, I see no benefit to pulling it out into another
table.


John W. Vinson[MVP]

 

[Vic]

Thanks you for your advice. Firstly for explain the one-to-one steps, it
made more sense now but mostly I'm so glad what you said coz I'm getting a
real headache trying to figure out how to spit the rest of this table. I
thought it wasn't good to have a table with so many fields, but for those
that are static, they may as well stay put.
V

Hi
I am working on a db that has just one table with 75 fields.
I'd like to split the table up. some of the fields can be put into
separate
tables and be joined by one-to-many relationships.

That's certainly important to do - and may be all that you need to do.

But there is a lot of the fields that will only have one record.
For example the Customer is in the main table with address, phone etc and
in
another table there will be the extra information for him like
Registration,
or Date joined which will only ever be once. Is that when you would use
on-to-one?

If you're *certain* that these fields will never be repeated (could
someone join, resign, and join a second time? if so would that be two
registrations?), then I'd really recommend that you use a single
table. There's no major overhead in having Null fields - Access
doesn't waste disk space on them, regardless of the size of the field.

There's a fair bit of overhead linking one to one tables, and they're
needed only in certain circumstances. One common reason to use them is
what's called "Subclassing": if an Entity has more than one distinct
sets of KIND of attributes. For instance, an Insurance policy might
have optional coverage for any combination of home insurance, auto
insurance, business insurance, life insurance, etc.; it would make
sense in this case to have a master policy table with the fields in
common to all of them, related one-to-one to tables with the
particular fields needed for the specific coverages.

Can you please tell me how to do a one-to-one relationship. In Help there
is a reference to it but there is no explanation or detail on how to.
In the relationship window I drag Customer ID to Customer ID but the
'Create
join' window just says 'one to many' at the bottom no matter what option I
use.

If there is a unique index (such as a Primary Key) on the CustomerID
field in both tables, the relationship will be one to one. If there is
a unique index in only one of the tables, that table will be the "one"
side. If there is no such index in either table, the relationship will
be indefinite.

In your case I would suggest not splitting the unique fields. Sure,
normalize the fields that are validly "many" side fields; but if the
customer's [Date Joined] is properly a static nonrepeating attribute
of that customer, I see no benefit to pulling it out into another
table.


John W. Vinson[MVP]

 

[Pieter Wijnen]

It's better to only select the fields you need for a form/query/report than
actually split the table 1-to-1

Pieter

Thanks you for your advice. Firstly for explain the one-to-one steps, it
made more sense now but mostly I'm so glad what you said coz I'm getting a
real headache trying to figure out how to spit the rest of this table. I
thought it wasn't good to have a table with so many fields, but for those
that are static, they may as well stay put.
V

Hi
I am working on a db that has just one table with 75 fields.
I'd like to split the table up. some of the fields can be put into
separate
tables and be joined by one-to-many relationships.

That's certainly important to do - and may be all that you need to do.

But there is a lot of the fields that will only have one record.
For example the Customer is in the main table with address, phone etc and
in
another table there will be the extra information for him like
Registration,
or Date joined which will only ever be once. Is that when you would use
on-to-one?

If you're *certain* that these fields will never be repeated (could
someone join, resign, and join a second time? if so would that be two
registrations?), then I'd really recommend that you use a single
table. There's no major overhead in having Null fields - Access
doesn't waste disk space on them, regardless of the size of the field.

There's a fair bit of overhead linking one to one tables, and they're
needed only in certain circumstances. One common reason to use them is
what's called "Subclassing": if an Entity has more than one distinct
sets of KIND of attributes. For instance, an Insurance policy might
have optional coverage for any combination of home insurance, auto
insurance, business insurance, life insurance, etc.; it would make
sense in this case to have a master policy table with the fields in
common to all of them, related one-to-one to tables with the
particular fields needed for the specific coverages.

Can you please tell me how to do a one-to-one relationship. In Help
there
is a reference to it but there is no explanation or detail on how to.
In the relationship window I drag Customer ID to Customer ID but the
'Create
join' window just says 'one to many' at the bottom no matter what option
I
use.

If there is a unique index (such as a Primary Key) on the CustomerID
field in both tables, the relationship will be one to one. If there is
a unique index in only one of the tables, that table will be the "one"
side. If there is no such index in either table, the relationship will
be indefinite.

In your case I would suggest not splitting the unique fields. Sure,
normalize the fields that are validly "many" side fields; but if the
customer's [Date Joined] is properly a static nonrepeating attribute
of that customer, I see no benefit to pulling it out into another
table.


John W. Vinson[MVP]

 

[Pieter Wijnen]

It's better to only select the fields you need for a form/query/report than
actually split the table 1-to-1

Pieter

Thanks you for your advice. Firstly for explain the one-to-one steps, it
made more sense now but mostly I'm so glad what you said coz I'm getting a
real headache trying to figure out how to spit the rest of this table. I
thought it wasn't good to have a table with so many fields, but for those
that are static, they may as well stay put.
V

Hi
I am working on a db that has just one table with 75 fields.
I'd like to split the table up. some of the fields can be put into
separate
tables and be joined by one-to-many relationships.

That's certainly important to do - and may be all that you need to do.

But there is a lot of the fields that will only have one record.
For example the Customer is in the main table with address, phone etc and
in
another table there will be the extra information for him like
Registration,
or Date joined which will only ever be once. Is that when you would use
on-to-one?

If you're *certain* that these fields will never be repeated (could
someone join, resign, and join a second time? if so would that be two
registrations?), then I'd really recommend that you use a single
table. There's no major overhead in having Null fields - Access
doesn't waste disk space on them, regardless of the size of the field.

There's a fair bit of overhead linking one to one tables, and they're
needed only in certain circumstances. One common reason to use them is
what's called "Subclassing": if an Entity has more than one distinct
sets of KIND of attributes. For instance, an Insurance policy might
have optional coverage for any combination of home insurance, auto
insurance, business insurance, life insurance, etc.; it would make
sense in this case to have a master policy table with the fields in
common to all of them, related one-to-one to tables with the
particular fields needed for the specific coverages.

Can you please tell me how to do a one-to-one relationship. In Help
there
is a reference to it but there is no explanation or detail on how to.
In the relationship window I drag Customer ID to Customer ID but the
'Create
join' window just says 'one to many' at the bottom no matter what option
I
use.

If there is a unique index (such as a Primary Key) on the CustomerID
field in both tables, the relationship will be one to one. If there is
a unique index in only one of the tables, that table will be the "one"
side. If there is no such index in either table, the relationship will
be indefinite.

In your case I would suggest not splitting the unique fields. Sure,
normalize the fields that are validly "many" side fields; but if the
customer's [Date Joined] is properly a static nonrepeating attribute
of that customer, I see no benefit to pulling it out into another
table.


John W. Vinson[MVP]

--

 

[John Vinson]

I
thought it wasn't good to have a table with so many fields

It is unusually large - but if they are valid, atomic, nonrepeating
attributes dependent *only* upon the Primary Key, then the design may
be correct.

If you wish to post the fieldnames and brief descriptions, someone
might be able to give you a different viewpoint on whether they fit
that description.

John W. Vinson[MVP]