I am setting up a database using mysql to store different products with different product types.

For example I could have Books, Software and clothes etc.

What I am think of is creating a parent table (product_table with the structure:


Then Having a TYPE table which would be:

ID |TYPE |Product_ identifier_Table

So then the Product_ identifier_Table would contain the name of the table the which contains the product details.

Would this be the correct way of setting up the database?


I'm not sure why you need different tables for different product details. I'm going to guess though, that it's because your product types are so different that they have very different attributes (clothing will have sizes: S, M, L whereas books will have have an indicator of hardcover vs. paperback, etc...). You might want to try a more generic solution, where you have a table of product attribute types and another table of attributes. Something like this:



  attribute_type_id (FK to product_attribute_type)
  product_id (FK to product)

Then you can have data that looks like this

ID | name
1  | Awesome pants
2  | Book of cool stuff
3  | 1337 software

ID | attribute_type_name
1  | waist size
2  | number of pages
3  | system requirements
4  | inseam

attribute_type_id | product_id | value
1                 | 1          | 33 inches
4                 | 1          | 34 inches
3                 | 3          | Win7, 4 GB RAM
2                 | 2          | 703 pages

Getting info on a product is easy:

select product.name, product_attribute.value, product_attribute_type.name
from product
inner join product_attribute on product.product_id = product_attribute.product_id
inner join product_attribute_type on product_attribute_type.attribute_type_id = product_attribute.attribute_type_id
where product.product_id = $PRODUCT_ID;
| improve this answer | |
  • thanks, That does seem like an effective method there will be other products added to the database at later dates and other types but that can be just added into the tables im guessing. Also the books will have authors, ISBN10s and 13s which is another thing to consider but this is a good starting point for me – user1714133 Jan 17 '13 at 17:04
  • @user1714133: You can add as much as you want to product_attribute_type, I kept it small for the sake of the example. – FrustratedWithFormsDesigner Jan 17 '13 at 17:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.