Database Relationship Problem

I am sure this will probably be a no-brainer for most of you but I am having some trouble with some relationships in a database I am designing in MySQL.  I have 2 elements (charges and transactions) that I need to associate.  The issue is a charge may be associated with multiple transactions and a transaction may be associated with multiple charges.  What would be the most efficient way to represent this?  The solutions I have come up with so far have seemed kludgy at best.

Jon Lindbo
Tuesday, August 24, 2004

http://www.google.com/search?hl=en&lr=&ie=UTF-8&q=many-to-many+database+-access

--
ee

eclectic_echidna
Tuesday, August 24, 2004

You need a join table to create the many-to-many relstionship.

charge-<charge-transaction>-transaction

The charge-transaction table should contain foreign keys from the other two tables.

Ewan's Dad
Tuesday, August 24, 2004

Thanks for all of the help

Jon Lindbo
Tuesday, August 24, 2004

Ewan's Dad is correct:

>> "The charge-transaction table should contain foreign keys from the other two tables."

But to be more specific:

The charge-transaction table should contain foreign keys from the other two tables AS THE CONCATENATED PRIMARY KEY OF THE JOIN TABLE.

anon
Tuesday, August 24, 2004

The solution to implementing a many-to-many relationship in relational databases is very standard and is a bit kludgy. C'est la vie. That stuff you saw on Google is where it's at, I'm afraid.

Something I always thought was neat was how to implement a table that has a many-to-many relationship with itself. For example, a table containing "help" topics can have related topics. Any topic can be related to any number of other topics. So...

Help Table  <---many:many--->  Help Table

becomes

Help Table <-- one:many ---> Related Help <-- many:one --> Help Table

giving

Help Table (HelpID*, Text)
HelpRelatedTopic (HelpId*, RelatedHelpID*)

where * marks the primary key.

Herr Herr
Wednesday, August 25, 2004

It's not really cludgy when you think about it in terms of relations. A 'join table' coincides very well with the mathematical notion of an arbitrary binary relation on (the sets of keys of) your two tables. Which is exactly what you're trying to model.

If the relation is also guaranteed to be a function (or partial function) then it's more efficient to model it using a foreign key in the domain table... (a 'one-to-many'/'many-to-one' relation) but if it could be 'Many to Many' you need the join table approach.

Matt
Wednesday, August 25, 2004

I don't understand this.

Charge is supposed to be an amont type (i.e. $15.99)

Transaction is supposed to be an id (number) or a title (string).

It seems they both belong to the same table: Transaction.

What am I missing?

Raju Patel
Wednesday, August 25, 2004

For the data I am modeling a charge represents a charge for service that is associated with a particular account.  It contains information related to that charge like the charge type and terms.  A transaction has information about that actual exchange of monetary funds that pay for one, many or part of a charge.  I guess a more common ware to think of what I am calling a charge would be a debit but I am not keeping a running balance.  I only track active charges and keep a history of transactions that paid those charges. 

Jon Lindbo
Wednesday, August 25, 2004

  Recent Topics

  Fog Creek Home