I'm going to try my first site with

[cygnusx197]

I'm going to try my first site with content from a db with php, and am stuck on how to build the database for my bookmarks.

I guess the folder analagy is pretty accurate.
You can have multiple categories, with multiple subcategories, and more subcategories in those, and yes... another level of subcategories, with no fixed number of tiers.

So for example, programming -> web related -> php -> devshed.com
or
linux -> distros -> redhat -> package managers -> up2date -> howto.html

I know to create a table with main categories (design, programming, hardware, personal, ect.) and assign an id.

I was going to create another table for subcategories, and join them to the parent category by its id, but then I realized that I have several layers of subcategories beneath each of those.

It just doesn't seem very efficient to have a table for every "folder" in my set of bookmarks, and could use a tip or two.

Here's what I'm thinking about now:

bookmark_parents table defines top level categories:
id category
1 design
2 linux
3 programming
4 misc

bookmark_subcategories table defines ALL subcategories, regardless of how many tiers deep they go... like a folder within a folder within a folder within a folder:
id subcategory
1 php
2 perl
3 flash
4 distros

bookmarks table:
id bookmark
1

http://www.devshed.com

2

http://www.perl.com

3

http://www.redhat.com

4

http://www.were-here.com

bookmark_relations
id bookmark parent
1 1 1
2 2 2
3 3 4
4 4 3

 

[sleipnir214]

You only need two or three tables, depending on whether any given link can appear in more than one category.

Your "Categories" table can store all categories, regardless at which level a category appears. Assign each category an id number, and start numbering categories at 1. Record the parent category of each category in your table, with the "parent" column of top-level categories set to 0 (zero).

Store your links in their own table, with one column as the id number of the category in which it resides.

If a link can appear in more than one category at a time, you'll need a "many-to-many" relationship table which has two columns, one the id number of a category, the other the id number of a link.

To show your specific two examples ("programming->web related->php->devshed.com" and "linux->distros->redhat-> package managers->up2date->howto.html&quot in tabular form (assuming a link can only be in one category at a time):

Table Categories:
[tt]
id Name parent_id
1 Programming 0
2 linux 0
3 web related 1
4 distros 2
5 php 3
6 redhat 4
7 package managers 6
8 up2date 7[/tt]

Table links
[tt]
id link category_id
1 devshed.com 5
2 howto.html 8[/tt]

______________________________________________________________________
TANSTAAFL!

 

[GiffordS]

Just have to say.... excellent answer.