Poor MySQL database design?

Question

I'm creating a database for a browser game. So far I was doing quite well (I guess) until I decided to make items stackable. Therefore, I created "quantity_table". This is my simplified database:

My goal is to get data of an item and quantity in one query. I tried something like this:

SELECT items.*, quantity_table.quantity1 FROM items JOIN quantity_table WHERE id_item = 3 AND id_quant = 1 

Result:

It kinda worked... but if I select item no. 2 the last column would be quantity2. And I don't need quantityX columns, but something universal, like 'quantity'. So query is not the problem, but database itself. But I'm STUCK for good with this! Really do not know how to solve this problem. Could anybody help me?

Answer 1

What about creating association table item_quantity?

CREATE TABLE item_quantity(
id_item INT NOT NULL,
id_quant INT NOT NULL,
CONSTRAINT id_item_quantity PRIMARY KEY (id_item,id_quant),
CONSTRAINT fk_id_item FOREIGN KEY (id_item)
    REFERENCES items(id_item),
CONSTRAINT fk_id_quant FOREIGN KEY (id_quant)
    REFERENCES quantity_table(id_quant));

Then you can select informations using item_quantity table like :

    SELECT items.id_item,items.name,quantity_table.quantity FROM item_quantity Inner Join items ON item_quantity.fk_id_item = items.id_item Inner Join quantity_table ON item_quantity.fk_id_quant = quantity_table.id_quant

Answer 2

Databases don't have the concept of lists like most programming languages. Any time you need a many relationship, it has to be a table.

Using that concept here, your equipment table should be like:

Table Equipment

• id_acc: int(10)
• id_item: smallint(5)
• qty: tinyint(3)

To get all items for user 1, do something like:

select i.id_item as item_id, name as item_name, type as item_type, qty as item_qty
from accounts as a 
inner join equipment as e 
  on a.id_acc = e.id_acc 
inner join items as i
  on e.id_item = i.id_item
where a.id_acc = 1