...

View Full Version : best db design for unknown # of paragraphs



perayo
01-21-2004, 08:48 PM
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.

raf
01-21-2004, 10:50 PM
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


$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


$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.

mindlessLemming
01-22-2004, 01:37 AM
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

raf
01-22-2004, 07:24 AM
You're welcome. Happy researching :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum