Database design (relative?)

item's with more catagories

[ketel]

Hi,

I've done a little scripting with php en mysql and now i'm working on a new site that requires a bit more.

I've got item's that can belong to one are more head categories and subcategories.

I was thinking to put two fields in with the description of the item Hcategory and Scategory, these can be filled up with category names separated by ',' .

I could then break them apart with strtok() are explode(), but than i can't use the query with distinct and that's what i won't to use to show categories.
It all seems slow and it produces a lot of overhead.

As you can see I don't have much experience in relative database design and i'm seeing it rather flat one to one could someone point me to the right direction.
thanks

[dcrombie]

You need three tables if you want to be able to query them properly:

items 
----------- 
id int 
name varchar

categories 
----------- 
id int 
name varchar

linking 
----------- 
itemid int 
categoryid int

;)

[ketel]

Thanks,

So a query for all item's in a certain category would look like this

SELECT items.name FROM items,linking,categories WHERE categories.name = "$preferd category" AND linking.categoryid = category.id AND items.id = linking.itemid

I'm i hitting any target are am i lost in space.

I also would have to use some kind of join INSERT query to make it easy to insert.

[dcrombie]

Or, a bit neater:

SELECT items.name FROM items 
INNER JOIN linking ON (items.id = linking.itemid) 
INNER JOIN categories ON (linking.categoryid = category.id) 
WHERE categories.name="$preferd category";

Yes, for the INSERT, you need to capture the id of the new item and use that to populate the linkings table.

;)

[ketel]

:-)

I need at least one more table I think because i've got the category en subcategory, I would like to be able to sometimes show subcategories if they press the category and sometimes the items whatever subcategory, depending on the total items to show.

But now I've got an idea how to create my database, so i will try some things out first.
Thanks for your help.

[dcrombie]

geen punt. I'm sure you can work it out now.

[SeanW]

I need at least one more table I think because i've got the category en subcategory

You could have your category table include a parent field, so subcategories can link back to their parent.

Sean

[moltar]

You can also use Modified Preorder Tree Traversal algo. It's more complex, but more efficient in some situations.

[ketel]

-> SeanW: the parent field seems to be a valid option just got to check out how it relates to a subcategory that can have more topcategories, but i don't think that will be the case. Thanks

-> moltar: Its seems really interesting to use where you have a lot of categories like mine does, the left en right number gives you the information to where it stands in the treestructure.
I can see it in my head but to implement in a real live situation seems a bit hard to me at the moment also the updates and inserts seem to be not that easy.
At this point The Adjacency List Model seems a better option for me (partly how SeanW suggested) as I won't have thousands of categories.
But the link is in my favorites for use later.

->dcrombie : how did you know I spoke dutch, I now my english is a bit rusty, no the name ketel did it ;-)