0

A student proposes a new topic. It is up to the adviser to accept or reject the topic. If he accepts the topic, the adviser will supervise that student with that topic.

Now this is where it gets me.

A meeting is scheduled between the student and the adviser that will be for discussion pertaining to that topic.

If I make a meeting_table. How should I refer the meeting? With regard to the topic that was selected? If this was the case then topic_id will be the foreign key.

Or should I refer it by using advisor_id and student_id as the foreign keys?

Which would be an easier approach? as I'll be making a web application after completing the database design.

SupaOden
  • 722
  • 4
  • 13
  • 41
  • Can a student and advisor be related through more than one topic? Can the same topic be the basis for more than one student/advisor relationship? Is a meeting always about exactly one topic? – Ted Hopp Dec 14 '11 at 19:11
  • @Ted Hopp the topic will be unique to each student. Similar topics will be rejected. A student can only have 1 adviser. An adviser can supervise multiple students (providing they each have unique topics) – SupaOden Dec 14 '11 at 19:13
  • If none of the answers solve your problem, maybe you should clarify what the problem is. – Sonny Dec 21 '11 at 14:09

5 Answers5

1

Base on my answer to your previous question, I would expand on that ERD like this:

enter image description here

The Student would need to have a Topic and an Adviser before scheduling a meeting. Both the Topic and the Adviser can be found by doing a joining Meeting -> Student -> [Adviser|Topic].

Community
  • 1
  • 1
Sonny
  • 8,204
  • 7
  • 63
  • 134
0

If the meeting relates to a topic, a student, and an advisor, shouldn't it reference all three? Or for a more generic meeting schema, I'd use something like:

ATTENDEES
id
name
role              ; i.e. 'student', 'advisor', etc

ROOM
id
description

TOPIC
id
description

MEETING
id
topic_id            ; FK to TOPIC.id
room_id             ; FK to ROOM.id
description

MEETING_ATTENDANCE
attendee_id         ; FK to ATTENDEE.id
meeting_id          ; FK TO MEETING.id

This schema will allow an arbitrary number of attendees (so you could have one advisor and three students, etc). And for bonus points, it throws in a meeting room id as well.

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
  • Referencing all three of topic, student, and advisor represents redundant information, meaning a non-normalized schema. – Ted Hopp Dec 14 '11 at 19:16
  • i think role is a good thing in this model, but it belongs in the meeting_attendance table not the attendee table. One person conceivably could fill different roles in different meetings. – Randy Dec 14 '11 at 19:19
  • @Randy: Sure, that would be a reasonable adjustment, depending on your needs. Roles could be an attribute of a person (i.e. student, faculty, etc) or of the person relative to the meeting (i.e. presenter, attendee, vendor, etc); so depending on your case, you may need to move the 'role' (or have multiples) – Jonathan Hall Dec 14 '11 at 20:05
  • You need to add a table in your schema to represent the relationship between student, advisor, and topic. From OP's description, this relationship needs to be recorded whether or not there are any meetings scheduled. – Ted Hopp Dec 14 '11 at 20:15
  • @TedHopp: Sure, there will need to be many more supporting tables for a fully-functional DB. But since the question was specifically about the meeting relationships, I only addressed that here. – Jonathan Hall Dec 14 '11 at 20:30
0

i would suggest a meeting with meeting_id then another table meeting_topic with meeting_id and topic_id - this will allow multiple topics as well depending on your uniqueness specification. Also, since many people may attend the same meeting something like meeting_attendee with at least two rows one for the student and one for the advisor. something like this:

meeting
----------
meeting_id
meeting_date
location_id (?)

topic
----------
topic_id
description

meeting_topic
-----------
meeting_id
topic_id

meeting_attendee
-----------
meeting_id
attendee_id
Randy
  • 16,480
  • 1
  • 37
  • 55
  • There should be fields in the `topic` table relating the topic to the student and advisor. This relationship exists independently of any meetings being scheduled. If that were in place, the `meeting_attendee` table would then be redundant. – Ted Hopp Dec 14 '11 at 19:23
  • as stated in the color text, this is a bit broader in that it allows multiple people in one meeting, as well as multiple topics for that meeting. – Randy Dec 14 '11 at 19:43
0

My table design:

Student (id, name)

Advisor(id, name)

Topic(id, name, created_by(foreign key id of student), advisor(foreign key id of advisor))

Meeting(id, name, schedule, topic (foreign key id of topic))

This is the simplest database design considering one student and one advisor in meeting.

mprabhat
  • 20,107
  • 7
  • 46
  • 63
  • This is not normalized. In table Meeting, the `student` and `advisor` values are functions of `topic` (through the Topic table). – Ted Hopp Dec 14 '11 at 19:20
0

With the info given, it seems to me that referencing the meeting to the topic works nicely. However, whether that's the easiest depends on what queries you expect (including their relative frequency).

Ted Hopp
  • 232,168
  • 48
  • 399
  • 521