Best structure for product db

I have a new web client selling products online. The products come from 20 or so different suppliers (brands), each of which has (at most) 20 variations on their product (models). So far so good. Each model (across all suppliers) comes in a range of size dimensions where dimension A is one-to-many for B and (A & B) are one-to-many for C.

For example, if the product were window frames, A would be width, B height and C colour, so each supplier would offer X number of frame types, each of which is available in a variation of A widths, B heights and C colours. So there might be 10 different frame styles from one supplier and one style might come in 5 different heights, each of which is available in 7 different widths but for which the colour choices vary. For example, a 500cm x 1200cm frame might be available in white, brown or beige but the 500cm x 1500cm is only available in white.

If you're still with me, what would be the best way to build a MySQL db that:

a) allows for easy, efficient retrieval for display on product pages,
b) allows for easy, efficient reporting (how many white frames did they sell, how many 500cm wide ones) and
c) is easy for the site owner (non-web savvy) to create and adminster the product range without a nightmare set of selections.

The range of sizes isn't huge so numbers shouldn't be a problem - I just can't think of the best way to organise it.

Any advice or comments welcome.

Jon

 

this is like creating a oop design in db tables.

i suggest that every major attribute should have a separate tables.

ie.

product_sizes table
product_available colors
product_category
product_country_restriction

then under your main product table, there must be each column for each of the attribute..

 

Hello

bartolay13 is right & Now am just explaining the things

First of all we need to understand that

The size of the product will be saved into Width X Height format into a single field(column) of the table. We can store Width & Height into separate columns, but there is no need because this will make the things messy every time when you will code & make queries. Avoid this.

Now how to design tables

Product Table

product_id (Primary Key)
product_name
brand_id (Foreign Key)
supplier_id (Foreign Key)
size_id (Foreign Key)
color_id (Foreign Key)
quantity

Brand Table (Same for Supplier Table, just change the name of the table & fields)

brand_id (Primary Key)
brand_name


Size Table

size_id (Primary Key)
product_id (Foreign Key)
size


Color Table

color_id (Primary Key)
product_id (Foreign Key)
color

Now when you need to enter the product into database the process is as follows

Process:

1. On form load you will retrieve data from all tables(which are related to product) for all the fields you will use to enter the product details.

2. After feeding the fields of form with product information, Insert data into all tables(which are related to product) using single query. This step will depends upon your coding.

When you need to retrieve the Number of products for any particular size & color then you will run query something like this:

SELECT p.quantity FROM products_table AS p, size_table AS s, color_table AS c WHERE p.product_id = s.product_id AND p.product_id = c.product_id;

In this way you can preform as many as operations on these tables

Thanks,

Jimmy