Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    15

    Bad design of CMS article tables?

    As part of the PHP/MySQL Content Management System which I'm constructing I have an article table which houses all of my articles and all of its components such as images etc. In addition to this I have a pages table which contains the content for all articles which have more than one page. I have a feeling that my table design isn't as good as it could be. Could anyone suggest improvements to what I have below please?

    For instance at the moment I have the first page of the article in article table because most articles only have one page. The rest of the article's pages are in the pages table, is this a mistake?

    article Table
    Fields
    article_id
    writer
    subject
    headline
    subheading
    keywords
    description
    article_text
    picture1
    picture2
    picture3
    created
    modified
    published

    pages Table
    page_id
    article_id
    pagenum
    pagetitle
    pagecontent

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, it's not a mistake, but it's more complicated than it needs to be

    use just one table, and have page 2 link to page 1

    of course, this begs the question of whether there needs to be a page 2 in the first place

    i would let the front end decide where to split an article into pages

    might have a totally different cutoff point for smaller devices, for example
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Posts
    15
    I think I understand what you mean. But if I had, for example, all the 4 pages of the article in the article table I may have a problem with the article_id field because at the moment it is the primary key of the article table.

    Doesn't the first part of your suggestion mean that with each page of the article, the article_id would have to have the same value. Is there a good way to get around this?

  4. #4
    Join Date
    Feb 2005
    Location
    Colorado Springs
    Posts
    222
    Do you intend for this to be a "flat" table? It seems to me that list tables might be helpful. For example, a KeyWord table that can be linked to your article table via an intermediary table. Perhaps the same with authors (if there is more than one author per article).

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by googly
    ...if I had, for example, all the 4 pages of the article in the article table I may have a problem with the article_id field because at the moment it is the primary key of the article table.
    well, like i said earlier (and i repeat for emphasis now), it might be a better idea not to have "pages" at all, just articles -- one article, one row -- then your front end could decide where to split an article into pages

    but if you must have pages, yes, then the combination (article_id,page_no) would be the primary key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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