Database Design Tips

[NewEcommerce1]

I am using MAMP on a Mac for development purposes. It has:

Apache 2.2.21

MySQL 5.5.9

PHP 5.3.6

PHPMyadmin 3.3.9.2

 

In looking at the database design, I decided to only use categories, products and sales tables as that was all that was really needed.

 

There are several sizes for some of the products but only a small or large version. (say 2oz or 8oz)

I also added a few description fields to the product like contents, instructions and a small and large image as well as a short and long description so I can display a short version in the listing and a long version in the product description/detail page.

 

For the two different sizes on some of the products I was simply going to title it small or large after the product name and have a size field within the product itself as well indicating its size.

 

The product_type field seemed to be not needed so I took it out. Each product has an id and a category id. This works well - I have each of the categories as a link and the redirect works fine for listing all the products in a category.

Click on a product in the category listing view and it pulls up the individual product by its id. I used "sales.php" as the category listing and modified the query appropriately.

I used "browse.php" for the individual listing and modified its query to only display the individual product. From there there is the link to add to cart. I modified the HTML/CSS views as well.

 

What I would like to do however, is when the individual product is pulled up, if there is a small and a large size, display both small and large link on the product listing - a link to add the small and a link to add the large. Display both so the user can add either one.

 

Unless I put in another field into the products table that indicates that there is another size available - a logical field that indicates that and then a query to pull up the other one (say matching descriptions) - or

go with a separate size database that lists size id and the product id, size and price. For the products that have multiple sizes there would be multiple entries that would match that product id. I would guess that the price would have to go into the size table as well. For products that only had a single size there would be only one entry in the size database that matched that product id.

 

When adding to the cart, the product id would have to have a size id and thus would have a price. The sku would be modified to include a product id and a size id when adding to the cart.

The other one is simpler in that I would only need the product id.

 

Does this approach make sense? Anything I am missing?

 

Thanks.

[Larry]

If I understand you correctly, if ALL products only had 1 or 2 sizes and would only EVER have 1 or 2 sizes, having the two size options in the products table is a reasonable choice. It is restrictive and it's not normalized and you'll need to include two columns for the stock, but it's an easier solution, certainly.