Content Management system DB layout

[Slarti42]

Hi,

I'm currently planning a CMS in PHP with mySQL database.
My question is what sort of database schema would work best. What i curently have is lots of related tables that seem to be nested and are probably not the best way to do it. The structure is:-

Section+
       -Sub-section+
       |           -Page 1+
       |           |      -Heading 1
       |           |      -Paragraph 1+
       |           |      |           -Image 1
       |           |      |           -Image n
       |           |      -Heading n
       |           |      -Paragraph n+
       |           |                  -Image 1
       |           |                  -Image n
       |           |
       |           -Page 2+
       |                  -Heading n
       |                  -Paragraph 1+
       |                  |           -Image 1
       |                  |           -Image n
       |                  -Heading n
       |                  -Paragraph n+
       |                              -Image 1
      etc..                           -Image n

I'm not sure if that gives you an idea of what I mean. Basically i have a table for:

sections - contains uniqueId as PK, section title,

subsections - contains uniqueId as PK, sectionId as FK, subsection title,

paragraphs - contains uniqueId as PK, subsectionId as FK, the actual text and an order feild for setting the order they display on the page.

images - contains uniqueId as PK, a list of all the images on the server with a feild linking the to a specific paragraphId.

I'm not sure if any of this is clear or I've presented it in the corect way but I am quite new to this concept.

Thanks for any help or coments

Slarti

[BlitzCraig]

I would just make a single "Sections" table with the following structure:

SectionID - Primary Key
ParentID - Explained below
etc... - Whatever other fields you need

Now, in a top-level section, the ParentID should be null, since it has no parent section.
In every other section, the ParentID would link to the SectionID of its immediate parent section.

i.e.:
Computers - SectionID = 1, ParentID = NULL
Programming - SectionID = 10, ParentID = 1
Intercal - SectionID = 35, ParentID = 10
SNOBOL - SectionID = 36, ParentID = 10

This should give you an infinite number of sub-levels with a minimal number of database tables and editing pages.


Alternately, you could add a field called LevelNumber to the table, and put a number in there to let you know which level you're on, like so:

Computers - SectionID = 1, ParentID = NULL, LevelNumber = 1
Programming - SectionID = 10, ParentID = 1, LevelNumber = 2
Intercal - SectionID = 35, ParentID = 10, LevelNumber = 3
SNOBOL - SectionID = 36, ParentID = 10, LevelNumber = 3


The latter method is an extra field in the database and may involve more site administration coding, but would probably simplify the amount of code needed on the regular pages, especially for drilling back up the hierarchy.

My two cents' worth, anyway...

[Slarti42]

Thanks BlitzCraig for the sugestion. That will make this for more expandable and simlified than my first thoughts

Cheers

Slarti