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?

Having numbers in column names is always an indicator of a bad design. — juergen d, Jan 22 '17 at 21:52
I've heard good things about the book, Database Design for Mere Mortals. I don't share your so far, so good opinion. — Dan Bracuk, Jan 22 '17 at 21:56
When tempted to have a array of values splayed across columns, tempt yourself to make another table with one item per _row_. — Rick James, Jan 23 '17 at 00:39

score 1 · Accepted Answer · answered Jan 22 '17 at 22:32

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

score 0 · Answer 2 · answered Jan 22 '17 at 23:59

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

Is it possible to change the quantity_table to have only two columns : id_quant,quantity? — Khlifi Hatem, Jan 23 '17 at 00:04

Poor MySQL database design?

2 Answers2

Table Equipment