Hey David,
I just got around to checking the list... would have replied on the
NYPHP-Mysql thread... but it seems like it's back here now.
Are you using MySQL? If not, the syntax could be slightly different
but should be mostly the same.
Data Hierarchy:
Courses
Modules
Lessons
Pages
Storyboards
So you're saying that each course has multiple modules... each module
has multiple lessons... each lesson has multiple pages... and finally
each page has multiple storyboards? Are modules, lessons, pages, or
storyboards shared between courses? Like could a module appear in
multiple courses? Or a lesson in multiple modules or courses? etc...
I'm going to assume that they are not shared... but I can rework this
if you come back to me saying that they are.
cupscourses: CourseID
cupsmodules: ModuleID, CourseID, Module_Position
cupslessons: LessonID, ModuleID, Lesson_Position
cupspages: PageID, LessonID, Page_Position
cupsstoryboards: StoryboardID, PageID, Position
I do carry some IDs in the lower level tables that I know I could
do without, but knowing from previous work with an application I
supported, having the whole set of IDs in the tables may simplify
queries, for example getting all storyboards for a course requires
then to look only at one table rather than five.
Yeah... that's true. If you put indexes on the ids, it shouldn't be
too big a deal to join the tables together. Plus, as you're finding
here, you have to join them all to get the right position anyway. De-
normalizing certain things like that can be useful sometimes if the
joins are complex enough to really slow down your app... but I would
say in this case, all the joins are on primary keys so it's simple
enough to leave it normalized. Your call though :)
Notice that I split cupssbmain into cupscourses and cupsstoryboards.
Maybe you already have a "cupscourses" table... if not, I think you
should create one. That seems to be the main missing piece from your
structure.
OK... so now, if I want to select all the storyboards for a course...
here's the sql:
SELECT
s.StoryboardID
FROM cupscourses c
LEFT JOIN cupsmodules m ON m.CourseID = c.CourseID
LEFT JOIN cupslessons l ON l.ModuleID = m.ModuleID
LEFT JOIN cupspages p ON p.LessonID = l.LessonID
LEFT JOIN cupsstoryboards s ON s.PageID = p.PageID
WHERE c.CourseID = 23
ORDER BY m.Module_Position, l.Lesson_Position, p.Page_Position
_______________________________________________
New York PHP Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php
