OK guys, 'bookamrks' DB again!

Anon

Okay fellas

I've had much advice on this from Allen, Kirk and Vincent (forgive my time lapse, duty calls) but for anyone new to the story here's the deal.

I need to make a bookmarks database for my wifes site. The idea is to create something similar in functionality to a browsers 'favourites' feature. After much discusion and thought I've come up with the following.

Links
id|title|desc|url

catagory
cat_id|parent|name

cat_link
id|cat_id

Okay guys, what I need is a yay or nay on this. I enjoy the lengthy discussions but I've goota build this bugger this weekend so some approval/disaproval type answers would be fantastic!

Much thanks

Nick

gid__

Kinda funny, I've been gearing up to make a bookmarks db myself, simply because I'm tired of never having the bookmarks I want when I want them.

One main thing I can think of is that you might want a "sort" field, so users can put them in any order they wish, also a "date" field might not be a bad idea either, just for informations sake, and maybe something to search on later on, or just another options to order them by.

I have a site that I run for people in an irc channel I'm in (picture db), so I was gonna hack a boomarking system into it, besically for my own use, but also because other people have expessed intrested in the project when I was talking about it a few months back.

I haven't really put too much thought into my database design yet, nor do I even have much on paper yet.... I was planning on starting my project this weekend, (ya right, that's what I said last weekend: 🙂 Anyway, if I get any of my db design done and start coding junk this weekend I'll let ya know what all I come up with.

ace21

Here is the best way to do it. One table

tblLinks
linkid - autoincrement
linkname - name shown to the viewer(link or folder)
parent - the linkid of the parent
link - contains the link or name "folder"

The way it works is this...
you start off and create a main with a parent of 0 with link='folder'. b/c it's the first folder it should have a linkid of 1

when you insert a link or folder that is part of that main you give it's parent field the linkid 1. this goes on downward that each entry will have a parent associated with it.

Now browsing. when you start you pass it a folder value of 1. Then to get its contents you go and select * from tblLinks where parent=1
you could also put where link=folder to get all it's folders or link<>folder to get all the links.

Hope this helps

Ace21

[deleted]

Nay! :-)

I'd do it like this:

Links:
id(pk)|title|desc|url|cat_id(fk)

Categories:
cat_id(pk)|parent_cat_id(fk)|desc

This because one link only appears in one category, and each category is ultimately a child of one single parent category (no threads)

Notice that categories has an FK to itself (categories are subcategories of other categories), so be very carefull when deleting categories.

Anon

Vincent, your proposal is a perfectly valid way to do it, PROVIDED Nick has made the decision that links can only appear in one category. It's certainly going to be simpler to build it that way.

But what Nick has shown us is actually adequate for allowing links to belong to multiple categories (while still keeping the categories as a simple tree.) This is more complex, but also more useful, which is why I suggest and/or seconded the notion (sorry, too lazy tonight to go back and remember which!)

Anon

Unless Vincent or Kirk can point out a problem and I think you were giving it the thumbs up Kirrk, I'm going to go with the structure I posted.

It is more complex but I need to have the same links appearing in different catagories.

I don't understand what 'FK' refers to in your answer Vincent?

If you guys could tell me what I should use as keys in my structure that would be fantastic. Here's what i intend...

Links
id|title|desc|url (pk id)

catagory
cat_id|parent|name (pkcat_id)

cat_link
id|cat_id (pk id)

Much tanks for all your input guys!

Nick

[deleted]

