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