Table structure for a product database

[rudenborg]

Hi!

I'm working on a website for my business and have a few questions about how best to design the MySQL database.

First of all we have products (art prints) that have lots of options, and the user has to be able to select which one he wants for the particular print. So we'll have one basic art print but it can have up to 21 permutations (example: blue mat gold frame, red mat gold frame, green mat gold frame, etc.). The other thing is that each item may have different quantities and different values of permutations. Basically, some of the items would have the same permutations but other wouldn’t follow any pattern at all. We have less than 150 prints but with the different permutations we have somewhere around 2500 different items. I would like to have just the prints be items instead of having 2500 rows in the product’s table.

My question is: how would I best store these values (permutations) in a table so they could relate back to the products table? I would like to have a page where I could select an item, and be able to have it load, from the database, the options for that print in a select box. So I would have one php script that could view any item.

I hope this makes sense!

Thanks,

Jonathan

 

[sleipnir214]

This sounds like a good place for the classic three-table schema....

One table will store prints. It will contain columns for a description of the print, etc. It must include some kind of unique id column, for which an auto_increment column is probably best.

The second table will store options -- matting options, frame options, etc. It must also have a unique ID column.

The third table will merely describe the relationships between items in the other two tables. It may have only two columns -- one to store the ID of a print, one to hold the ID of an option.





Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[rudenborg]

Thanks for the help! Your solution makes a lot of sense.

I do have another question though. most of the prints will have several options, up to a max of 21, so this means that print_id column in the third table will have a lot of repeates.

print_id option_id
1 2
1 3
1 4
1 5
etc.

is this bad? is there a good way to eliminate this? Or was I missing something in your orignial explanation?

Thanks again for you time!

Jonathan

 

[sleipnir214]

This is perfectly normal. Any given print_id or option_id value can an probably will be duplicated.

What you don't want to see is any given (print_id, option_id) 2-tuple (an entire record) to be duplicated.





Want the best answers? Ask the best questions!

TANSTAAFL!!