×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

ER modelling a relationship using alternative join columns

ER modelling a relationship using alternative join columns

ER modelling a relationship using alternative join columns

(OP)
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!

RE: ER modelling a relationship using alternative join columns

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

RE: ER modelling a relationship using alternative join columns

(OP)
Ah, so I just label the relationship connectors differently?

I didn't know that. Thanks.

RE: ER modelling a relationship using alternative join columns

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

RE: ER modelling a relationship using alternative join columns

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

Thank you for your quick responses, r937 and johnherman.

RE: ER modelling a relationship using alternative join columns

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close