Yannick Mortier wrote: > 2009/2/4 tedd <tedd@xxxxxxxxxxxx>: > >> Hi gang: >> >> I need some fog removed. >> >> I have a problem where I have an unlimited number of tutors teaching an >> unlimited number of courses. When I call upon a tutor, I want to see all the >> courses they teach. >> >> In my old days, I would just set up a linked list of courses and attach it >> to the tutor (another linked list). As a tutor adds courses, I would just >> add the course to the end of the linked list. If the tutor deletes a course, >> then I would remove it from the list by changing a single pointer. If I >> needed a list of all the courses the tutor taught, I would just run down the >> linked list pulling them out as needed. >> >> But now I have to think in terms of records in a database. I'll eventually >> figure it out, but what are your suggestions/solutions? >> >> I understand that I can have one record set up for each tutor, and another >> record set up for each course, and then tie the two together by another >> record like an assignment. That way I can have as many assignments as I want >> tying courses to tutors. >> >> It that the way you guys would do it? >> >> Thanks, >> >> tedd >> -- >> ------- >> http://sperling.com http://ancientstones.com http://earthstones.com >> >> > > Hi, tedd! > Though you might think that this is a stupid task I can recommend you > to draw a little entity-relationship model. It quite helps you to > overlook the structure of the database that you want to design. It has > got different relations between the data and defines a way to > represent those in the database. > > If you are interested in this you can look in wikipedia here: > http://en.wikipedia.org/wiki/Entity-relationship_model > > Since I heard from these models the first time I always use them and I > had no more database changes after I started coding since then. > > Greetings > > > > Hey Tedd, Your idea is correct. The keyword you're looking for is 'atomary data', where you have just 1 occurrence of a particular type of information in your entire database. In your case, this translates to: - A single table for tutors (`tutors`) - A single table for courses (`courses`) - A table that combines these two (`tutors_courses`) Basically, what you're doing here is what you did in 'the old days': you're creating a linked list, but on the database level (and more efficient probably; certainly easier to manage). DB queries will cause you some headaches at first, if you're not used to this. You'll be using JOIN instructions to pull data out of the db. I use InnoDB for this type of stuff (it validates the link). Sample query: SELECT `tutors`.`name` AS `tutor_name`, `courses`.`course` AS `course` FROM `tutors_courses` AS `tc` LEFT JOIN `tutors` AS `t` ON `t`.`id` = `tc`.`tutor` LEFT JOIN `courses` AS `c` ON `c`.`id` = `tc`.`course` WHERE <whatever you're filter is> HTH, Stijn -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
