'Bookmarks' DB: help with planning.

[Anon]

Hi all
I want to build a database for my wifes site for her bookmarks. I need it to support multiple catagories and infinate sub-catagories and am having a little trouble visualizing how this might be done.

So far I reckon to have one 'master table' containing

id|url|description|title

and a catagories table containg enums of all the catagories but that's where I'm stuck.

I'ts easy to build it if I limit it to one level of catagories but want if I want those to have catagories and so forth. (just like IE favourites)

I don't expect anyone to design for me but a few pointers would be great 🙂

Cheers

Nick

[amcgrath]

table CATEGORY
CAT_ID pk
NAME vc
PARENT_ID fk to CATEGORY

So you could have records like this:
CAT_ID NAME PARENT_ID
1 Stupid Places 0
2 In-Laws 1
3 Sewing Sites 1
4 Needle Point 3

Stupid Places is the main parent (I usuallly call this one EVE, little biblical, but you get the idea.) It's children are cat_id's 2,3 which a grnadcild at cat_id 4. If you really look at it, it'll make sense.

[Anon]

Fantastic!
I especially like your view of womens browsing habits, I seriously laughed my head off! I think I'll keep this away from the missus though eh?

Thanks very much

Nick

[amcgrath]

Your welcome. Another way, though this mihgt get you spun up, especially if you're not used to it, is to create your bookmark table (ID, name, etc) and then create another table call bookmark_grp with ID,PARENT_ID).... this way, you could have one link that shows up as a child of X number of parent links, or as a child of Y, etc. without physically duplicating a specific link....(the primary key of this table would be ID,PARENT_ID combined, btw)

[Anon]

You're right of course, I'm confused. So far I think I'm going with this;

LINK TABLE

id|cat_id|title|desc|url

CAT TABLE

cat_id|name|parent

I'm figuring on being able to grab the whole db as a multi-dimentional array or just get all links from a specific catagory.

What do you think, should I build it?

Much thanks

Nick

[Anon]

what do you think about keys?

I thoght id in the first and name in the second?

Nick

[amcgrath]

1. Sure, I'd do it, it looks pretty much good to go. (The only thing I'd do is create a cat_id sequence and make this the default value for the cat table, instead of something like serial... that's just me though).

2. I'd select links that belong to a category. Say on your main page, you run a query :

SELECT c.cat_id,c.name,
l.id,l.name as title,l.url
FROM cat c, link l
WHERE c.cat_id = $cat_id
AND l.cat_id = c.cat_id
ORDER BY c.name

(NOTE: I'd add a description field to the LINK table, that way you could show a page with the description and an option to visit, or to drill further down the cat tree)

You can stick this into an array, which will basically give you a category and all the links associated it with it. When a person clicks any of these links you'll not only be able to retrive the link information for display (read above note) but also all of the links/categories associated with this link's cat_id.

3. No, cat_id should be the P/K in the cat table. I'd put a unique index on cat_id,parent_id as well.

Good Luck, if you get it online lemme know.

[Anon]

This is a very good suggestion, especially if the overall database is going to grow to any substantial size. Any time you have to categorize all information by putting into a single heirarchy, you end up having trouble finding things since you may not remember the right category. Being able to assign an item to multiple categories multiplies your chances of finding it later on.

[Anon]

Hmm...
Not really following this bit.

So far there are two tabes

Boolmarks_links
id|cat_id|title|description|url

and

bookmarks:catagories
cat_id|name|parent

Now how would it work with the third table?

And do I just give the user the option of inderting into multiple catagories the time of insertion, is that what you mean?

Cheers

Nick

[Anon]

You get rid of 'cat_id' from 'bookmark_links' because that's the part of your design that says, a link can only belong to one single category. Instead, you have this:

  links
  id|title|description|url

  categories
  id|name|parent

  link2cat
  link_id|cat_id

You could certainly make it possible for the user to add to multiple categories.

[Anon]

Okay, got it. Certainly makes sense to me!

Thanks very much

Nick

[Anon]

One small thing...

Does this mean that in your example

links
id|title|description|url

categories
id|name|parent

link2cat
link_id|cat_id

the catagories table will contain duplicate names like

id|name|parent
1 |one }0
2 |one |0
3 |one |0
4 |two |3
5 |tre |3

Thsnks again

Nick

[Anon]

Dear Nick,

My company does some unique bookmark applications. We allow you to build a list n links deep and share to readers (with permission of course).

This may or may not ne what you are looking for.

Gil Borman
founder
netjumper.com