SitePoint Sponsor

User Tag List

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

    Do I need Multiple Tables?

    I am creating a home-grown Content Management System (CMS), and could use some advice on how to separate my data.

    When this started, I just had "Articles", but as things have grown, I will also have other content like: Interviews, Case Studies, Current Events, Famous Cases, etc.

    The way I am building my pages, is to have attributes in my "Articles" table like this:
    - id
    - pretty_title
    - meta_title
    - meta_description
    - meta_keywords
    - page_title
    - page_subtitle
    - written_on
    - author
    - body
    - endnote_listing
    - created_on
    - updated_on


    (**NOTE: In the "body" field I just copy and paste my raw HTML code in there, so all of the content and markup are there.)

    Because of how my table is structured, it seems to me that I could not only have "Articles" in this one table, but also "Interviews", "Case Studies", "Current Events", "Famous Cases", "Op-Ed", etc.

    So, should I have a separate table for each type of content OR should I add a "Category" field and dump all of the content into just one table?!


    Sincerely,


    Debbie

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,492
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    If an article can have only one category, add a category field.
    If an article can have multiple categories (many to many relationship), you'll need an articleCategories table.

  3. #3
    Non-Member
    Join Date
    Apr 2011
    Location
    no fixed address
    Posts
    851
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DoubleDee View Post


    So, should I have a separate table for each type of content OR should I add a "Category" field and dump all of the content into just one table?!

    This is related to Database Normalisation.

    If you decide to add categories to your articles I would suggest having a Categories table so that only valid categories are assigned to an article.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    ... should I have a separate table for each type of content OR should I add a "Category" field and dump all of the content into just one table?
    the latter
    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,756
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    If an article can have only one category, add a category field.
    If an article can have multiple categories (many to many relationship), you'll need an articleCategories table.
    That doesn't answer the question I asked...


    Debbie

  6. #6
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,756
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    the latter
    What happens if an "Article" is not exactly like an "Interview" is not exactly like a "Case Study"?

    Then would you mess different types of data/records in one "God Table"?

    For instance, most people would store "Parts" and "Customers" and "Orders" in one table even though it is possible...

    Chances are - for now - an "Article" and an "Interview" and a "Case Study" are more similar than different, but that could change, thus my question...


    Debbie

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    What happens if an "Article" is not exactly like an "Interview" is not exactly like a "Case Study"?

    Then would you mess different types of data/records in one "God Table"?
    depends on the degree of "not exactly"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,492
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    That doesn't answer the question I asked...
    The answer was implied (and very obviously). Sorry if you didn't understand, luckily r937 gave it to you.
    Actually, I even gave you some more info just in case you left out important information about your problem like you usually do, and indeed you did as demonstrated by this question
    Quote Originally Posted by DoubleDee View Post
    What happens if an "Article" is not exactly like an "Interview" is not exactly like a "Case Study"?

  9. #9
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,756
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    The answer was implied (and very obviously). Sorry if you didn't understand, luckily r937 gave it to you.
    Actually, I even gave you some more info just in case you left out important information about your problem like you usually do, and indeed you did as demonstrated by this question
    What would make you think

    "Articles", "Interviews", "Case Studies", "Current Events", "Famous Cases", and "Op-Ed
    would be the same thing?

    The names alone imply disparate data.

    I'm surprised r937 thought that should go in the same table.

    It seems like a possibility, but I wouldn't have posted if it was obviously one way or the other.

    If you don't understand something that I post, Guido, then just ask, and stop nipping at my heals.


    Debbie

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    I'm surprised r937 thought that should go in the same table.
    i'm surprised you're surprised


    you presented it like this --
    Quote Originally Posted by DoubleDee View Post
    The way I am building my pages, is to have attributes in my "Articles" table like this:
    - id
    - pretty_title
    - meta_title
    - meta_description
    - meta_keywords
    - page_title
    - page_subtitle
    - written_on
    - author
    - body
    - endnote_listing
    - created_on
    - updated_on


    (**NOTE: In the "body" field I just copy and paste my raw HTML code in there, so all of the content and markup are there.)

    Because of how my table is structured, it seems to me that I could not only have "Articles" in this one table, but also "Interviews", "Case Studies", "Current Events", "Famous Cases", "Op-Ed", etc.
    does an interview have a title? does a case study have meta keywords? does a current event have a body? does a famous case have a created on date?

    as your very first post suggested, all of these "articles" are more similar than different
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,756
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i'm surprised you're surprised


    you presented it like this --does an interview have a title? does a case study have meta keywords? does a current event have a body? does a famous case have a created on date?

    as your very first post suggested, all of these "articles" are more similar than different
    Yes, on the surface, they are similar - if not maybe even identical.

    But to follow that logic, you could say that "Employees" and "Customers" and "Contractors" are all people and therefore should be in the same table, right?

    Like most business problems, I won't know how things should be until I know how things should be?!

    Today, I would say an "Article", an "Interview" and a "Case Study" *are* identical and could go in the same table since each has meta-tags and I am lumping the entire content/copy in one field.

    But over time things could change.

    For example, maybe an "Interview" would break up content into what the "Interviewer" and "Interviewee" say?

    Or maybe a "Case Study" would necessitate breaking up the content more into sections like "Background", "Problem", "Approaches", "Final Solution" whereas an "Article" is pretty much just *one* thing?

    Of course I am asking about these unknowns now, so that I don't choose an architecture that was poorly conceived for growth and scalability.

    To me, I am leaning towards have an "Articles" table, and an "Interview" table, and a "Current News" table because things are more like to diverge than converge.

    Of course, 500 tables later I may hate that approach?!



    Debbie

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    But to follow that logic, you could say that "Employees" and "Customers" and "Contractors" are all people and therefore should be in the same table, right?
    yes, they should

    Quote Originally Posted by DoubleDee View Post
    For example, maybe an "Interview" would break up content into what the "Interviewer" and "Interviewee" say?
    you would still have the entire interview inside a block of html, and you would not want to break down and track the individual questions and answers unless each question and its answer needed to be isolated (and have its own primary key, etc.)

    Quote Originally Posted by DoubleDee View Post
    Or maybe a "Case Study" would necessitate breaking up the content more into sections like "Background", "Problem", "Approaches", "Final Solution"
    i still see the entire case study as a block of html


    Quote Originally Posted by DoubleDee View Post
    To me, I am leaning towards have an "Articles" table, and an "Interview" table, and a "Current News" table because things are more like to diverge than converge.
    my advice is to go ahead and build it that way

    there is no better teacher than experience

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

  13. #13
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,756
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    is to go ahead and build it that way

    there is no better teacher than experience

    That sounds like a vote against my thoughts...


    Debbie

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    That sounds like a vote against my thoughts...
    i would never vote against your thoughts

    i am suggesting that you go ahead and build your app with separate tables for all these different kinds of content

    stop agonizing over it in advance, and save your agonies for what happens after you get going

    but please -- get going

    don't fall victim to paralysis by analysis
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,756
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i would never vote against your thoughts
    You're allowed to disagree, you know?!


    i am suggesting that you go ahead and build your app with separate tables for all these different kinds of content

    stop agonizing over it in advance, and save your agonies for what happens after you get going

    but please -- get going

    don't fall victim to paralysis by analysis
    But I do that so well!!

    (Help me get 100 visitors to my website and/or my clients websites every day, and I'll stop being so paranoid/calculated... )


    Debbie

  16. #16
    Non-Member
    Join Date
    Apr 2011
    Location
    no fixed address
    Posts
    851
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Post your ERD.

    If you don't have an ERD for your database then you will be more likely to stuff something up when you create your database and later on when you code up your back end processing. If you are sure your ERD is correct, then creating the db and the back end processing should be relatively straight forward.

    You normally wouldn't start building a house without a set of architect's plans and so the same should apply to building a database, especially a largish one.


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
  •