NYCPHP Meetup

[David Krings]

Mark Armendariz wrote:
> I tried replying on the mysql list the other day but never got a bounce 
> and it never went through.  Here's what I posted..

Thanks for reposting.

> I'm taking a guess here and please forgive me if I'm incorrect in my 
> assumptions, but it looks like you're going to want to look into your 
> normalization.  It seems you  may have too many relationships between 
> all the tables.
> 
> I'll take a hypothetical guess that the data hierarchy might look like 
> this - if not, follow along to see why I'm stating a hierarchy
> Courses
>   Storyboard
>       Modules
>           Lessons
>               Pages
> 

Almost, it is
Courses
     Modules
	Lessons
	     Pages
		Storyboards

> Where
> A Course has a bunch of Storyboards associated with it
> A Storyboard will have a bunch of Modules associated with it.
> A Module will have a bunch of Lessons associated with it
> A Lesson will have a bunch of Pages associated with it
> 
> If that were the case, I would make the tables look something like this:
> 
> cupssbmain: StoryboardID, CourseID
> cupsmodules: ModuleID, StoryboardID, Module_Position
> cupslessons: LessonID, ModuleID, Lesson_Position
> cupspages: PageID, LessonID, Page_Position
> 
> You'll notice that each 'sub table' has only its own id and a 'parent' 
> id.  This chain of relationships would allow you to grab all the lessons 
> for a specific course by id as long as you join the storyboard and the 
> module, or all the pages as long as you join the chain of parent tables 
> and so one.  Basically to get to any sub table, you join the parents on 
> the way down.

That is what I have in my tables. 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.

> something like this (I added Titles to your sample for display purposes):
> 
> SELECT
>   s.StoryBoardID,
>   m.Module_Position,
>   l.Lesson_Position,
>   p.Page_Position,
>   s.StoryTitle,
>   m.ModuleTitle,
>   l.LessonTitle,
>   p.PageTitle
>> FROM cupssbmain s
>   LEFT JOIN cupsmodules m ON s.StoryBoardID = m.StoryBoardID
>   LEFT JOIN cupslessons l ON l.ModuleID     = m.ModuleID
>   LEFT JOIN cupspages p   ON p.LessonID     = l.LessonID
> WHERE
>   s.CourseID = 23
> ORDER BY
>   m.Module_Position ASC,
>   l.Lesson_Position ASC,
>   p.Page_Position ASC

AHA! I see a LEFT JOIN, which probably takes out all those records that 
confuse the query (and myself) otherwise. Given the hierarchy and the 
fields that I need (I do not care about the titles), the query should be 
like this

  SELECT
    s.StoryBoardID,
    m.Module_Position,
    l.Lesson_Position,
    p.Page_Position,
  FROM cupssbmain s
    LEFT JOIN cupslessons l ON l.ModuleID     = m.ModuleID
    LEFT JOIN cupspages p   ON p.LessonID     = l.LessonID
    LEFT JOIN cupssbmain s ON s.PageID 	     = p.PageID
  WHERE
    s.CourseID = 23
  ORDER BY
    m.Module_Position ASC,
    l.Lesson_Position ASC,
    p.Page_Position ASC

Correct?


> Otherwise, I'm afraid I'm not sure I understand what you're trying to 
> accomplish with your current structure, but I predict a lot of confusion 
> into the future without clarifying the order and hierarchy of your data.

And I try to prevent this with putting some more effort into 
normalization and keeping the number of fields in tables small. For 
example, cupssbmain has only the array of IDs and a flag field for 
Delete, nothing else. Name or attached files will go into a separate table.
Right now I try to create an array in PHP that contains all the 
Storyboard IDs in the correct order so that I can provide a navigation 
tool for flipping through the storyboards of a course.


Thanks for all your help,

	David