PK=primary key
FK is foreign key, pointing to a PK from another table. In 'real' databases this means that you cannot ever insert a value that does not already exist in the other column (you can't link an url to a non-existing category)

I'd advise a small naming change, because you're bound to mistake 'link' as in hyperlink with 'link' as in 'link between hyperlink and category'.

urls
url_id(PK)|title|desc|location

catagory
cat_id(PK)|parent_cat_id(FK)|name

cat_link
id(PK)|url_id(PK&FK)|cat_id(PK&FK)

Notice that cat_link has a three-way PK, because you can only link one url to each category once. You can link one url to several categories, but no category can have the same url twice.

The 'id' field in the cat_link is there for maintenance, it's much easier to refer to records using an ID than by using the data in the record. (for example when you are printing a list of links and making them clickable for editing/deleting)

Small note on the side: put a little check in your script to make sure that a category can NEVER have itself as a parent. That would give an infinite loop that you may not find untill you have pulled all your hair out of your head.

Anon

Hmmm....
I'll have to do a little reading on Foriegn Keys I guess. I like the id column on the cat_link table that makes a lot of sense.

On this tabel if I put the FK's to one side for a moment, do you mean like

PRIMARY KEY(id, url_id, cat_id)

so the whole table is indexed?

Cheers Vincent

Nick

knickerlas

Done my reading and MySQL doesn't support them so I'll settle for making my code as bullet proof as possible.

When I eventually change ISP I may well switch to PostgreSQL

Nick

[deleted]

Yes I mean that the all columns are in the same Primary key. Not because that makes them indexed (primary keys are always indexed) but because that enforces uniqueness. It prevents you from making the same link twice.

About indexing: It's always a good idea to put indexes on those columns or groups of columns that you will be using in your queries.

Anon

do you mean like PRIMARY KEY(id, url_id, cat_id)

That would be quite unnecessary, since 'id' is already unique.

Instead, 'id' all by itself should be the primary key.

In addition, it's vital for performance to also index on

  url_id, cat_id

and

  cat_id, url_id

Although I suppose since you don't really care about sorting results by the minor column in these cases (after all, the id's are completely arbitrary) you could get away with just indexing by 'url_id' and 'cat_id'.

Anon

Well, regardless of whether mysql supports foreign-key restraints, you still are using foreign keys conceptually when you take a primary key from one table and use it as data within another table. It's just that the dbms doesn't give you any help in keeping the data integrity intact.

Anon

Funny you should say thet, I'm just creating the tables and I just realized that fact myself!

Weird, I've been using databases for a while and have, for one reason or another not paid them as much attention as other aspects of my progamming.

I've resolved to address this lapse immediately (after all I've had the temerity to write an article about databsed sites so I ought to know my stuff!)

Thanks guys

Nick

[deleted]

do you mean like PRIMARY KEY(id, url_id, cat_id)

That would be quite unnecessary, since 'id' is already unique.

Instead, 'id' all by itself should be the primary key.

The point is that the entire combination of id, cat_id and url_id must be unique, which you can do by putting them all into the primary key.

Anon

Well that makes sense to me.
What about this 'perfomance issue' that Kirk has raised? As far as I can see it's pretty good as it is?

Nick

Anon

The point is that the entire combination of id, cat_id and url_id must
be unique, which you can do by putting them all into the primary key.

Here's the gotcha: what's really necessary is that cat_id + url_id be unique, so that you can't have multiple rows all assigning the same url to the same category. 'id' is added just as a convenience in maintaining the table (you said so yourself earlier.) But if 'id' is itself unique, then id + cat_id + url_id is going to be unique regardless of the contents of 'cat_id' and 'url_id'.

It's adequate in this case to say:

primary_key(id)

and then create UNIQUE indexes on cat_id+url_id and url_id+cat_id.

Anon

OK, any time you see "performance" cited as a reason for creating an index, there is (even if the writer didn't realize it) an implied disclaimer "as long as the data set is large enough". In the current instance, the indexes support quick retrieval of (a) all the categories for a particular url, and (b) all the urls for a particular category.

In addition, indexes are generally the method by which a dbms will enforce uniqueness or primary-key-ness, but there's no conceptual reason that a different method could be used.

[deleted]

"and then create UNIQUE indexes on cat_id+url_id and url_id+cat_id.
"

And that's the bit that I was missing.
cheers kirk.

[deleted]

"but there's no conceptual reason that a different method could be used"

somewhere in there is a typo, or I lost my English skills 🙂

A small note on the 'if the dataset is large enough' bit:
Indexes are seperate datastructures that have to be maintained. If you work with a very small dataset (few records in a table) just consulting and updating the index may take longer than doing a sequencial scan of all the records in the table(s).
But as the number of records in your database grows, the time it takes to update and consult an index does not grow as quickly as the time it takes to do a sequencial scan. That's mainly because the indexes you build only contain a fraction of the data that the actual record contains.

Anon

I guess we end up with this right?

urls
url_id(PK)|title|desc|location

catagory
cat_id(PK)|parent_cat_id|name

cat_link
id(PK)|url_id|cat_id (unique url_id+cat_id and cat_id+url_id)

I've not seen this way of using unique, i'd have just used unique(url_id, cat_id) and have been done with it.

Cheers guys!