Thanx for all you guys for

pankaj.sharma — Sat, 05 Jan 2008 06:21:46 +0000

Thanx for all you guys for this nice discussion

L3ZL1E — Wed, 19 Dec 2007 22:24:19 +0000

Ok, thanks, that makes sense. Then it would follow that they would then be sequentially numbered, as they are added-which would solve my dilemma.

I just like the fact that I can look at the table names in my database admin and know that is the category or subcategory I am dealing with-it's already very frusterating that they have been organizing the information as they go along...and I've had to tweak it as I go along...but I took on the project, so gotta just deal with it!

Thanks again! Much appreciated!

pr0gr4mm3r — Wed, 19 Dec 2007 22:04:00 +0000

Basically, you're asking for a many-to-many relationship. Each item can have multiple categories, and each category can have multiple items. In databases, that it not possible without using an associative table. This is a table in between the products and category tables.

For example, you need a table that holds the category id and the item id. There will be an entry in this table for each item-category relationship. If you have the item with the id '354', and a category with the id '546', and they are associated with each other, then you enter a record in the associative table with both these ids. If this item is also part of category '547', then you put another record in the associative table for that as well.

If this associative table has the name "Item_Cat", and you want to find all the items in category 546, then you write a query like

SELECT * FROM Item_Cat WHERE category_id = '546';'

You can easily join that with your items (table name: "Items") table by doing something like this:

SELECT * FROM Item_Cat INNER JOIN Items ON Item_Cat.item_id = Items.item_id WHERE Item_Cat.category_id = '546';'

L3ZL1E — Wed, 19 Dec 2007 20:36:30 +0000

There is one product table, with the basic product information in it, but the products can exist in more than one category/subcategory, therefore i made tables with product numbers to associate them with categories and subcategories because I really didn't know how many columns I would need if I went that route (they are organizing the information as I make the site). Seemed to be the most logical solution at the time. Maybe it was a bad idea???

pr0gr4mm3r — Wed, 19 Dec 2007 01:58:44 +0000

Creating one table per category doesn't seem necessary. What is this site for?