SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot RyanKing1809's Avatar
    Join Date
    Oct 2011
    Location
    Melbourne, Australia
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Breaking Down Page Structure into Database Tables - How many tables is too many?

    I'm a bit new to database design and wondering what I should store in tables and what should stored with a markup-language in a text field that could then later be processed by the server. My tendency is to make a table for essentially everything but I've noticed many applications like say mediaWiki use markup to store their data in a large text field. Does this save time by limiting the amount of Database Queries and the size of Tables? Or does the server side processing take up more time?

    My page structure is as follows:

    Code:
    Page contains:
    id
    page_image_id
    user_id
    title
    path
    deleted
    
    Page has many Sections

    Code:
    Section contains:
    id
    page_id
    position
    
    Section has many Paragraphs
    Section has many Images
    Section has many Page_Links

    Code:
    Paragraph contains:
    id
    section_id
    body_text
    position
    Code:
    Image contains:
    id
    section_id
    user_id
    image_path
    description
    position
    Code:
    Page_link contains:
    id
    page_id
    section_id
    description
    position


    I like to design the database in mind of the possibility of reaching 100 000+ pages. Noting that, the last 3 tables could get very large very quickly. Would it be more efficient to store all that information in markup in the section table then processing the information using server-side code? Or am I underestimating the speed of database queries and this is more efficient as the data there ready to be manipulated? At which point should I stop making tables and use markup instead?

  2. #2
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    So I guess this is like a CMS you're building?

    For our site, we store the page content in individual pages, and just use the database for page titles, contact info, and other details about the page, then use includes for the header and footer. Which header and footer is used (along with which stylesheet) is determined by the directory structure.

    But I have a unique situation in that I didn't have a decent production database available (my office used Access up into this year) so I felt I had to limit what the database did. If I'd had a decent database when we started, I might have set things up differently.
    <cfset myblog = "http://cydewaze.org/">

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,192
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    I don't really understand why you would need a separate table to break-up paragraphs. In most cases that information is just stored in a text column. What is the benefit of breaking up each paragraph of a page into a different table row? For me there is none but you might have some weird idea in mind…

    Besides content of pages within a content management system are rarely only defined by paragraphs. In most cases there will probably be some type of editor that allows some level of control over style, layout, etc without fully exposing HTML vulnerable to XSS. The content of a page will most likely and practically be HTML, bbcode or something in between – not just raw text, as that is quite limiting.

    Most content management systems these days use some type of editor for content entry. A good example is aloha editor which seems to be all the rage these days. If you were to use an editor like that is would not be very practical to define content within the context of paragraphs.
    The only code I hate more than my own is everyone else's.

  4. #4
    SitePoint Zealot RyanKing1809's Avatar
    Join Date
    Oct 2011
    Location
    Melbourne, Australia
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    I don't really understand why you would need a separate table to break-up paragraphs. In most cases that information is just stored in a text column. What is the benefit of breaking up each paragraph of a page into a different table row? For me there is none but you might have some weird idea in mind…
    Yeah you're right I should just have it stored as a text column. Coming from the perspective of the page object in the application it makes sense to separate the paragraphs, images and links for computing but after reading a bit on database design I need to think of a column as what is most atomic from the data retrieval perspective - meaning that since I never retrieve and store the paragraphs on a separate occasion the rest of the section there's no need to store it in separate table.

    Thanks for your help!


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
  •