relationship help

[Guest]

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.

 

[onedaywhen]

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]

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]

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]

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]

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.

--

 

[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

 

[onedaywhen]

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.

Oh dear, we are in a muddle aren't we <g>? I thought the same of you
i.e. <assume Jeff type voice>: 'I did a subclassed database for a
client once and now I try to apply it to healthcare scenarios.' I can
now see that isn't the case.

I'll be clear: I am familiar with the subclass approach. I am currently
working for a client with an extensively subclassed database i.e. an
Officer is an Individual is an Entity (I didn't name the elements <g>).
It works really well in this data model because the client is in the
business of modelling company structures. While it would be possible to
leverage this approach to, say, the Northwind example, I wouldn't
recommend it because there would be costs (e.g. attributes would be
split between entities which would have to be rebuilt using JOINs) and
no/few benefits.

In summary, I am trying to steer the OP away from the subclassed
approach suggested by Mike Sherrill ('When your doctor gets sick...')
because I think the level of detail isn't required. I'm done on the
subject, apart from asking you to please state whether you recommend
your subclassed approach for the OP of this thread (they'll be no come
back from me).

one size fits all

That's a no from me (although I can see scope for industry standard
schemas, as is happening in the XML world).

Your questions/statements about "what about A...?"

You got it right when you asked in another thread, 'is this your
learning style?' My answer is yes. Do I learn by asking questions?
Yes. Do I learn through discussion? Yes. Do I learn by asserting a
position and seeing if I can defend it? Of course. I can do this
because I retain an open mind. Is it a good learning style?
Questionable <g>.

I was going to ask whether you think you could learn something from me
but this in an unfair question. So I'll end with a warning: don't end
up like other MVPs with a closed mind. Take the following thread as an
example: do a google groups search for the exact phrase, "this should
have a bit of tolerance". I'm still waiting for my thanks for
introducing the guy to CHECK constraints <g> but equally I'm without a
reply to my question about whether the tolerance is required (I was
wondering whether NOW() could fail the CHECK if the clock ticked over
to the next second while processing). Should you wish to pick up on
that thread yourself ...?

Me, I have to go and eat humble pie dished out by an MVP in a thread
where I thought you couldn't INSERT a value to an autonumber column. I
think I prefer it when I'm wrong because I learn something.

Cheers,
Jamie.

--