relationship help

I'm completely confused about relationships between tables. Bassically, I
have 4 types of transactions made by the same group of people. However, not
every person will make every type of transaction. Each type of transaction
is in its own table, and I want to to be able to update a person's info that
exists on any applicable table when I enter/update it on one. Does that make
sense? In the end I just want to be able to print a report that can tell me
what amount people spent in each transaction type. Do I even need to set up
relationships? Thanks!

The notion of relationships is closely tied to the notion of normalization,
the process of separating out the "entities" you are dealing with (and their
attributes - facts about them).

From your description, it sounds like you have "people" as one entity --
their attributes might be firstname, lastname, phonenumber, address, city,
state/province, postalcode.

It also sounds like you have "transaction types" as an entity -- you mention
four types.

So far, it sounds like two tables, with a "natural" relationship of
"many-to-many" (one person could have multiple transaction/types, and a
given transaction type might be relevant to multiple persons). Relational
databases (e.g., Access) handle this many to many relationship by
introducing a "resolver" (or junction) table.

If I'm understanding your situation, you would need a third table to hold
"person-transaction" information. This third table would hold one
row/record for each valid person-transaction combination. The kinds of
facts/attributes that would show up in this third table might be:

tblPersonTransaction
PersonTransactionID
PersonID (this is a "foreign" key from the Person table)
TransactionTypeID (this is a "foreign" key from the TranactionType
table)
TransactionDate
TransactionAmount
SalesPersonID (also a foreign key from the Person table)
...

Note that this design obviates the need for separate tables for each
transaction type. With this design, you can simply query the third table to
find all of a person's transactions, or to find all persons who were part of
a given transaction type.

--
Good luck

Jeff Boyce
Access MVP

"dssrtmom" wrote in message
news
I'm completely confused about relationships between tables. Bassically, I
have 4 types of transactions made by the same group of people. However,
not
every person will make every type of transaction. Each type of
transaction
is in its own table, and I want to to be able to update a person's info
that
exists on any applicable table when I enter/update it on one. Does that
make
sense? In the end I just want to be able to print a report that can tell
me
what amount people spent in each transaction type. Do I even need to set
up
relationships? Thanks!

Jeff Boyce wrote:
If I'm understanding your situation, you would need a third table to
hold
"person-transaction" information.

The OP actually said, 'Each type of transaction is in its own table'.
It sound like they may have the kind of 'subclassing' you were alluding
to in another thread (nurses, administrators, doctors etc).

Anyhow, 'person-transaction' sounds very unlikely to me. What does it
mean to you? Are you envisaging other kinds of relationships parings
between the Transactions table and other non-person entities? It sounds
to me like the Transactions table (or a subclass thereof) is a table
already describes a relationship between a person and other entities
e.g. those involving an online purchase of a book.

Jamie.

--

Jamie

An everyday example of person-transaction is found in the Northwind sample
application. Any "Order processing" application will probably have a
"person-transaction" table, if it is reasonable well-normalized.

Although the OP described current structure (one table for each transaction
type), this may not represent a normalized data structure -- and may be a
carry-over from a spreadsheet design...

Jeff

"onedaywhen" wrote in message
oups.com...

Jeff Boyce wrote:
If I'm understanding your situation, you would need a third table to
hold
"person-transaction" information.

The OP actually said, 'Each type of transaction is in its own table'.
It sound like they may have the kind of 'subclassing' you were alluding
to in another thread (nurses, administrators, doctors etc).

Anyhow, 'person-transaction' sounds very unlikely to me. What does it
mean to you? Are you envisaging other kinds of relationships parings
between the Transactions table and other non-person entities? It sounds
to me like the Transactions table (or a subclass thereof) is a table
already describes a relationship between a person and other entities
e.g. those involving an online purchase of a book.

Jamie.

--

Jeff Boyce wrote:
An everyday example of person-transaction is found in the Northwind
sample
application. Any "Order processing" application will probably have a
"person-transaction" table, if it is reasonable well-normalized.

Sorry, you've lost me. Go slower, please.

I'm looking at Northwind but I can't seem to match your model to
Northwind. Person table is Customers, obviously. Where does
tblPersonTransaction fit? I see Orders and Order Details as being
likely candidates but I'm not sure which one.

Perhaps it's the name that is confusing me. Why tblPersonTransaction
and not simply Transactions?

Although the OP described current structure (one table for each
transaction
type), this may not represent a normalized data structure -- and may
be a
carry-over from a spreadsheet design...

But it could be a 'subclassed' structure, couldn't it? Why not assume
in the OP's favor and go with that?

Many thanks,
Jamie.

--

Jamie

I was reading between the lines of the original post -- if there are
transaction types, there are transactions. It might be sufficient to name
the table tblTransaction (but I don't have enough information, and
table-naming is still somewhat an art, not a science).

