Unanswered: Database Design has me a little stumped
I am developing a database for a specific online course for a client that has 4 levels: Course, Module, Chapter, and Page. Course, Module and Chapter are there only to allow organization for the 800 - 100 pages in this course. Normally, I would have a table for each level similar to this:
This would be fine except that the client wants to be able to at any level, (module, chapter, page) copy all of the elements below it.
Again this isn't hard codewise to do but the client also wants to be able to edit one of the pages and have it make the changes throughout all of the pages that are the same. So this leads me to something like an instance of a page, chapter, module.
4 Tables with id, name, etc.
4 instance tables that have an instanceID, elementID, parentelement
For example. PageInstance with:
This would allow me to track multiple pages with the different chapter references. However, what about when I want to move a complete module. What happens then? Everytime I write it out and it seems right I am able to think up a scenario that doesn't work well.
Help a frazzled coder that isn't in any way a database expert not make a mistake that will take hours of painful correction down the road.
COURSE (ID_COURSE PK)
MODULE (ID_MODULE PK and ID_COURSE FK)
CHAPTER (ID_CHAPTER PK and ID_MODULE FK)
PAGE (ID_PAGE PK and ID_CHAPTER FK and ID_PAGE_CONTENT FK)
PAGE_CONTENT (ID_PAGE_CONTENT PK)
The PAGE doesnt contain the content, but the table PAGE_CONTENT does. PAGE has a FK to PAGE_CONTENT. So, if you copy a PAGE instance (record), you will only copy the reference to the same PAGE_CONTENT instance (record) and not the actual content. So if a PAGE_CONTENT instance alters, it is altered for all the PAGE's that reference it.
Last edited by Wim; 10-21-08 at 06:24.
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2. Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages