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.
You need a join table to create the many-to-many relstionship.
Thanks for all of the help
Ewan's Dad is correct:
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.
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.
I don't understand this.
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.
Fog Creek Home