Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    May 2003
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    best db design for unknown # of paragraphs

    I am creating a table insid a db for news articles. There are the following fields: id, headline, body, source. Each article has a different number of paragraphs and so in the 'body', I would enter the data as
    paragraph 1 content<P>paragraph 2 content<P> [and so on]

    But I would like to enter data without having to put the '<P>' tag in between paragraphs. Say I want to change <P> to <hr>, that means I would have to go thru each entry to change it. I've resorted to adding additional fields namely body1, body2, body3, etc. each for a paragraph.

    There's gotta be a more sufficient way to do this...right?
    Thanks.

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    one of the golden rules of relational db-design is:
    "if you don't know how many instances of something your gonna have, then you need a 1 row per instance approach"

    so you need an extra table like 'paragraphs' that looks like this

    parID | id | paragraphtext

    id = the id of the articles-table

    Then you can join on the id-columns in both tables to compose the article

    Like
    PHP Code:
    $sql="SELECT parargaphtext FROM articles INNER JOIN paragrahs ON articles.id=paragraphs.id WHERE articleID=" $article
    $result 
    mysql_query($sql,$link) or die ('Q1 : ' mysql_error());  //$link = connection-identifier
    if ($result){
              if (
    mysql_num_rows($result) >= 1){
                  while (
    $row=mysql_fetch_assoc($result)){
                      echo (
    '<p>' $row['parargaphtext'] . '</p>');
                  }
              } else {
                   echo(
    'No parargaps for this article');
              }
               
    } else {
              
    $error =('Possible databaseproblem.
                        Please notify the administrator.'
    );

    So if you then want to change the paragraph tags, you only need to do that inside the while loop once for all articles.

    Of course, the real sollution is to add a parargaptype to the paragraphtext and then store a partypeID inthere, or a typelabel, that you use as the class for that pragraph.

    parID | id | paragraphtext |partype
    1 | 1 | Practical db-design for generic multi-layer CMS | title
    2 | 1 | This article discusses a practical model to ... | shortdescr
    2 | 1 | The first rule of all CMS implementations should be ...| argument

    Like
    PHP Code:
    $sql="SELECT paragrahs.parargaphtext, paragrahs.partype FROM articles INNER JOIN paragrahs ON articles.id=paragraphs.id WHERE articleID=" $article
    ... 
    while (
    $row=mysql_fetch_assoc($result)){
            echo (
    '<div class="'$row['partype'] .'">' $row['parargaphtext'] . '</div>');

    and then you specify the paragraphs layout for each type inside your CSS
    Like
    .title {
    ...
    }
    .argument {
    ...
    }

    You see? Each independent layer its own task and definition to ensure optimal maintenance and flexability.
    You know it makes sense.
    Last edited by raf; 01-22-2004 at 08:21 AM.

  • #3
    Senior Coder
    Join Date
    Oct 2003
    Location
    Australia
    Posts
    1,963
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for that raf, that was quite a helpful mini article, got me doing a little more research into intelligent dB design.
    Even though I don't use php (yet).
    Cheers,
    Andrew

    I take no responsibility for the above nonsense.


    Left Justified

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You're welcome. Happy researching


  •  

    Posting Permissions

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