SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    How to add Sub-Sections to my Data Model?

    As stated in an earlier thread, I want to add the concept of "Sub-Sections" to my website.

    Currently, I have articles just grouped by Section (e.g. Finance) which doesn't give me a lot of control with the UI or back-end. For example, I might have the diverse articles all lumped together under "Finance"...
    local.debbie/finance/sequester-could-lead-to-more-start-ups
    local.debbie/finance/asian-stocks-rise-most-in-7-months
    local.debbie/finance/new-tax-laws-will-impact-small-businesses

    ...when they would be better groups like this....
    local.debbie/finance/economy/sequester-could-lead-to-more-start-ups
    local.debbie/finance/markets/asian-stocks-rise-most-in-7-months
    local.debbie/finance/taxes/new-tax-laws-will-impact-small-businesses


    So here is the data model that I have come up with...

    One SECTION has one or more ARTICLEs
    One ARTICLE has one or more SECTIONs

    One SECTION has one or more SUB-SECTIONs

    One SUB-SECTION has one or more ARTICLEs
    One ARTICLE has one or more SUB-SECTIONs


    And using Crow's Foot Notation...

    (1) SECTION -||-----|<- SECTION_ARTICLE ->|------||- ARTICLE

    (2) SECTION -||-----o|- SUB-SECTION

    (3) SUB-SECTION -||-------|<- SUB-SECTION_ARTICLE ->|----------||- ARTICLE


    Questions:
    1.) To do things like validating URLs and storing data, do I need relationship #1?

    (It seems like Relationship #1 and #3 are almost redundant?!)


    2.) Do I just care about the end relationship between an Article and it's Sub-Section, or do I need to care about the relationship to the parent Section?


    3.) Anything I need to take care of or should be concerned about?


    I just don't feel very confident in what I am doing here...

    Sincerely,


    Debbie

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    1) only if you have articles that don't belong to a subsection
    2) see 1)
    3) retrofitting existing articles
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    r937,

    So it sounds like you approve of my Data Model and description above??


    Debbie

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    So it sounds like you approve of my Data Model and description above??
    i guess so

    i'd use only one table for sections and subsections, though
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i guess so

    i'd use only one table for sections and subsections, though
    Why??

    Right now I have this...

    Code:
    SECTION -||-----|<- SUB-SECTION

    Debbie

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Why??
    because i like it, and i think it's more flexible

    see Categories and Subcategories
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,276
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    Thought I'd chime in to say that I agree with r937. Using just one table is more normalized and more flexible.

    All you really need to do is add a field named something like parent_section_id to your table. Top-level sections will have a NULL parent section, and any sub-sections will have the parent section ID set appropriatly. This also makes it incredibly easy to create sub-sub-sections and sub-sub-sub-sections if ever you wanted to.
    "First make it work. Then make it better."

  8. #8
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,777
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Jeff Mott View Post
    Thought I'd chime in to say that I agree with r937. Using just one table is more normalized and more flexible.

    All you really need to do is add a field named something like parent_section_id to your table. Top-level sections will have a NULL parent section, and any sub-sections will have the parent section ID set appropriatly. This also makes it incredibly easy to create sub-sub-sections and sub-sub-sub-sections if ever you wanted to.
    I dunno. I started reading the link, and it sounds complicated. (Sorry, my brain is already overflowing trying to get this dumb website done?!)

    You make some interesting points about scalability, though.

    I'll look at it again after supper, and see if it sinks in more.


    Debbie

    P.S. I followed what r937 was doing with the first example, but I'm in a fog how to apply that to my website. (Remember, I am adding the concept of "Sub-Sections" to my website, which means I have to figure out how to re-tool my mod_rewrites and PHP and Queries...


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
  •