How do i store repetition of item in table?

Question

I have three tables user table

CREATE TABLE IF NOT EXISTS `user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL,
   PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8_unicode_ci;

item table

CREATE TABLE IF NOT EXISTS `item` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `item` varchar(20) NOT NULL,
  `price` int(3) DEFAULT NULL,
  `likes` int(4) DEFAULT NULL,
   PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8_unicode_ci;

order table

CREATE TABLE IF NOT EXISTS `order` (
  `user_id` int(11) DEFAULT NULL,
  `order_no` varchar(50) DEFAULT NULL,
  `item1` varchar(5) DEFAULT NULL,
  `item2` varchar(5) DEFAULT NULL,
  `item3` varchar(5) DEFAULT NULL,
  `item4` varchar(5) DEFAULT NULL,
  `item5` varchar(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8_unicode_ci;

I store item_id in item1 or item2 so on column in order table. i give only 5 items can be select by user but they can increase quantity of any item like any user order two quantity of item1 how do i store this on database .

and any suggestion for improvement in tables .

See [Junction Tables](http://stackoverflow.com/a/32620163) – Drew Sep 11 '16 at 22:45 — Drew, Sep 11 '16 at 22:45

Bill Karwin · Accepted Answer · 2016-09-12T02:08:43.760

The best way to allow an unlimited number of items per order is to use another table:

CREATE TABLE IF NOT EXISTS `orderitem` (
  `order_no` varchar(50),
  `itemid` INT,
  `qty` INT NOT NULL DEFAULT 1,
  `price` NUMERIC(9,2) NOT NULL,
  PRIMARY KEY (order_no, itemid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8_unicode_ci;

Remove the five item columns from your order table.

As the user adds items, INSERT to the table above. You therefore have no limit to the number of items for each order.

This design is necessary whenever you have a many-to-many relationship. It's not a good idea to use five columns, that creates a limitation that is inconvenient (as you know).

If you want to allow the user to increase the quantity of a given line item, add a qty column to this table. When they choose to add more quantity of the same item they had chosen before, increase the qty.

You will also need a price column in this table. You need to record the price paid for the item during the current order, because the price could change tomorrow.

Re your comment:

INSERT INTO orderitem (order_no, itemid, qty, price)
VALUES (1234, 42, 3, 19.95)
ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty), price = VALUES(price);

Read the documentation about http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html to understand what this does.

Thanks sir , also tell me that if user select one item but quantity is five how to store this — gaurav, Sep 11 '16 at 18:20
Please explain to me if user ordered two items one have 3 quantities other have one so when i Insert in table it create two row with same order no . is that process — gaurav, Sep 11 '16 at 18:53

score 0 · Answer 2 · answered Sep 11 '16 at 14:23

0

One suggestion would be not to define your own id field, unless you have a very specific reason for it. Most databases can keep track of that for you. If you want to define your own you should probably add a unique constraint, and/or auto increment it.

I might not understand the question but it sounds like you want to be able to change the values in your items? I'm not quite sure what you're using but here's sql documentation

answered Sep 11 '16 at 14:23

Joey Wood

273
1
10

suppose user order 3 quantity of one item so i store in item_id of item in `item1` column but where should i store that quantity is 3 because i store only item_id not quantity – gaurav Sep 11 '16 at 14:28
are there only five items to choose from or can they at max choose 5 items? – Joey Wood Sep 11 '16 at 17:11
they can choose max 5 items – gaurav Sep 11 '16 at 18:04
I would use the table from the other answer with a few modifications. Itemid and order_no should be foreign keys that reference the oder_no and id from the other tables – Joey Wood Sep 11 '16 at 21:48

How do i store repetition of item in table?

2 Answers2