2

I'm building a web application concerns a student and an adviser. The student will select his independent study and an adviser will supervise it. I am struggling to find the correct relationship for my program.

Basically the actors in my application are.

a Student can select 1 independent study.
a Student can only have 1 Adviser.
an Adviser can supervise multiple Students.
a coordinator can be an adviser also

My attempt to make ERD for my application.

Student - > Adviser (1 to many)

Coordinator - > Adviser (1 to 1)

But the problem is that I need a table that indicates that advisor A supervises student X. How should I do this?

BenMorel
  • 34,448
  • 50
  • 182
  • 322
SupaOden
  • 722
  • 4
  • 13
  • 41
  • 1
    Coordinators can't supervise more than one Adviser? Are they all Advisers but are denoted as a Coordinator if they supervise another adviser? – Sonny Dec 14 '11 at 16:16
  • Is there relationship between Coordinators and Students? Between Coordinators and Studies? – ypercubeᵀᴹ Dec 14 '11 at 16:51
  • @ypercube A coordinator is basically the one who will approve the selected study the student has proposed. After that its the job of the adviser to supervise. The coordinator can be an adviser also. – SupaOden Dec 14 '11 at 17:08
  • Your text at the beginning says that there's a relationship between Students and Studies and another relationship between advisers and studies. Right after that you state: "a Student can only have 1 Adviser" and "an Adviser can supervise multiple students". So what do you really have? Relationship between Advisor and Study or relationship between Advisor and Student? – Fabian Barney Dec 14 '11 at 17:15
  • There is a relationship between adviser/student/coordinator – SupaOden Dec 14 '11 at 17:25
  • 1
    Ok, then the "it" at the end of your 2nd sentence is a bit misleading. What about students having multiple studies? I mean, is it a real world application? Then you should consider it and then it get more clear why I would prefer a relationship between Advisor and Study. There will be one Advisor per study but when a student has multiple studies he has one advisor for each study and not only one overall. – Fabian Barney Dec 14 '11 at 17:35
  • no a student can only have 1 study, that is the requirement – SupaOden Dec 14 '11 at 17:48

2 Answers2

3

Here's another ERD based on ypercube's comments on my other answer:

enter image description here

In this model, if there's an Adviser record with the Coordinator's id as it's id, that Coordinator is an Adviser.

Community
  • 1
  • 1
Sonny
  • 8,204
  • 7
  • 63
  • 134
  • This is better but not very good way to make a `1:1` relationship. You only need `coordinator(id, name)` and `adviser(id)`. With `adviser(id)` being PK and also FK to `coordinator(id)`. – ypercubeᵀᴹ Dec 14 '11 at 17:32
  • 1
    @ypercube - Ahh! I was misunderstanding the OP in that all Advisers are Coordinators. – Sonny Dec 14 '11 at 17:34
2

Here's an ERD:

enter image description here

My understanding is that a Coordinator is simply an Adviser which supervises another Adviser. If an Adviser's parent_id is set, that "parent" is the Adviser's Coordinator. My Diagram allows for a Coordinator to supervise more than one Adviser because that what makes sense to me, but that relationship could be changed to One-to-One (1:1).

I use MySQL Workbench.

Sonny
  • 8,204
  • 7
  • 63
  • 134
  • It's the Coordinator -> Adviser relationship. It seems that a Coordinator is simply an Adviser that supervises another Adviser. – Sonny Dec 14 '11 at 16:44
  • Can you please elaborate one the arrow from the adviser table that points to itself. – SupaOden Dec 14 '11 at 16:45
  • 1
    OP says: *"a coordinator **can be** an adviser also"*. There is a difference between **can be** and **can supervise** – ypercubeᵀᴹ Dec 14 '11 at 16:55
  • @ypercube - Yes, there could be an `is_coordinator` flag, but it seems to me that assigning a parent is equally as effective, making an `is_coordinator` flag redundant. Also, just because a person is an Adviser, doesn't mean they are advising any students. – Sonny Dec 14 '11 at 16:58
  • 1
    An `is_a` relationship can be done with either a flag or (better in my opinion) with 2 tables (supertype/subtype). The other tables (student, study) and relationships you have seem to be what the OP describes. – ypercubeᵀᴹ Dec 14 '11 at 17:05
  • @ypercube - Good point. I have added another answer with a model based on your suggestions. – Sonny Dec 14 '11 at 17:25