I have a database with products and I want to group them into categories. I know that to do that I need a separate table for categories and add a categoryID field to the products table. In this way, however, I can only assign one caterogy to every product. How can I assign more than one caterories to a single product? Any help will be much appreciated.

this sounds like the classic many-to-many relationship to me, in which case you need a structure something like . . .

products table

link table

categories table

each product has a unique id
each category has a unique id

in the link table each record has two fields, each is the unique id (foreign key) of one product and one category

now you can do table joins so if you want all products in a category, select all the records from the link table joined to the products table that have category nnn

this reference shows it nicely and probably explains it better <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>



www.tekstenuitleg.net/en/articles/database_design_tutorial/8

Thank you very much. How then can I create an "add product" page in dreamweaver? I'll have categories and subcategories and I'd like the field look like this:

category: writing instruments (category)
-pens (subcategory)
-pencils (subcategory)
recycled products (category)
-recycled pens (subcategory)
-recycled sticky notes (subcategory)

The dash represents a checkbox and I'd like to be able to tick e.x. both pens in writing instruments and recycled pens in recycled products for a product called "recycled pen".

What I will have is the following tables:
1. products with subcategoryID as the foreign key
2. subcategories with categoryID as the foreign key
3. categories with no foreign keys
4. a linking table with productID and subcategoryID

Please can you let me know how to do it in dreamweaver?

Hi
must admit i'm not the best coder for stuff like this in DW <img src=../images/dmxzone/forum/icon_smile_sad.gif border=0 align=middle>

Your add product would be and insert record action, I know that works well in DW as far as records being inserted into a single table, and also foreign keys going in, for example from dropdown lists - so that would give you the category, I've not done anything like the many to many relationship using DW, your best bet there is probably a search for something like many to many relationship using dreamweaver

when it comes to the actual coding part I hand code in php because I have a coding background. I found DW couldn't do a lot of the stuff I could turn out in a few lines and that I spent ages trying to do things that were really straightforward in php.

I guess there will be others who disagree with this approach - however I've developed a style of coding and dropping things into DW 'framework' pages that works for me - if you examine the code for something that DW has created you can see how it slots snippets of php in.

what code would you use for it then? maybe I could just put the code in if I know what it would have to be.

the code would have to be specially written - using the database tables and same variable names as DW is using, and it would have to have the appropriate layout CSS etc to fit in with your existing pages, it's not something that could be done remotely