Storing Product Information - ADVICE NEEDED

[Canman2005]

Hi all

 

I need some advice, basically im building a shopping cart and I have this example as one my product lists

 

main category (cloths)

sub category (tshirts)

sub sub category (funny tshirts)

sub sub sub category (starwars)

sub sub sub sub category (R2D2)

 

I need to store what categories each product is assigned to.

 

Now I need some advice, should I have in my products table the following fields

 

maincat

subcat

subsubcat

subsubsubcat

subsubsubsubcat

 

and store the relevant category ID numbers in each field of those 5 fields?

 

Or should I just have one field and store the category ID numbers in one field, for example 2,34,234,543,788

 

Does that make sense?

 

Thanks

 

Ed

[Barand]

try

[pre]

table category

---------------

catID    (PK)

category

parentID

 

data

+-------+----------------------+----------+

| catID | category            | parentID |

+-------+----------------------+----------+

|  1  | cloths              |    0    |

|  2  | tshirts              |    1    |

|  3  | funny tshirts        |    2    |

|  4  | starwars            |    3    |

|  5  | R2D2                |    4    |

|  6  | shirts              |    1    |

|  7  | long-sleeve          |    6    |

|  8  | short-sleeve        |    6    |

+-------+----------------------+----------+

[/pre]

[Canman2005]

Thanks Barand

 

But in the products table itself, should I store the category ID numbers in one field or have a field for main category, a field for a sub category and so on?

[DarkWater]

Have a products table, a category table, and a category_associations table that stores all product associations.

[Canman2005]

and hold all the products and related categories in the category_associations table?

 

if so, how do you suggest I store this data in the category_associations table?

[Barand]

You only need the association table if a product can belong to more to one category.

 

If a product belongs to only one category

 

 

[pre]

category              product

----------            -------------

catID    -----+      productID

category      |      prod_description

parentID      +---<  catID

[/pre]

 

If a product can belong to more than one category

 

[pre]

category              prod_category        product

----------            -------------        ----------

catID    -----+      id              +---- productID

category      |      productID  >---+    prod_description

parentID      +---<  catID

[/pre]