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