[thelist] Database Schema Suggestions

[Burhan Khalid]

Evoltees:

   I'm once again faced with a database design problem, and not quite 
sure what is the best way to solve it.  The task is to design one of 
those unlimited category directories.  Like dmoz.org or yahoo 
directories, et. al.

   I'm really not quite sure how to desgin the database, but I do 
realize that if I'm careless with the database design, the rest of the 
application will fall apart.  The database server we will be using is MySQL.

   My current thoughts are to create a table of catgories, and to add 
NULL in a field if the category is a parent.  But since there can be /n/ 
number of children (nodes) I'm sure this will fall apart fairly quickly.

   The other idea I had was to store the relationship information in the 
table itself. So there would be a column called "map" with something 
like 1:3:4 which would describe all parents for this particular node.

   I'm convinced there is a better method to structure the database so 
that it is easier to query for the relationships.  Any thoughts on this 
would be graciously recieved.

<tip type="Database Diagramming Tools" author="Burhan Khalid">

   If you are looking for a free tool that will create relationship
diagrams from different database servers, consider DBDesigner 4 from
fab force [ fabforce.net ].  It works on both Windows and Linux, and has
the ability to "reverse engineer" relationship diagrams from databases.

</tip>