Db schema help

Drakla

I'm creating a multiple catalogue database that will serve multiple sites, each site being assigned the id of the catalogue it will use.

For the categories in each catalogue I'm using the id / parent id scheme that Drawmack showed me about a year ago (and have used loads since)

id pid name
9 0 electricals
11 9 fridges

Which is fine for one catalogue because when you display the top level you just check for all categories with a pid of zero.

To adapt this to a multi-catalogue scheme I've got two choices - add an extra field to the categories table saying which catalogue it belongs to, then on entry search for all categories for this catalogue with a pid of zero - thing is, the catalogue field isn't really necessary after that as you travel down the category tree.

Option two, when a new catalogue is added, create a category that will be the top level for that catalogue and assign that to the catalogue. Then all its top level categories would use that as their parent.

Catalogues
id name toplevelcat
3 site1 883

I know bulking the table with the extra field won't be that expensive, as I can't see there being more than a few thousand categories. Plus having the extra field would be useful for tracking orphaned entries if errors happen due to corruption. But I'm a swine for the optimisation, so would like a few oppinions on this.

Cheers.

tomhath

I'm having a lot of trouble following your two alternatives. But I would consider adding a couple of tables to the database.

First table describes a catalog (including the site).
Second table that describes a catagory and links back to the catalog.
Third table that describes the item and links back to a category.

If you have sub-categories within a parent category you can use the scheme you have now (in the second table).

Drakla

Cheers Tomhath - that's exactly what I do have in place at the moment, I thought I might have explained it all really badly.

Basically I was asking whether or not I should have a field in the category table to say which catalogue it belongs to, because you can code it so that you don't have to have one, but can still use the category table for loads of seperate catalogues. So for each new catalogue I'd create a 'dead' top level category in the table, then chain all the child categories under that - The catalogue table would then just have to say what the top level category was.

Or I could add a catalogue field to the category table, then when a new one was added you'd set all the pids of the top level categories to zero and set their catalogue number. But as you start browsing the catalogue and enter one of the top level categories, to show it's subcats you just look for those with their PIDs the same as the category you're in - the extra catalogue field wouldn't be needed.

So is it a waste putting it in, or would the added security of being able to track where a category was if perhaps a server crash happened make it worth doing anyway.

Hope that makes more sense.

Thanks,
D.