NYCPHP Meetup

[Mark Armendariz]

David Krings wrote:
>
> cupssbmain: StoryboardID, PageID, LessonID, ModuleID, CourseID
> cupspages: PageID, LessonID, ModuleID, CourseID, Page_Position
> cupslessons: LessonID, ModuleID, CourseID, Lesson_Position
> cupsmodules: ModuleID, CourseID, Module_Position 
Hi David,

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..

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

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.

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


Which would give you a flat version of your data - something like this

SBID     M_Pos     L_Pos     Pag_Pos
1      1      1      1      Story 1      Module 1      Lesson 1      Page 1
1     1     1     2     Story 1     Module 1     Lesson 1     Page 2
1     1     2     3     Story 1     Module 1     Lesson 2     Page 4
1     1     2     3     Story 1     Module 1     Lesson 2     Page 3
1     2     3     5     Story 1     Module 2     Lesson 3     Page 5
1     2     3     6     Story 1     Module 2     Lesson 3     Page 6
1     2     3     7     Story 1     Module 2     Lesson 4     Page 7
1     2     3     8     Story 1     Module 2     Lesson 4     Page 8

Or more specifically - but in a flat version
Story 1
    Module 1
        Lesson 1
            Page 1
            Page 2
        Lesson 2
            Page 3
            Page 4
    Module 2
        Lesson 3
            Page 5
            Page 6
        Lesson 4
            Page 7
            Page 8

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.


Good luck, and have a fantastic weekend!

Mark Armendariz


P.S. As a side note, with a complex sql question like this, it's always 
helpful to offer SQL create and insert statements so people can easily 
and quickly create sample data to help you solve your problem.