3

I have a database to build based on inventory for my shop. This is the current data that I have put into tables. This is the package:

the package

This is the product attribute:

attributes

This is the table to produce

inventory list

The problem is, each product has different packages. The data shown is the product that has 2 packages, "color" and "power".

So the question is: how to do "color" and "power" as column on the database?
and what will happen if it has more than 2 packages?

I am using MySQL for this.

What I'm going to do is to track the quantity, 

product-23 : white, 100 = 30qty  
product-23 : white, 50 = 30qty  
product-23 : black, 100 = 30qty

What I have is 2 tables "package" and "values", includes already have tables like product that has product id.

So whenever one user is trying to purchase product-23 : white, 100, it will check the quantity on the database.

Here is the fiddle

Improve this question

1 Answer 1

Reset to default
1

You have basically created a basic Key Value Store. See here for more details on that. You will need to make use of the PIVOT command to get the result you need. Check here for that.

Improve this answer
3
  • does it means that i have to create the "joined tables" first to do the PIVOT ?, i mean, does the initial data should be the "all combined data" first or it should be like the one "package" and "values" on my question?, sorry I'm quite new in db-design
    – faiz
    May 17, 2014 at 15:53
  • 1
    OK I thought you were describing the tables you had already designed. Database design is very important and difficult to change later on. You want to get this right from the outset. I recommend you start here: ntu.edu.sg/home/ehchua/programming/sql/… and then research 3rd normal form. I wouldn't go too in depth because what you're looking to do seems quite simple but the more you know the better it will be.
    – James Anderson
    May 17, 2014 at 15:58
  • i already normalize the raw data into the database. its just that I'm having trouble on how to create/handle the db-table for the "stock"/"qty"
    – faiz
    May 17, 2014 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.