one to one relationships

[Catherine Jo Morgan]

Does Access store two tables which have a one-to-one relationship, as if
they were just one table? I ask because in the db I'm making, there are a
few instances in which a one-to-one relationship seems appropriate. But my
reasoning is that for the same entity, sometimes several fields are relevant
only for some records. So putting those fields into a separate table would
keep the main table from having a lot of empty fields for many records.

But if Access stores the records as if they were in one big table, there's
no point in my splitting the table into two, is there?

This is still the db for the recreational tree climbing business.

Here's an example: tblJobProposals. The fields in this table all apply to
one or two people wanting a climb. If someone wants to arrange a climb for a
group, six more fields apply. So I make tblGroupProposals, with the same
JobProposalID so it's linked to the other JobProposal fields. (Also, of
course, the JobProposalID is a FK in other tables, so I don't want two
different PKs.)

Does it save space and make sense to do it this way? TIA

[Larry Linson]

No, Access does not store one-to-one relationship tables as though they were
a single table. If there is actually a record in the related table for each
record in the main table, there will be a bit of additional overhead. If
there are many empty records in the related table, you may save a bit of
disk space (but, on the other hand, these days disk space is both cheap and
fast). My choice would be on the basis of "the way I think of this as
modeling the real world, does it make sense to keep them separate" rather
than saving a little disk space. I think your arrangement makes sense for
two separate tables.

Larry Linson
Microsoft Access MVP

"Catherine Jo Morgan" <cj******@hemc.net> wrote in message
news:B4********************@hemc.net...

Does Access store two tables which have a one-to-one relationship, as if
they were just one table? I ask because in the db I'm making, there are a
few instances in which a one-to-one relationship seems appropriate. But my
reasoning is that for the same entity, sometimes several fields are relevant only for some records. So putting those fields into a separate table would
keep the main table from having a lot of empty fields for many records.

But if Access stores the records as if they were in one big table, there's
no point in my splitting the table into two, is there?

This is still the db for the recreational tree climbing business.

Here's an example: tblJobProposals. The fields in this table all apply to
one or two people wanting a climb. If someone wants to arrange a climb for a group, six more fields apply. So I make tblGroupProposals, with the same
JobProposalID so it's linked to the other JobProposal fields. (Also, of
course, the JobProposalID is a FK in other tables, so I don't want two
different PKs.)

Does it save space and make sense to do it this way? TIA