help with normalization and optimization

[Anon]

Hi I am making a 'bookmarks' database and pretty much have it sussed using two tables but I know that it might be better to use three but can't grasp it.

Here are the tables:

links table
id|cat_id|title|url (PK=id, cat_id)

catagories table
cat_id|name|parent (PK=cat_id, name)

I was told that to allow duplicate catagories and still index the 'name' I'd need three tables. As you might see I'm a little lost so any help would be much appreciated.

Cheers

Nick

[amcgrath]

Ok, Nick, here we go again 😉

1. Drop the parent column on categories table.

2. Drop cat_id column from links table.

3. Drop the column name as the pk of cateogries table.

4. Create a table CAT_PARENT_GRP
   with CAT_ID,PARENT_ID columns.

5. Create a link_cat_grp table with CAT_ID ID columns.

So, now you sohuld have this:

links
id|title|url

cat_parent_grp
cat_id|parent_id

link_cat_grp
cat_id|id

categories
cat_id|name

Enter a couple categories into the categories table. So that it becomes clear, only enter three (COLORS,BOOKS,CARS)

Now, in the cat_parent_grp table enter the id numbers for these three example categories with 0 as the parent id. These three categories no reprenst the top level of your category tree.

Now, enter these links into the links table (www.colors.com,www.books.com,www.cars.com).

In the link_cat_grp table, enter the corresponding id's for these three links, with the cat_id that matches (in this example it's obvious.)

And now, technically you are done.... but wait. Lets say you have an idea. That idea is to add www.pastels.com to your book mark list. It definitely would follow under the "color"category, but you want to define that even further. So, what you would do is this:

Enter "www.pastels.com" into your links table.

Enter "pastels" into your categories table.

Enter the cat_id for the pastels cateogry into the cat_parent_grp.cat_id column, with the cat_parent_grp.parent_id value being the cat_id for the "colors" category.

Lastly, enter the id from the links table for "pastels" into the links_cat-grp table, with the cat_id for pastels found in categories talbe.

Get that far, and see if it starts to make sense. I'll walk you through the rest once you get that far. (Don't worry, when we're all done, you'll be a wiz at this and it will be clear as a bell.)

[amcgrath]

All that manual entering will be replaced with PHP code soon enough, I just think sometimes to better understand something, hands on slaving usually works the best 😉

[Anon]

Wicked.
Thanks for taking time out to explain this to me Allen. I'll get on it now and let you know how it works out.

Many thanks

Nick

[Anon]

Okay, I've got them all in, can you tell me where I need indexes and auto increments? I started to add them myself but soon realized that I might be jumping the gun a little!

Cheers

Nick

[Anon]

Allen,

This is all fine, but it seems as though the extra table for categories really isn't needed if what we want is a simple tree (where, in very unlifelike form, each object has only one parent.) Your fuller design allows networks (and even complicated ones that aren't DAG's, etc.)

For most simple cases, a plain tree is good enough, and so having parent_id directly be part of the categories table is good enough.

[Anon]

The only things that should ever be auto increment are the ID columns that are used as primary keys.

[Anon]

Isn't the point to allow catagories to have more than one parent?

Maybe I'm missing something, but I would like the database to be capable of having catagories with the same name but different parents (it should really resemble a browsers bookmarks)

Nick

[[deleted]]

There's a big difference between categories that have the same name, and categories that have more than one parent.

you can make a category 'bicycles' with a subcategory 'red' and you can make a category 'cars' with a subcategory 'red'.

If you had one category called 'red' that had both 'bicycles' and 'cars' as parents, and you wanted to move up to the parent category, how whould you know which one to go to, 'cars' or 'bycicles'?

[[deleted]]

Interesting method. Cryptic, looks bloated, but interesting.
Are we still talking about categories with parents though? it looks like you have two tables too many for that...

[ace21]

Vincent is right. You really can't have it have more than one parent as you lost track and if you are looking for something that resembles a folder structure you wouldn't want any subcategory to have more than one parent. And if you are trying to do it like a folder structure you can break it down in to one table and make the whole process easier.

Ace21

[Anon]

Interesting discussing guys but I really need some help designing this bookmarks database. The way I see it is that using Allens original idea with Kirks suggestion to drop the fourth table would be about right.

But after all this I'm still lost. Could someone tell me how so this the simplest way please.
I just need it to work like a bookmarks tree and be able to support 10 or 1000 bookmarks.

Many thanks to all who've offered help on this 🙂

Nick

[[deleted]]

Have a peek at this:
http://www.hvt-automation.nl/yapf/database/dbdesign_threads.php

[amcgrath]

I hate to be brief, but I have to run down to North carolina for the day. Trust me, this has been an enjoyable thread.

OK, to answer quick, Vince, each query to the database would ALWAYS need to have a parent_id provided. So, something like this simple example:

SELECT links., cat., cpg.parent_id
FROM links, cat_parent_grp AS cpg, categories AS cat, link_cat_grp AS lcg
WHERE cpg.parent_id=3
and cat.cat_id=cpg.cat_id
and lcg.cat_id=cat.cat_id
and links.id=lcg.id ;

Knowing the parent_id is critical to making this work. Luckily, as you choose children of particular parent, they themselves become potential parents as soon as the data is retrieved, while the original parent (in this example id # 3) would become, in essence, the grandparent.

I gotta run, I'm running late. I'd really like to keep this thread going, if it's doing anybody any good. Nick, sorry if got to complex, but that's the cool thing about dicsussion boards.

FYI, this methodology was put into place in production for a now defunct web site (www.allaboutboats.com, among others) and it worked pretty good. We needed links to appear across different categories and did not want to duplicate the links themselves in the link table).

vincent wrote:

There's a big difference between categories that have the same name, and categories that have more than one parent.

you can make a category 'bicycles' with a subcategory 'red' and you can make a category 'cars' with a subcategory 'red'.

If you had one category called 'red' that had both 'bicycles' and 'cars' as parents, and you wanted to move up to the parent category, how whould you know which one to go to, 'cars' or 'bycicles'?