relationship help

[dssrtmom]

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]

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.

 

[Jeff Boyce]

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

 

[Jeff Boyce]

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>

 

[Jeff Boyce]

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

 

[Jeff Boyce]

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

 

[Jeff Boyce]

And one more...

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

Jeff