The Order table in Northwind is a rough analog to what I was describing...

Sub-classing is an approach that fits situations in which you have a valid
business reason to keep some subset(s) of a larger group isolated from
another subset. Certainly when they have different attributes, or perhaps
when using this as a mechanism for security.

I've found that simple categorizing (this A belongs in category 3) doesn't
require sub-classing.

I can happily suggest how you or an OP can "drive nails with a chain saw",
if the only thing I do is read and answer-as-asked. I believe I do you and
other posters a disservice if I don't read between the lines and offer
other, possibly-related observations.

Or would you rather that I only help folks down that one-way, dead-end
street (I don't know for certain, but some of the "signs" suggest that
someone's headed that way).

Just one person's opinion...

Jeff Boyce
Access MVP

"onedaywhen" wrote in message
oups.com...
Jeff Boyce wrote:
An everyday example of person-transaction is found in the Northwind
sample
application. Any "Order processing" application will probably have a
"person-transaction" table, if it is reasonable well-normalized.

Sorry, you've lost me. Go slower, please.

I'm looking at Northwind but I can't seem to match your model to
Northwind. Person table is Customers, obviously. Where does
tblPersonTransaction fit? I see Orders and Order Details as being
likely candidates but I'm not sure which one.

Perhaps it's the name that is confusing me. Why tblPersonTransaction
and not simply Transactions?

Although the OP described current structure (one table for each
transaction
type), this may not represent a normalized data structure -- and may
be a
carry-over from a spreadsheet design...

But it could be a 'subclassed' structure, couldn't it? Why not assume
in the OP's favor and go with that?

Many thanks,
Jamie.

--

Jeff Boyce wrote:
It might be sufficient to name
the table tblTransaction (but I don't have enough information, and
table-naming is still somewhat an art, not a science).

Try searching the web using the following keywords:

tbl prefix violation ISO-11179 standards

Sub-classing is an approach that fits situations in which you have a
valid
business reason to keep some subset(s) of a larger group isolated
from
another subset.

I just thought it a little contradictory in one thread you supported
subclassing even though the OP made no mention of it, yet in this
thread you seemed to ignore the OP when they at least alluded to
subclassing. Apologies if I got the wrong end of the stick.

Jamie.

--

This may be an artifact of the "one-size-fits-all" approach -- there may be
situations that call for sub-classing, and others that don't. Real world
being modeled, and all...

Jeff

"onedaywhen" wrote in message
oups.com...
Jeff Boyce wrote:
It might be sufficient to name
the table tblTransaction (but I don't have enough information, and
table-naming is still somewhat an art, not a science).

Try searching the web using the following keywords:

tbl prefix violation ISO-11179 standards

Sub-classing is an approach that fits situations in which you have a
valid
business reason to keep some subset(s) of a larger group isolated
from
another subset.

I just thought it a little contradictory in one thread you supported
subclassing even though the OP made no mention of it, yet in this
thread you seemed to ignore the OP when they at least alluded to
subclassing. Apologies if I got the wrong end of the stick.

Jamie.

--

Jamie

Further thoughts (showers usually wash them down the drain...G)...

Your questions/statements about "what about A...?" , (...B?, ...C?, ...D?),
your "one size fits all" comment, and your reference to ISO standards
together suggest the possibility that you are looking for a single model
that fits all real world situations.

I wish you all the best in finding it!

Jeff Boyce
"onedaywhen" wrote in message
oups.com...
Jeff Boyce wrote:
It might be sufficient to name
the table tblTransaction (but I don't have enough information, and
table-naming is still somewhat an art, not a science).

Try searching the web using the following keywords:

tbl prefix violation ISO-11179 standards

Sub-classing is an approach that fits situations in which you have a
valid
business reason to keep some subset(s) of a larger group isolated
from
another subset.

I just thought it a little contradictory in one thread you supported
subclassing even though the OP made no mention of it, yet in this
thread you seemed to ignore the OP when they at least alluded to
subclassing. Apologies if I got the wrong end of the stick.

Jamie.

--

And one more...

Thanks for the discussion/threads. They've been interesting...

Jeff

"onedaywhen" wrote in message
oups.com...
Jeff Boyce wrote:
It might be sufficient to name
the table tblTransaction (but I don't have enough information, and
table-naming is still somewhat an art, not a science).

Try searching the web using the following keywords:

tbl prefix violation ISO-11179 standards

Sub-classing is an approach that fits situations in which you have a
valid
business reason to keep some subset(s) of a larger group isolated
from
another subset.

I just thought it a little contradictory in one thread you supported
subclassing even though the OP made no mention of it, yet in this
thread you seemed to ignore the OP when they at least alluded to
subclassing. Apologies if I got the wrong end of the stick.

Jamie.

--