Newbie question on database structure.

[Anon]

Hi. I'm working on a PHP/MySQL based website, and I'm having a bit of trouble determining how to structure the database. The idea is that the website consists of a number of articles (documents), ideally referrenced by id numbers, with the following wrinkles:

1) Some articles may be split into separate pages at arbitrary points (i.e. not every x number of lines).

2) Some articles may be subordinate to other articles, sometimes several levels deep (i.e. articles a, b, and c may be subsections of article d, which along with article e is in turn a subsection of article q).

3) Related to the above point, it may be necessary at times to generate a heirarchal "map" of a given article's structure - an on-the-fly table of contents, so to speak.

What I can't quite figure out is how one would best reflect the structure of this site in the structure of one's database in such a manner as to minimize the amount of data one has to slog through to determine what fits where and in what order. Anyone have any pointers? Recommended reading? Anything? Keep in mind that I'm rank newbie with prior experience only in simple PHP/MySQL applications like guestbooks or news-update pages, so please use small words. 😉

• David Prokopetz.
  [e-mail]

[edwardsbc]

1) Keep pages in a table with ID's, then in another table keep article ID's and Page ID's. Both of these tables are separate from the article table. A simple join can then retrieve all pages for a particular article.

2) In your 'article' table have a column for 'parent article' which contains a zero if no parent or another article ID if this article is subordinate.

3) Once (2) is done, a 'tree' can be generated based on parent/child relationships. WARNING: The only way I've been able to generate these trees is by using recursive functions/queries. It can get VERY slow for a large number of parents and children. Perhaps someone else has figured out a better way...?

I have some sample code if your are interested in how this works. Note, if you don't care about the structured tree, retreiving data from the joined tables is fast. Its the process of putting children beneath the parent that can get slow. If you are just counting or summing values there is no problem.

[Anon]

Yes, sample code would be helpful, if you would be so kind. Efficiency really doesn't concern me at this point - I have very little idea how to go about it, period, so anything to help put me on the right track would be invaluable.

Thanks in advance,

• David Prokopetz
  [e-mail]

[edwardsbc]

Ok, I'll try to expain what this code does. I have a "Domain" tree table with domainid (PK), parentid, and some other fields. This snippet from a file called viewdomains.php just shows a simple tree with a two hyphen indent for every child:

Domain1
--Domain2
----Domain2a
--Domain3
----Domain3a
------Domain3a-x

etc.

#Begin Snippet ViewDomains.php
#Show chosen top level (ROOT or sn[domainid])
$sql="SELECT * FROM $tbldomains WHERE domainid=$slevel";
$r=odbc_exec($h,$sql);
if (!$r) {die("Query Failed. $sql");}
while (odbc_fetch_row($r)) {
$dn=GetFieldsDomains($r);
echo '<tr class="light">';
echo '<td>'.$dn['code'].'</td>';
echo '<td>'.$dn['description'].'</td>';
echo '</tr>';
$flag=true; #needed to alternate rows
$z=DomainTree($dn['domainid'],1,$h);
}
#End Snippet

The important part is last line where the function DomainTree() is called. Here's the function:

#Function DomainTree
function DomainTree($id,$indent,$h) {
$e=$GLOBALS['tbldomains'];
$sql="SELECT * FROM $e WHERE parentid=$id ORDER BY code";
$f=odbc_exec($h,$sql);
if (!$f) {die("Query Failed. [functions.php DomainTree]");}
$s=str_repeat("--",$indent);
while (odbc_fetch_row($f)) {
$fn=GetFieldsDomains($f);
if ($GLOBALS['flag']) { echo '<tr class="med">'; }
else { echo '<tr class="light">'; }
echo '<td>'.$s.' '.$fn['code'].'</td>';
echo '<td>'.$fn['description'].'</td>';
echo '</tr>';
$GLOBALS['flag']=!$GLOBALS['flag'];
$e=DomainTree($fn['domainid'],$indent+1,$h);
}
return true;
#End Function DomainTree

Notice how DomainTree() calls itself. This is the whole trick. I have other, more complicated examples where I'm pulling information from another table and displaying it under the proper domain, but the concept is the same.