Confused about one-to-many or many-to-many relationships

Hello,

I am in the process of designing the tables for a parcel acquisition and
lease database.

I will have two tables, tblPropertyInfo (tPI) and tblOwnerInfo (tOI).
The tPI will have the Tax Assessment ID (TAID)as the PK. The tOI will
have the SSN of the owner as the PK. Here are some relationships that
exist in each table.

Table structures
-the tOI will also have name, phone, and address fields, with the tPI's
TAID as the FK*, and have the FK indexed and to accept duplicates.

- the tPI will also have township, range, section, tract fields, with
the fOI's SSN as the FK*,and have the FK indexed and to accept
duplicates.

* NOTE: I'm assuming that this is a proper, necessary implementation,
putting the aforementioned FK's as shown in their respective table
structures, right?

Here are some scenarios:

A) John and Sally Smith own parcel A
B) John Smith also owns parcel B, and C
C) Sally Smith owns 50% of parcel D
D) Joe Johnson (Sally's brother) owns the other 50% of parcel D

Relationships
1 - Scenario A is a Many-to-One relationship (MTO)
2 - Scenario B is a One-to-Many relationship (OTM)
3 - Scenario C is a One-to-One relationship (OTO)
4 - Scenario D is a One-to-One relationship (OTO)

Here are my questions:
1 - Are my relationships correct? Or are there any Many-to-Many
relationships shown?

2- In the process of doing the relationship diagram, when I choose the
PK from the tOI (SSN) and drag it to the tPI FK (SSN), which of those 3
options (see ** below) should I choose?

3 - When I constructing the form and say I'm making the field SSN, for
example. How can I enable it so that I don't have to enter the SSN twice
in the two tables? Theoretically, I should only have to enter it into
the tOI. But I'm missing something here, I think

---------------------------------------
** Edit Relationships Dialog/Join Type Button/Join Properties: 1) Only
include rows where... 2) Include ALL records from 'tblOwnerInfo' and
only those from the 'tblPropertyInfo' where the joined fields are
equal. 3) Include ALL records from 'tblPropertyInfo' and only those
from the 'tblOwnerInfo' where the joined fields are equal.
---------------------------------------

As a side note, can anyone recommend a good site that explains Join
Properties well, to a beginner like myself?

I know that these are VERY fundamental issues for 90% of those of you
who read these posts, but I REALLY need to get a grip on this before I
move on to create the rest of the database for this project.

TIA.

Phil.

On Wed, 06 Jul 2005 11:48:38 -0700, CAD Fiend
wrote:

Hello,

I am in the process of designing the tables for a parcel acquisition and
lease database.

I will have two tables, tblPropertyInfo (tPI) and tblOwnerInfo (tOI).
The tPI will have the Tax Assessment ID (TAID)as the PK. The tOI will
have the SSN of the owner as the PK. Here are some relationships that
exist in each table.

Table structures
-the tOI will also have name, phone, and address fields, with the tPI's
TAID as the FK*, and have the FK indexed and to accept duplicates.

- the tPI will also have township, range, section, tract fields, with
the fOI's SSN as the FK*,and have the FK indexed and to accept
duplicates.

* NOTE: I'm assuming that this is a proper, necessary implementation,
putting the aforementioned FK's as shown in their respective table
structures, right?

No. It is not correct. There should be NOTHING concerning the property
in tOI, and NOTHING concerning the owner in tPI.

Here are some scenarios:

A) John and Sally Smith own parcel A
B) John Smith also owns parcel B, and C
C) Sally Smith owns 50% of parcel D
D) Joe Johnson (Sally's brother) owns the other 50% of parcel D

Relationships
1 - Scenario A is a Many-to-One relationship (MTO)
2 - Scenario B is a One-to-Many relationship (OTM)
3 - Scenario C is a One-to-One relationship (OTO)
4 - Scenario D is a One-to-One relationship (OTO)

Well... yes. But these are relationships between *individual
entities*. What you need for Access is the relationship between
*TABLES*, or (in the real world) between classes of Entities, rather
than individual instances of those entities.

Here are my questions:
1 - Are my relationships correct? Or are there any Many-to-Many
relationships shown?

There is one Many to Many relationship. That's all.

A given Property can be owned by zero, one, or many Owners.
A given Owner can own zero, one, or many Properties.
Property -- many-many -- Owner

2- In the process of doing the relationship diagram, when I choose the
PK from the tOI (SSN) and drag it to the tPI FK (SSN), which of those 3
options (see ** below) should I choose?

None of them, because you don't have a SSN in tPI, and you don't have
a property ID in tOI.

3 - When I constructing the form and say I'm making the field SSN, for
example. How can I enable it so that I don't have to enter the SSN twice
in the two tables? Theoretically, I should only have to enter it into
the tOI. But I'm missing something here, I think

You need a THIRD TABLE - Ownership:

tOwnership
SSN FK to tOI, who owns the property
TAID FK to tPI, what property do they own
other fields, e.g. percentage ownership defaulting to 1.0

You would drag SSN from tOP to SSN in tOwnership; the three types of
relationship are *almost* irrelevant, since they don't affect
relational integrity. All they do is give you a default join type on a
new query. In this case use option 1 - you are not interested in outer
joins at this point, all you need to know is who owns what! Similarly,
you'ld drag TAID from tPI to TAID in tOwnership.

In your scenarios above, tOwnership would have records with (using
names in place of ID's)

John Smith Parcel A 1.0 community property?
Sally Smith Parcel A 1.0 community property?
John Smith Parcel B 1.0
John Smith Parcel C 1.0
Sally Smith Parcel D 0.5
Joe Johnson Parcel D 0.5


John W. Vinson[MVP]

Thank you John, for taking the time to explain this properly to me. I
apprieciate your and everyone's patience when it comes to explaining
(probably for the umteenth time) concepts that are probably old hat to you
all, but new to newbies like myself.

Phil.

John Vinson wrote:

On Wed, 06 Jul 2005 11:48:38 -0700, CAD Fiend
wrote:

Hello,

I am in the process of designing the tables for a parcel acquisition and
lease database.

I will have two tables, tblPropertyInfo (tPI) and tblOwnerInfo (tOI).
The tPI will have the Tax Assessment ID (TAID)as the PK. The tOI will
have the SSN of the owner as the PK. Here are some relationships that
exist in each table.

Table structures
-the tOI will also have name, phone, and address fields, with the tPI's
TAID as the FK*, and have the FK indexed and to accept duplicates.

- the tPI will also have township, range, section, tract fields, with
the fOI's SSN as the FK*,and have the FK indexed and to accept
duplicates.

* NOTE: I'm assuming that this is a proper, necessary implementation,
putting the aforementioned FK's as shown in their respective table
structures, right?

No. It is not correct. There should be NOTHING concerning the property
in tOI, and NOTHING concerning the owner in tPI.

Here are some scenarios:

A) John and Sally Smith own parcel A
B) John Smith also owns parcel B, and C
C) Sally Smith owns 50% of parcel D
D) Joe Johnson (Sally's brother) owns the other 50% of parcel D

Relationships
1 - Scenario A is a Many-to-One relationship (MTO)
2 - Scenario B is a One-to-Many relationship (OTM)
3 - Scenario C is a One-to-One relationship (OTO)
4 - Scenario D is a One-to-One relationship (OTO)

Well... yes. But these are relationships between *individual
entities*. What you need for Access is the relationship between
*TABLES*, or (in the real world) between classes of Entities, rather
than individual instances of those entities.

Here are my questions:
1 - Are my relationships correct? Or are there any Many-to-Many
relationships shown?

There is one Many to Many relationship. That's all.

A given Property can be owned by zero, one, or many Owners.
A given Owner can own zero, one, or many Properties.
Property -- many-many -- Owner

2- In the process of doing the relationship diagram, when I choose the
PK from the tOI (SSN) and drag it to the tPI FK (SSN), which of those 3
options (see ** below) should I choose?

None of them, because you don't have a SSN in tPI, and you don't have
a property ID in tOI.

3 - When I constructing the form and say I'm making the field SSN, for
example. How can I enable it so that I don't have to enter the SSN twice
in the two tables? Theoretically, I should only have to enter it into
the tOI. But I'm missing something here, I think

You need a THIRD TABLE - Ownership:

tOwnership
SSN FK to tOI, who owns the property
TAID FK to tPI, what property do they own
other fields, e.g. percentage ownership defaulting to 1.0

You would drag SSN from tOP to SSN in tOwnership; the three types of
relationship are *almost* irrelevant, since they don't affect
relational integrity. All they do is give you a default join type on a
new query. In this case use option 1 - you are not interested in outer
joins at this point, all you need to know is who owns what! Similarly,
you'ld drag TAID from tPI to TAID in tOwnership.

In your scenarios above, tOwnership would have records with (using
names in place of ID's)

John Smith Parcel A 1.0 community property?
Sally Smith Parcel A 1.0 community property?
John Smith Parcel B 1.0
John Smith Parcel C 1.0
Sally Smith Parcel D 0.5
Joe Johnson Parcel D 0.5

John W. Vinson[MVP]

Phil,

In addition to John's great explanation, you might find this helpful...
http://accesstips.datamanagementsolutions.biz/many.mht

--
Steve Schapel, Microsoft Access MVP


CAD Fiend wrote:
Hello,

I am in the process of designing the tables for a parcel acquisition and
lease database.

I will have two tables, tblPropertyInfo (tPI) and tblOwnerInfo (tOI).
The tPI will have the Tax Assessment ID (TAID)as the PK. The tOI will
have the SSN of the owner as the PK. Here are some relationships that
exist in each table.

Table structures
-the tOI will also have name, phone, and address fields, with the tPI's
TAID as the FK*, and have the FK indexed and to accept duplicates.

- the tPI will also have township, range, section, tract fields, with
the fOI's SSN as the FK*,and have the FK indexed and to accept
duplicates.

* NOTE: I'm assuming that this is a proper, necessary implementation,
putting the aforementioned FK's as shown in their respective table
structures, right?

Here are some scenarios:

A) John and Sally Smith own parcel A
B) John Smith also owns parcel B, and C
C) Sally Smith owns 50% of parcel D
D) Joe Johnson (Sally's brother) owns the other 50% of parcel D

Relationships
1 - Scenario A is a Many-to-One relationship (MTO)
2 - Scenario B is a One-to-Many relationship (OTM)
3 - Scenario C is a One-to-One relationship (OTO)
4 - Scenario D is a One-to-One relationship (OTO)

Here are my questions:
1 - Are my relationships correct? Or are there any Many-to-Many
relationships shown?

2- In the process of doing the relationship diagram, when I choose the
PK from the tOI (SSN) and drag it to the tPI FK (SSN), which of those 3
options (see ** below) should I choose?

3 - When I constructing the form and say I'm making the field SSN, for
example. How can I enable it so that I don't have to enter the SSN twice
in the two tables? Theoretically, I should only have to enter it into
the tOI. But I'm missing something here, I think

---------------------------------------
** Edit Relationships Dialog/Join Type Button/Join Properties: 1) Only
include rows where... 2) Include ALL records from 'tblOwnerInfo' and
only those from the 'tblPropertyInfo' where the joined fields are
equal. 3) Include ALL records from 'tblPropertyInfo' and only those
from the 'tblOwnerInfo' where the joined fields are equal.
---------------------------------------

