Page 1 of 1

Need some help (with examples and hand holding)

Posted: Mon Dec 14, 2009 6:21 pm
by MiniMonty
Hi all

this is a many-to-many question.

I'm pretty much an SQL newb and after a lot of reading I get the logic and the idea of the many-to-many
relationship on a db but I'm having trouble actually implementing it in my project so I'm
looking for some practical help in setting it up to a point where I can do some experiments and
learn more.

Scope:
it's a system that matches courses, students and tutors.
There are six courses that an unlimited number of students can take.
Each course is taught by a different tutor.

So far I have a table "myMembers" which represents the students.
But all my attempts at setting up other tables and having them "talk" to each other via a "join" table
to run queries like
"which courses has student number 123 signed up to" ?
or
"how many students have signed up to course number 3" ?
have failed.

So where do I go from here ?
I think I need some experienced (and probably patient) people to help me along to the next step.

All and any advice, help, hand holding and leading by the nose like an idiot much appreciated !

Best wishes
Monty

tech stuff - I'm developing live on a LAMP set up using php myadmin to sort the back end.

Re: Need some help (with examples and hand holding)

Posted: Mon Dec 14, 2009 10:54 pm
by califdon
OK, this is the classic example used to teach many-to-many relationships. Sounds like you're beginning on the right track. You have 3 basic entities, students, tutors, and classes. Each of these requires a table. For each possible relationship, you need an additional table. So a student may take many classes and each class may have many students--that requires a table. If a tutor might teach many classes or a class might have more than one tutor, that would require another table. If it's always just one tutor to a class, and no tutors will teach more than one class, it's just a one-to-many relationship and doesn't need an additional table. So you need something like this, with the assumption that tutors-to-classes is a many-to-many relationship:

Code: Select all

+----------+     +---------+     +----------+     +---------+     +----------+
| Students |     | Stu_Cls |     | Classes  |     | Cls_Tut |     | Tutors   |
+----------+     +---------+     +----------+     +---------+     +----------+
| Sid (PK) |<----| Sid     |  +->| Cid (PK) |<----| Cid     |  +->| Tid (PK) |
| Sname    |     | Cid     |--+  | Cnum     |     | Tid     |--+  | Tname    |
| ...      |     | Date    |     | Cdescrip |     | Date    |     | ...      |
|          |     | Grade   |     +----------+     +---------+     +----------+
+----------+     +---------+
So, every time a new class is set up, you link one or more tutors to the class, and every time a student enrolls in a class, you link the student to the class. How do you do that? With your PHP logic, storing the primary key IDs in the appropriate linking table, along with other possible info, such as the date of the class or the grade the student received. There's a lot to keep track of, but that's how it's done.

Re: Need some help (with examples and hand holding)

Posted: Tue Dec 15, 2009 6:44 pm
by MiniMonty
OK - stage 1 complete - I set up the tables !
Now the questions... (I did mention the hand holding part right) ?

I made a quick scrip to insert a course and a tutor like this:

Code: Select all

 
$course_id = "DayOneDigital";
$course_num = "1";
$course_descrip = "The First Step";
$tutor_id = "1";
$tutor_name = "Jon Mortimer";
$tutor_email = "jon@shutterbugclub.com";
 
 
 $sql = mysql_query("INSERT INTO courses (course_id, course_num, course_descrip) 
     VALUES('$course_id','$course_num','$course_descrip')")  
     or die (mysql_error());
 
 $sql = mysql_query("INSERT INTO tutors (tutor_id, tutor_name, tutor_email) 
     VALUES('$tutor_id','$tutor_name','$tutor_email')")  
     or die (mysql_error());
 
So my courses table now has one entry and my tutors table now has one entry.
What do I need to add to link that tutor to that course ?
(which I guess will teach me how to link student X to course Y etc.,)

Best wishes
Monty

Re: Need some help (with examples and hand holding)

Posted: Tue Dec 15, 2009 7:06 pm
by califdon
First of all, you need to show us what your tables contain. What fields, and which ones are key fields. Everything starts from there. There's little purpose in showing code if we don't know how the data is organized. A common problem beginners have is concentrating on code before they have a firm grasp of the data.

So is your tutors-to-classes relationship one-to-many or many-to-many?

Then, try to separate in your mind the adding of new instances of the entities, and the creation of relational links, which may be in the same tables (one-to-many) or separate tables (many-to-many). In some operational situations, you might be creating some relational links at the same time you're adding new instances, but probably in most situations you will have created the records in the entity tables, and later wish to link a particular tutor, e.g., to a particular class. The basic approach is to let the user select which tutor (or student) and which class are related. You might do this with code that asks the user to choose from a list, or perhaps enter a search term to determine which records are to be linked. Once you have made the selections, you will have the Primary Key values for each of the entities, so you just create a record in the many-to-many relational table with those values, and perhaps other data, such as the date of enrollment or whatever.

If it's a one-to-many relationship, it's a different scenario. Again, you have to give the user a way to select which records are involved, but instead of creating a new record in a relational table, you will be updating a foreign key value in one of the entity tables.

So you can see how critical it is to know what kind of relationships are involved and how your data is organized.

Re: Need some help (with examples and hand holding)

Posted: Sun Dec 20, 2009 2:17 am
by josh

Code: Select all

 
<?
 
mysql_query("INSERT INTO courses ( course_num, course_descrip)
VALUES('$course_num','$course_descrip')")  
or die (mysql_error());
 
$course_id = mysql_insert_id();
 
mysql_query("INSERT INTO tutors ( tutor_name, tutor_email)
VALUES('$tutor_name','$tutor_email')")  
or die (mysql_error());
 
$tutor_id = mysql_insert_id();
 
mysql_query("INSERT INTO course_tutors_association (tutor_id, course_id)
VALUES('$tutor_id','$course_id')");
Changes

- Removed the $sql variables, not needed
- Removed specifying the IDs, let the database automatically generate them
- Inserted code to get the automatically assigned IDs
- Added a third query to create the "association"


Now when you need to view these "relationships" you can use an SQL JOIN, there are INNER JOINs, OUTER JOINs, and LEFT JOINs. Learn what a LEFT JOIN is first and use that.