Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

ID |TYPE|

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?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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:

product
-------
  product_id
  name

product_attribute_type
----------------------
  attribute_type_id
  attribute_type_name

product_attribute
-----------------
  product_attribute_id
  attribute_type_id (FK to product_attribute_type)
  product_id (FK to product)
  value

Then you can have data that looks like this

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

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

product_attribute
-----------------
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;
share|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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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