As a side note, can anyone recommend a good site that explains Join
Properties well, to a beginner like myself?

I know that these are VERY fundamental issues for 90% of those of you
who read these posts, but I REALLY need to get a grip on this before I
move on to create the rest of the database for this project.

TIA.

Phil.

Thanks Steve, I will check it out.

Steve Schapel wrote:

Phil,

In addition to John's great explanation, you might find this helpful...
http://accesstips.datamanagementsolutions.biz/many.mht

--
Steve Schapel, Microsoft Access MVP

CAD Fiend wrote:
Hello,

I am in the process of designing the tables for a parcel acquisition and
lease database.

I will have two tables, tblPropertyInfo (tPI) and tblOwnerInfo (tOI).
The tPI will have the Tax Assessment ID (TAID)as the PK. The tOI will
have the SSN of the owner as the PK. Here are some relationships that
exist in each table.

Table structures
-the tOI will also have name, phone, and address fields, with the tPI's
TAID as the FK*, and have the FK indexed and to accept duplicates.

- the tPI will also have township, range, section, tract fields, with
the fOI's SSN as the FK*,and have the FK indexed and to accept
duplicates.

* NOTE: I'm assuming that this is a proper, necessary implementation,
putting the aforementioned FK's as shown in their respective table
structures, right?

Here are some scenarios:

A) John and Sally Smith own parcel A
B) John Smith also owns parcel B, and C
C) Sally Smith owns 50% of parcel D
D) Joe Johnson (Sally's brother) owns the other 50% of parcel D

Relationships
1 - Scenario A is a Many-to-One relationship (MTO)
2 - Scenario B is a One-to-Many relationship (OTM)
3 - Scenario C is a One-to-One relationship (OTO)
4 - Scenario D is a One-to-One relationship (OTO)

Here are my questions:
1 - Are my relationships correct? Or are there any Many-to-Many
relationships shown?

2- In the process of doing the relationship diagram, when I choose the
PK from the tOI (SSN) and drag it to the tPI FK (SSN), which of those 3
options (see ** below) should I choose?

3 - When I constructing the form and say I'm making the field SSN, for
example. How can I enable it so that I don't have to enter the SSN twice
in the two tables? Theoretically, I should only have to enter it into
the tOI. But I'm missing something here, I think

---------------------------------------
** Edit Relationships Dialog/Join Type Button/Join Properties: 1) Only
include rows where... 2) Include ALL records from 'tblOwnerInfo' and
only those from the 'tblPropertyInfo' where the joined fields are
equal. 3) Include ALL records from 'tblPropertyInfo' and only those
from the 'tblOwnerInfo' where the joined fields are equal.
---------------------------------------

As a side note, can anyone recommend a good site that explains Join
Properties well, to a beginner like myself?

I know that these are VERY fundamental issues for 90% of those of you
who read these posts, but I REALLY need to get a grip on this before I
move on to create the rest of the database for this project.

TIA.

Phil.