most efficient way of storing product list

mrsocks

hello,

i am building a store and would like to know the most efficient way of storing the products.

requirements are:
- there are going to be 1000's of products.
- one item may have a few choices (different colors) each with different skus (the sku shouldn't have be the product number because it is possible to have a product without a sku).
- the choices shold allow for price upgrades too. (if a person chooses a certain color that is more expensive, i should be able to handel that in the price and checkout process)
- i would like to be able to assign one product to multiple categories. each category has its own unique cat_id number.

i think those are the main points i need to cover.
currently, i have a way of doing it that implodes the cat_id numbers into one feild of the product row. same thing for the option skus. this process works nice and fast, but i want to make sure that this is the most effiicent way of doing it. also, right now, i cannot think of a way to easily add price levels to the different items at different choices.

thanks for any guidenace you can provide.

Happy Holidays.

ps.
anyone from London area? going there in may and would liek to know what you would suggest to see?

altexis

mrsocks I don't like to play the smart guy... but all this sounds very basic database skills.

you will definitely need to make a different table with the data of your products, a different one for the prices (once they are going to change a lot), another one for the categories and one more for the links between categories and products. they will all have to have a primary key and link themselves with indeces.

If you are not very experienced with databases make lots and lots of tests before you run the final version of your project.

DigitalExpl0it

if you want, here are something already made, php/mysql you might benifit from them

http://www.hotscripts.com/PHP/Scripts_and_Programs/E-Commerce/Online_Catalogs/

mrsocks

Originally posted by altexis
mrsocks I don't like to play the smart guy... but all this sounds very basic database skills.

you will definitely need to make a different table with the data of your products, a different one for the prices (once they are going to change a lot), another one for the categories and one more for the links between categories and products. they will all have to have a primary key and link themselves with indeces.

If you are not very experienced with databases make lots and lots of tests before you run the final version of your project.

hello fellow junior member altexis,

if having a table for the prices, one for the cat->product, one for the cats and one for the products is the most efficient way to go, then thank you.

yes, this is a basic way to do it, but i am looking for the most effient. currently, I do have a table for the products and a table for the categories. in the product table, i have a column for categories. when i enter a product, i am able to choose multiple categories. each category is seperated by a pipe and then to display the list of items from a certain catogory, i use a LIKE statement. that goes through the products and then pulls out the matching records. this currently works very fast for the size and traffic load of site that i have created.

DE,
thanks for the link. i already do have a cart working and am looking for the advice from experienced people if what i am doing is the most effienct. I can make the whole process work using checks against the product id number and then changing the cost accordingly but sine i do not have a formal background in database design, i just wanted the advice as to how the database should be designed. if what altexis said is the most efficient way to do it, i can set that up with out problem.

i am looking to add these new features and want to make sure that i am headed in the right direction.

thank you all for your advice.

swr

Originally posted by mrsocks

each category is seperated by a pipe and then to display the list of items from a certain catogory, i use a LIKE statement.

This is not very good. Because you are using LIKE, your DB can not make use of an index, and will have to scan through every row in the table to perform the query. If you end up having dozens or hundreds of items in the table, it would be faster to make use of indexes. If you end up with thousands of items and high traffic, depending on LIKE could bring your database to a crawl.

Using LIKE may not kill your particular application, but its really no harder to do things the proper way, once you know how.

What you want to implement is a many-to-many relationship (many products, many categories). It is a very common thing in relational databases, and there is a standard way to implement it. altexis described it in brief. What you really need to do is grab an introductory book on relational database structure. Once you know what "third normal form" is all about (and it's not as hard as it may sound) you should be okay.

mrsocks

Originally posted by swr

really need to do is grab an introductory book on relational database structure. Once you know what "third normal form" is all about (and it's not as hard as it may sound) you should be okay.

thanks for the advice.
that is pretty much what i was looking for. trying to learn it all at once and getting thins to work seems nice, but i want to make sure that it is correct.

any good relational database books? ( i will search, but just thought you may haev a suggestion)

take care.

swr

Most of what I know about data modeling I learned in an afternoon of watching someone who (mostly) knew what he was doing, as I described the application requirements to him. So I don't really know what books.

I've been googling looking for good tutorials, as I've met other people in your situation, and was there myself once (knowing SQL but not the basics for data modeling). I can't see any that I would describe as "good", but here are a couple of links that might be enough to make the lightbulb go off:

A phpbuilder.com article:
http://www.phpbuilder.com/columns/barry20000731.php3

A page on another site that might be worth looking at:
http://www.datamodel.org/NormalizationRules.html

That second link shows a very appropriate example. It starts with a "member list" table that has some data in a comma-seperated list, and then goes through normalizing it into a properly normalized many-to-many relationship (member/memberdb/database tables).

mrsocks

thank you very much.

have a happy new year.

mrsocks

ok,

i have read through the articles and they make sense. i understand that i should create the tables that hold the joining information and then search through that.

it seems as though i am close. i am able to add a new product. when i choose the many cateogries it can belong to, that catgory array is cycled through and then rows are inserted into the prods_to_cats table.

what i want to do now is be able to retrieve all the products from a certain category (and then order by brand and price)

i am unable to get the results to display.
here is my statement:

$sql = "SELECT
products.id,
products.title,
products.short_desc,
products.brand,
products.thumbnail,
products.srp,
products.price,
products.show_prices,
products.web_cart.
prods_to_cats.prod_id,
prods_to_cats.cat_id
FROM products, prods_to_cats
WHERE prods_to_cats.cat_id = '$id3'
AND prods_to_cats.prod_id = products.id
AND products.active = '1'";
// ORDER by 'brand' ASC, 'price' ASC limit $offset,$limit";

i want all the products that are assigned to the category id3.

i removed the ORDER BY for now to get it to work - but will want it incorporated.

from the article on phpbuilder, it looks as though i wouldnt even need to declare the prods_to)cats.cat_id or prod_id.

right now i am getting errors that are telling me that no results are created from this statement. (not 0, but "not a valid MySQL result resource")

this is the most complext statement i have tried to write and just want to know if i am even on the right track.

any adivce would be great. thanks.

mrsocks

woops, looks like i had a "." instead of a "," in one of the lines.

this seems to be working now.
thanks all for your help.

mrsocks

ok,
thank you all for the help.
i am now going to be using the above SQL statement and the table structures below.

##############
#

Table structure for table `cat_level3`

CREATE TABLE cat_level3 (
id3_cat int(11) NOT NULL auto_increment,
level1_cat varchar(150) NOT NULL default '0',
level2_cat varchar(75) default NULL,
level3_cat varchar(150) default NULL,
graphic varchar(255) default NULL,
html_graphic text,
active tinyint(1) NOT NULL default '1',
KEY id3_cat (id3_cat)
) TYPE=MyISAM;

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

Table structure for table `options`

CREATE TABLE options (
prod_id int(11) default NULL,
opt_choice varchar(100) default NULL,
opt_sku varchar(20) default NULL,
opt_price float(6,2) default NULL,
active smallint(6) default '1',
UNIQUE KEY opt_sku (opt_sku),
KEY prod_id (prod_id)
) TYPE=MyISAM;

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

Table structure for table `prods_to_cats`

CREATE TABLE prods_to_cats (
prod_id int(11) default NULL,
cat_id mediumint(11) default NULL,
KEY prod_id (prod_id,cat_id)
) TYPE=MyISAM;

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

Table structure for table `products`

CREATE TABLE products (
id int(11) NOT NULL auto_increment,
title varchar(255) default NULL,
short_desc text,
full_desc text,
thumbnail varchar(100) default NULL,
pic varchar(100) default NULL,
sku varchar(50) default NULL,
brand varchar(100) default NULL,
price float(6,2) default NULL,
show_prices tinyint(1) NOT NULL default '1',
srp float(6,2) default NULL,
weight varchar(20) default NULL,
ship_cost float(6,2) default NULL,
opt_label varchar(100) default NULL,
accessories text,
web_cart tinyint(1) NOT NULL default '1',
active tinyint(1) NOT NULL default '1',
UNIQUE KEY id (id),
UNIQUE KEY sku (sku)
) TYPE=MyISAM;

####################

does this all look correct? it seems as though the response is faster but i want to make sure that this is going to be the most effcient way.

thank you all.