ER modelling a relationship using alternative join columns

[cjashwell]

Hello. I have a problem concerning ER modelling a relationship between two tables in a database used as part of a charity fundraising system. I am trying to prepare an ERD based on a third-party supplied system.

The tables I'm concerned with are Constituent (which describes people) and Donation (which describes donations made to charity).

Constituent contains columns like firstname, surname, etc. The PK in Constituent (C) is C.constituentid.

Donation contains columns like amount, donation_date, donation_currency, etc. The PK is D.id and the foreign keys are D.solicitorid and D.constituentid.

Donations are both made by someone and solicited by someone. (It might in some cases be the same person.)

To represent this, Donation can be joined to Constituent by C.constituentid = D.constituentid for payments made by a donor to a fundraiser, and by C.constituentid = D.solicitorid to show who the payment has been made to.

In other words, for a row in Donation there are two columns, D.constituentid and D.solicitorid, and each will contain a constituentid from Constituent to show who the payment is from and who it is to.

My problem is, how do I show this on an ERD? Do I give Constituent an alias (e.g. ConstituentDonor, ConstituentFundraiser) and join 'both' tables to Donation on the relevant column? I'd like some insight if possible into best practice here. Any help greatly appreciated.

Thanks for your time in reading this!

 

[r937]

an ERD has entities (usually shown as boxes) and relationships (usually shown as lines connecting the boxes)

there are two entitites involved -- constituents and donations

there are two relationships between them, i.e. two lines between the entity boxes, labelled differently

is that what you were asking?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[cjashwell]

Ah, so I just label the relationship connectors differently?

I didn't know that. Thanks.

 

[johnherman]

Yes. The same can occur with Bill-To and Ship-To customers or addresses (including postal codes).

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[cjashwell]

Starting to make sense now. ER diagrams are very different to just writing SQL!

Thank you for your quick responses, r937 and johnherman.

 

[MDXer]

A good ER Diagram should help save time in wrtiting your SQL code. Some good ER Tools will even write bits for you.