Many to Many Table Design Help

[Evan]

Please I could use some help with desgin.

Lets say seller1 and seller2 want me to sell their product for them. I find
a broker representing buyer1 and buyer2 who purchase the product. I need to
record the transaction, the product, the sellers, buyers, and their
respective brokers.

Since the buyers, sellers, and brokers are all contacts I thought I could
break the Transaction down to two sides (buying side and selleing side)
using SideID from tableSides and combine each with the unique TransactionID
from tableTransactions in a junction table to create a unique occurrence of
TransactionID + SideID. From there, I could associate Contacts which each
side and break them down to ContactType (principle and agent). The problem I
run into is associating multiple contacts with that single occurrence of
TransactionID and SaleID.

Is it possible to create a second many-to-many relationship with a junction
table? I could easily just add ContactID to the junction table if there was
only one contact, but their can be multiple buyers, sellers, brokers on each
side. Is there another approach to the structure that might work intead?

Thanks in advance.

 

[TC]

The following structure would let you record any # of people against
each transaction, and also store the role of each person (buyer,
seller, broker, whatever) for that transaction. (So someone could be a
buyer in transaction #1, but a seller in transaction #2, and so on.)

tblPerson
PersonID
name, adrs, phone #s etc.

tblTransaction
TransactionID
data pertaining to the transaction as a whole
eg. date created

tblTransactionPerson
TransactionID } composite
PersonID } primary key
role (buyer, seller, broker, etc.)

A shortcoming with that structure is that it would not show you the
relationship between buyers, sellers, & brokers /within/ a transaction.
You would know, for example, that there were 3 buyers, and 3 brokers,
but you would not know which broker worked for which buyer. Does that
matter?

HTH,
TC [MVP Access]

 

[Evan]

Hi, Thanks for replying.

Two (that come to mind) points that have held me up with this setup:

1) A single person cannot obviously be buyer and seller in one transaction,
but they can be broker and buyer or broker and seller. This may sound
conuter-intuitive, but its a commission thing.
2) More importantly, YES, I do need to track which "side" of the transaction
a broker is on. Hence the ides of splitting the sides.

Any thoughts?

Thanks

 

[TC]

Ok, I did not read your question clearly enough :-(

Let me ask this: In a given transaction, say transaction #1: does each
person have a role /regardless of the other people/, or does each
person only have a role /in reference to some other person/?

For example, if Tom, Dick & Harry are all on transaction #1: does Tom
have a role /regardless of Dick & Harry/, or can you only speak of
Tom's role /in regard to Dick/, and his role /in regard to Harry/?

Or could Tom have a role for himslf, eg. BUYER, /and also/, a role in
regard to Dick, and another in regard to Harry?

If you answer that, we can get the structure right, toot sweet!

TC [MVP Access]

 

[Pat Hartman\(MVP\)]

To keep track of which side a broker is on, use two codes for broker instead
of one - Buyer's Broker and Seller's Broker.
I don't think you can use declarative RI to enforce rule #1. I think you'll
need to do it with code. Since an individual may take two roles in the
transaction a unique index can't be defined.

Hi, Thanks for replying.

Two (that come to mind) points that have held me up with this setup:

1) A single person cannot obviously be buyer and seller in one
transaction, but they can be broker and buyer or broker and seller. This
may sound conuter-intuitive, but its a commission thing.
2) More importantly, YES, I do need to track which "side" of the
transaction a broker is on. Hence the ides of splitting the sides.

Any thoughts?

Thanks

The following structure would let you record any # of people against
each transaction, and also store the role of each person (buyer,
seller, broker, whatever) for that transaction. (So someone could be a
buyer in transaction #1, but a seller in transaction #2, and so on.)

tblPerson
PersonID
name, adrs, phone #s etc.

tblTransaction
TransactionID
data pertaining to the transaction as a whole
eg. date created

tblTransactionPerson
TransactionID } composite
PersonID } primary key
role (buyer, seller, broker, etc.)

A shortcoming with that structure is that it would not show you the
relationship between buyers, sellers, & brokers /within/ a transaction.
You would know, for example, that there were 3 buyers, and 3 brokers,
but you would not know which broker worked for which buyer. Does that
matter?

HTH,
TC [MVP Access]

 

[Evan]

Hmm, trying to grasp the question! The broker(s) on a given "side"
represents the whole side so to speak. While there may be multiple agents on
each side and multiple buyers/sellers on each side, they would in almost all
cases represent the whole "side". Let me give some examples:

Trans #1: John represents Jim in purchasing from Betty who is represented by
Sally.
Trans#2: John represents himself in selling to Jim and Betty who are
represented by Bill and Bob.
Trans#3: Bill and John represent both Jim and Betty in selling to Sally who
Bill and John also represent.

Hopefully that demonstrates the idea. If it helps, the main purpose is to
track at the transaction level, but there is a need to query such as:

1) All sales where John represents the buyer
2) All sales where Bill was the buyer
3) All sales involving Bill as either the buyer or seller, etc.

Okay, I'll leave it at that for now and see what else you need to now.
Thanks so much for taking the time to help.

 

[TC]

Ok, those examples make it quite clear.

Let me cogitate a while on this!

TC [MVP Access]

 

[Evan]

Well, this is way over my head, but how about this:

Tables - tTransactions, tContacts, tContactTypes, tSideTypes,

One junction table with a composite primary key made up of the foreign key
from each of the tables. The combination of
Transaction/Side/Contact/ContactType would be a unique occurrence.

I have never dealt with more than a two-field primary key in a junction
table. Any thoughts?