Now, I'm starting to set up a product database. I could use some advice on the best way to set it up. I'm unsure about one field in particular. The "categories" field. Can this field have multiple values? Many item in the catalog will appear in multiple categories.
Here is the field list that I've come up with so far:
item_no
cats
weight
in_stock
cost
name
desc
included
I would appreciate any advice on how to best lay this out so I don't have to change everything after I realize I laid it out wrong.
Thanks,
Birdman
1) text-based containing multiple keywords so you could just do multiple likes to return matching results.
2) separate table linking a product to the categories it occupies so you could just return all product codes (and by extension products) based on a set of categories
3) multiple fields each representing a keyword, you could query this using simple logic
3 is the easiest but the hardest to upgrade, 1 is simple but lazy and 2 is a little more complex but will yield the best results in the long term as its easy to update/expand and fits with the relational model.
At the moment I'd say what you have seems reasonable enough as you aren't duplicating information and there is really no better way to express the data in a pure relational format.
- Tony
table "categories"
item_no
category_1
category_2
category_3
category_4
category_5
And set the values to "yes" or "no" depending on where you want the item to appear?
Thanks for the reply, dreamquick
Although written in ASP the following has a tutorial on the kind of things you need for a ecommerce database
www.princeton.edu/~rcurtis/ultradev/ecommdatabase.html
Here is a tutorial on php basics
www.webmasterbase.com/article.php/530
Cheers
table "categories"
item_no
cat_no / cat_desc
Where "cat_no / cat_desc" is either the category id or the category description (depends on how ambious you are feeling)
- Tony
Your product would have an ID. The categories would have an ID.
The product_to_categories_link table would contain a product_id and a categories_id.
This way, you can assign a single product to appear in an unlimited number of categories
item_no 12345
cats 1 4 8
or..
item_no 12345
cats 1,4,8
Sorry, I'm very green at this. ukgimp, thanks for the links. I'm off to read them.
Product 1 with YYNNN would mean it was in category 1 and 2.
Product 2 with NNYYN would mean it is in category 3 and 4.
Products (id, name, desc, etc..)
Categories (id, name, etc..)
Product_category(id,product_id, catogory_id)
<added>sorry shady, diddnt read your reply there - that is how it should be done , it will give you less problems in the long run</added>
[edited by: aspdaddy at 5:06 pm (utc) on Oct. 17, 2002]
Product table
--------------
Product_id
Product_name
other product fields
Category Table
--------------
Category_id
Category_name
Products_to_categories Table
----------------------------
products_to_categories_id
products_id
categories_id
Create your categories in the categories table.
Create your products in the products table.
Assign products to categories by creating records in the products_to_categories table.
You can therefore assign a product to multiple categories.
When listing products within a particular category, you will access the data via the products_to_categories table.
Hope this helps!
Product table
--------------
Product_id---------#2684------------#6562------
Product_name---fuzzybluewidget---fuzzyredwidget
Category Table
--------------
Category_id----------1------------------2------
Category_name--fuzzywidgets--------redwidgets--
Prod_to_cat Table
----------------------------
prod_to_cat_id-------1------------------2------
products_id
categories_id
Product table
--------------
Product_id---------#2684------------#6562-----------#9000
Product_name---fuzzybluewidget---fuzzyredwidget--fuzzyplainwidget
Category Table
--------------
Category_id----------1------------------2-------------3------
Category_name--widgets--------coloredwidgets--plainwidgets----------
Prod_to_cat Table
----------------------------
You now have a widgets category (1) in which all the widgets should appear.
prod_to_cat_id-------1----------2-----------3-------
products_id ---------2684------6562------9000-------
categories_id -------1----------1----------1--------
Also, make the colored widgets appear in the coloredwidgets category,and the plain in the plaincategory
prod_to_cat_id-------4----------5-----------6-------
products_id ---------2684------6562------9000-------
categories_id -------2----------2----------3--------
Category Table
id~~~~description
1~~~~~fuzzy products
2~~~~~shiny products
3~~~~~red products
4~~~~~blue products
5~~~~~widgets
6~~~~~flanges
Prod_to_cat Table
prodid~~catid
1~~~~~~~1
1~~~~~~~4
1~~~~~~~5
2~~~~~~~1
2~~~~~~~3
2~~~~~~~5
3~~~~~~~2
3~~~~~~~4
3~~~~~~~6
4~~~~~~~2
4~~~~~~~3
4~~~~~~~6
So if i wanted to find all the shiny products I'd simply do something like;
SELECT *
FROM Products
WHERE ID IN(
~~SELECT DISTINCT ProdID
~~FROM Prod_to_cat
~~WHERE CatID = 2
)
Which should bring back product ids #3 & #4. Obviously if you wanted to find products which are both Blue & Furry you could then do;
SELECT *
FROM Products
WHERE ID IN(
~~SELECT DISTINCT ProdID
~~FROM Prod_to_cat
~~WHERE CatID = 1
) AND ID IN(
~~SELECT DISTINCT ProdID
~~FROM Prod_to_cat
~~WHERE CatID = 4
)
Which would bring back product id #1 - the only furry and blue product you have, hope this explains it all - sorry for not using your example but it was a little to limited to explain using just red & blue widgets.
- Tony
[edited by: Dreamquick at 7:22 pm (utc) on Oct. 17, 2002]
Just wondering if you guys could help me one more time. I followed the above format, but I'm getting an error when I try to veiw the results. This is what I have:
Products table
item_no~~~~description
~~~1~~~~~"fuzzy blue widget"
~~~2~~~~~"fuzzy red widget"
~~~3~~~~~"shiny blue flange"
~~~4~~~~~"shiny red flange"
Categories Table
categories_id~~~~cat_name
~~~~~~~1~~~~~~~fuzzy products
~~~~~~~2~~~~~~~shiny products
~~~~~~~3~~~~~~~red products
~~~~~~~4~~~~~~~blue products
~~~~~~~5~~~~~~~widgets
~~~~~~~6~~~~~~~flanges
Prod_cat Table
item_no~~catid
~~~1~~~~~~~1
~~~1~~~~~~~4
~~~1~~~~~~~5
~~~2~~~~~~~1
~~~2~~~~~~~3
~~~2~~~~~~~5
~~~3~~~~~~~2
~~~3~~~~~~~4
~~~3~~~~~~~6
~~~4~~~~~~~2
~~~4~~~~~~~3
~~~4~~~~~~~6
$query = "SELECT *
FROM Products
WHERE ID IN(
~~SELECT DISTINCT item_no
~~FROM Prod_cat
~~WHERE Cat_id = 1
)";
Appreciate the help!
Anyhow, looking at your table/fieldnames, this join should work.
SELECT item_no, description
FROM Products INNER JOIN (Categories INNER JOIN Prod_Cat ON Categories.Categories_ID =Prod_Cat.Catid) ON Products.item_no=Prod_Cat.item_no
WHERE Prod_Cat.Catid=1
What was the error?
- Tony
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /mypage.html on line 24
Thanks for the help with this! I'm getting the same error with aspdaddy's approach also.
you did it like so
$query = mysql_query("one of the above queries");
while ($row = mysql_fetch_array($query)) //this would be line 24 where $query is invalid
{
do stuff;
}
is that where the error comes from?
have you checked whether the query returns anything?
