SitePoint Sponsor

User Tag List

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

    How to store Website Content in Database??

    I could use some expert advice on how best to store Content from my website in my back-end database (i.e. MySQL).

    When things started off maybe 18 months ago, the only content that I had was traditional "Articles", e.g. some write-up about a news event, person, or some topic.

    The fields in this table include...
    Code:
    - id
    - slug
    - written_on
    - author
    - title
    - body
    - reference_listing
    - created_on
    - updated_on

    Since then, there is a lot of new Content that needs to be added to my website, including things like:
    - How-To Guides
    - Listing of Different Business-Types
    - Key Contacts for Small-Businesses
    - Top 20 Venture Capital (VC) Firms
    - Small-Business Organizations
    - Famous Entrepreneurs
    - Listing of Free Resources
    - Listing of Elected Officials (by State)
    - Listing of Tax-Free States
    - Listing of Right-To-Work States


    I have absolutely NO CLUE of where to store all of this Content, and if it should even go in my Database?! (Furthermore, I haven't even created the actual content for the above list, so I don't know the exact form it will take!!)

    Technically, nearly all Content on my website could be classified as some form of an "article". (Especially if it isn't just a table or list.)

    If this new Content should go into my database, then where??

    Do I want to muck up my true Article table with things like "Famous Entrepreneurs" or "Listing of Business-Types"??

    Do I create a new "catch-all" table called "other_content"??

    Do I create a new table for nearly every new category??

    Or, do I "punt", and just hard-code things as HTML/PHP pages and stick them all in some generic directory called "other"??

    Definitely a much more involved topic than I had ever anticipated!!!

    Sincerely,


    Debbie

  2. #2
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,263
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Since then, there is a lot of new Content that needs to be added to my website, including things like: ... I have absolutely NO CLUE of where to store all of this Content, and if it should even go in my Database?!
    It seems like your application is starting to become a CMS. If that's what you actually want, then perhaps you could peek under the hood of existing CMSs for ideas, or even straight-up use one of them.

    Quote Originally Posted by DoubleDee View Post
    Or, do I "punt", and just hard-code things as HTML/PHP pages and stick them all in some generic directory called "other"??
    Whether or not you want your application to behave like a CMS depends on how you want to maintain your site. If you're comfortable editing code, then there's nothing wrong with coding HTML/PHP pages. Or if you wish to maintain your site through a web WYSIWYG interface, then you can keep working toward this CMS-type solution. (Though, keep in mind that if you're rolling your own CMS, then you'll still be editing code to maintain that CMS.)
    "First make it work. Then make it better."

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    Technically, nearly all Content on my website could be classified as some form of an "article".
    i'd put it into the "body" column of your existing table

    even if it's a list, you'll still be storing html, only with ULs and LIs instead of Ps
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i'd put it into the "body" column of your existing table

    even if it's a list, you'll still be storing html, only with ULs and LIs instead of Ps
    Well, my "body" column is MediumText - which I believe will hold like 15 pages of Text (??) - so there is certainly room.

    But isn't mixing different types of data in one "catch-all" table a bad idea from Database Design and all??

    And wouldn't mixing all of my website's content into one table cause other issues like easily finding things, and query performance as well?

    Because the other data is disparate as far as I can see at this point, I'm not sure how else to group it. But it does seem like at the very least I should keep traditional Articles in my "article" table, and all other content in some other table...


    Debbie

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Jeff Mott View Post
    It seems like your application is starting to become a CMS. If that's what you actually want, then perhaps you could peek under the hood of existing CMSs for ideas, or even straight-up use one of them.
    Care to give me some hints of how the back-end database of most CMSes are organized? (Honestly, I'm not sure I have the time or know how to start looking into CMS solutions or how they are coded...)


    And, yeah, I guess I am rolling my own CMS.


    Whether or not you want your application to behave like a CMS depends on how you want to maintain your site. If you're comfortable editing code, then there's nothing wrong with coding HTML/PHP pages. Or if you wish to maintain your site through a web WYSIWYG interface, then you can keep working toward this CMS-type solution. (Though, keep in mind that if you're rolling your own CMS, then you'll still be editing code to maintain that CMS.)
    I am indifferent. I guess it would be nice to have a web-form you could use to update things.

    What is more attractive to me, is having my data centralized, and having greater control on slicing and dicing and rearranging and analyzing my Content. (Things you can't easily do with files, but can easily do with database records...)


    Debbie

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    But isn't mixing different types of data in one "catch-all" table a bad idea from Database Design and all??
    i understand your concern but the answer is no, not in this case


    Quote Originally Posted by DoubleDee View Post
    And wouldn't mixing all of my website's content into one table cause other issues like easily finding things, and query performance as well?
    not at all


    Quote Originally Posted by DoubleDee View Post
    But it does seem like at the very least I should keep traditional Articles in my "article" table, and all other content in some other table...
    okay, then, if that's how you feel, go ahead and design some additional tables...

    ... but i sure wouldn't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i understand your concern but the answer is no, not in this case
    So, are there any other "gotchas" I should be aware of going down this path of "All Content in One Table"?


    Is that how CMS do it?


    Short of showing you my ERD - which I know you don't read anyways - are there any tips on how to architect things so my design last a long time?

    (What I had worked great until I realized that I will have a fair amount of Content that is not necessarily an "article".)

    Thanks,


    Debbie

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    (What I had worked great until I realized that I will have a fair amount of Content that is not necessarily an "article".)
    perhaps the "gotcha" here was in thinking that your content was only articles
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,764
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    perhaps the "gotcha" here was in thinking that your content was only articles
    We live and we learn...


    Debbie


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
  •