Page 1 of 1

Newb question about lists

Posted: Mon Nov 30, 2009 5:20 pm
by MiniMonty
Hi all,

The site I'm building is an online learning resource with 10 courses for members to take and complete
in any order they choose.

I have a table "myMembers" which holds member details like first and last name, email, p'word,
date joined and last log on date.

I need a way of recording which members have signed up to which courses and can't
quite get my head around how to approach it.

Do I make a new table called "courses" with ten fields listing each course and then enter the data
against each course - or - put a new field in the members table to list which courses they've
signed up to ? (preferred but I have no idea how to hold a list in a field in the db)

So I guess my question is about how to hold lists on the db - it's always the way, writing it out helps
to clarify the question I actually need to ask!

Could a field in the members table hold a list such as "1,5,9,6" to indicate that the member had signed
up to those courses in that order?
Or could the table "courses" hold a list of members (who are identified by number) who have signed up
to that course ?
i.e.
course1 = 22,99,108,4,66
course2 = 33,44,6,1,987

Any pointers / links / lessons on this very gratefully received !

Best wishes
Monty

Re: Newb question about lists

Posted: Tue Dec 01, 2009 11:09 am
by AbraCadaver
What you have here is a many-to-many relationship. You have many members that can have many courses and many courses that can have many members. You'll need three tables: members, courses and a join table. Here's an example:

members
member_id
firstname
lastname
etc...

courses_members
member_id
course_id

courses
course_id
coursename
etc...

-Shawn

Re: Newb question about lists

Posted: Tue Dec 01, 2009 4:13 pm
by MiniMonty
That sounds like perfect logic and a good approach.

Care to expand a little or pooint me at a good tutorial ?

Best wishes
Monty

Re: Newb question about lists

Posted: Tue Dec 01, 2009 4:30 pm
by AbraCadaver
MiniMonty wrote:That sounds like perfect logic and a good approach.

Care to expand a little or pooint me at a good tutorial ?

Best wishes
Monty
You join the tables when you do a query, so you can get all courses someone is assigned to or get all people assigned to a course etc...

This is a simple one: http://www.tonymarston.net/php-mysql/many-to-many.html

I use the CakePHP framework for many of these types of things. If you follow the conventions and get the db schema right then its a breeze.

-Shawn

Re: Newb question about lists

Posted: Tue Dec 01, 2009 4:39 pm
by MiniMonty
Looks like some good reading for this evening !
Thanks :D

Monty

Re: Newb question about lists

Posted: Wed Dec 02, 2009 6:46 pm
by MiniMonty
Hmmmm....

well I read it (three times) and I see the concept but can't help feeling a bit out of my depth !
Just installing Cake now so see how that goes.

One question:
Three tables:
1) myMembers
2) courses
3) myMembers_courses

But in which do I record which member has actually signed up for which course ?
Or vice versa ?
If in members I still see the need to record a list like "1,4,6,2,3" (being the course number id)
and if in courses I see the same problem.
Or does the courses table grow and grow to hold a new record every time any member signs
up for any course ?
Am I missing something basic and fundamental ?

I could do with a good old talking to about this so if anyone is in the mood to write a blurb
on helping newbs understand "many-to-many" please speak freely.
If your example were to include a student / tutor relationship where six courses were on offer
to an unlimited number of students you won't find me complaining to the headmaster.

Best wishes
Monty