MySQL database primary key usage

Question

I am designing a MYSQL database for online tutoring website

Let's say, I have:

faculty table with (faculty_id, faculty name)
subject table with (subject_id, subject name)
student table with (student_id, student name)
class table with (class_id, faculty_id, student_id, subject_id)

Now I would like to run SQL queries on the class table to find out all students enrolled with a particular faculty & under a particular subject for which I have:

$sql=("SELECT * 
       FROM class 
       WHERE (faculty_id = '" . mysql_real_escape_string($_POST['faculty_id']) . "') 
       and (subject_id = '" . mysql_real_escape_string($_POST['subject_id']) . "')");

However, I can't seem to figure out how to retrieve student name, faculty name, and subject name instead of just the faculty_id, student_id & subject_id stored in the class table.

Is there some type of SCHEMA and foreign key relations I need to create which automatically link the ID numbers to their respective rows in the respective tables so i can run UPDATE, DELETE, and INSERT queries based on these ID numbers?

Am I missing something important here? I'm not an expert in DB design. Please help.

Even the nickname is ALL CAPS! How about you show us some table structures for subject/faculty? — Jacob
Have you tried adding an INNER JOIN clause to your query against the other tables you want to bring data in from? — cdhowie
I feel a little bad in editing this question to remove the last of the caps. For those of you confused by @Delan's comment, check the original version of this question in the edit history for a giggle. — Conspicuous Compiler
hehe, my bad.. not trying to sound cool, smart, loud or arrogant.. was just trying to emphasize on the last part "im a newb at DB design and need help" :) — ABI

Dan Grossman Dan Grossman · Accepted Answer · 2011-08-11T07:46:20

That's not something that happens in your schema, it's something that you do when you query the database.

To get information from a related table, you JOIN that table.

SELECT
  class.class_id,
  student.name AS `student_name`,
  faculty.name AS `faculty_name`,
  subject.name AS `subject_name`
FROM
  class
INNER JOIN
  student
ON
  student.student_id = class.student_id
INNER JOIN
  faculty
ON
  faculty.faculty_id = class.faculty_id
INNER JOIN
  subject
ON
  subject.subject_id = class.subject_id
WHERE
  class.faculty_id = ?
AND
  class.subject_id = ?

You'll have to replace any column names with what they actually are (your examples had spaces in them, so I'm sure those aren't the real names), and put your PHP code with the post values where the ? are... good time to read up on PDO parameterized queries too.

MySQL database primary key usage

2 Answers