NYCPHP Meetup

[Rob Marscher]

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