SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast Slarti42's Avatar
    Join Date
    May 2001
    Location
    UK
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Content Management system DB layout

    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:-
    Code:
    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

  2. #2
    SitePoint Zealot BlitzCraig's Avatar
    Join Date
    Mar 2001
    Location
    Louisville, KY
    Posts
    114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...
    Craig H. Rettig - Bitbucket Heaven
    To understand recursion, you must first understand recursion.

  3. #3
    SitePoint Enthusiast Slarti42's Avatar
    Join Date
    May 2001
    Location
    UK
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Thanks for that

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

    Cheers

    Slarti


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •