How To Store User Purchases in MYSQL?

Question

So I'm learning php/mysql and I'm wondering how could I store what item a user purchased in a table. So far, I made a database called X. X has several tables which are:

(a) Users - This contains username, password and user_id(primary key)

(b) items - This contains item_name, item_price and item_qty

Now I have a problem. Let's say the user buys 5 items. How will I store this in the database? What's the approach to this problem?

Secondly. Let's say 5 users buy the same item, how do I store this in the database and again what is the best approach to this problem?

I'm very new to this so I need help.

Thanks :)

Answer 1

The best way to do this is to have 4 tables

TABLE `user` (
  `id`,
  `username`,
  `password`,
  ...
  PRIMARY KEY (`id`));

TABLE `product` (
  `id`,
  `name`,
  `price`,
  ...
  PRIMARY KEY (`id`));   

TABLE `order` (
  `id`,
  `user_id`,
  `time`,
  ...
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `user`(`id`));   

TABLE `order_product` (
  `order_id`,
  `product_id`,
  `quantity`,
  ...
  PRIMARY KEY (`order_id`,`product_id`),
  FOREIGN KEY (`product_id`) REFERENCES `product` (`id`),
  FOREIGN KEY (`order_id`) REFERENCES `order` (`id`));

The order_product is a junction table to establish a many-to-many relationship between the order and product table.

That's it.

Answer 2

One method (w/o adding more tables) is to add a foreign key to the items table for the user_id of the purchaser. Note, set this field as an index.

Here's some examples:
User 101 buys item A, quantity 1
User 101 buys item B, quanty 2
User 102 buys item A, quantity 5

Items Table
item_name item_price item_qty user_id
A $1 1 101
B $2 2 101
A $1 5 102

Another (my preference) is to create a third table for **purchases** and use the **items** table only for listing unique instances of items, and add a primary key (**item_id**) to the **items** table.

Items
item_id (primary key)
item_name

Purchases
purchase_id (primary key)
item_id (foreign key)
item_qty
item_price
date_of_purchase

Answer 3

You could simply have an Orders table which links an Item (and quantity) to a User.

Orders - 
  user_id (PK, FK -> Users Table)
  item_id (PK, FK -> Items table)
  quantity
  time (PK)

---

If you additionally wanted to track transactions you could have another table like this:

Transactions -
  transaction_id (PK, FK -> Transaction table)
  user_id (FK -> Users table)
  time

And the Orders table would be a little simpler:

Orders - 
  transaction_id (PK)
  item_id (PK, FK -> Items Table)
  quantity

Example:

If 5 users by the same item, in the first method:

Orders:

user_id | item_id | quantity | time
1       |  99     |    1     |  31/10/2013 01:01:01
1       |  105    |    2     |  31/10/2013 01:01:01
2       |  99     |    1     |  31/10/2013 01:01:06
3       |  99     |    1     |  31/10/2013 01:03:00
4       |  99     |    1     |  31/10/2013 03:26:09
5       |  99     |    1     |  31/10/2013 04:11:16

If 5 users by the same item, in the second method:

Orders:

transaction_id | item_id | quantity
       123     |   99    |    1
       123     |   105   |    2
       124     |   99    |    1
       125     |   99    |    1
       126     |   99    |    1
       127     |   99    |    1

Transactions:

transaction_id | user_id | time
       123     |   1     |   31/10/2013 01:01:01
       124     |   2     |   31/10/2013 01:01:06
       125     |   3     |   31/10/2013 01:03:00
       126     |   4     |   31/10/2013 03:26:09
       127     |   5     |   31/10/2013 04:11:16

And finally in answer to your second question:

Both of these table designs would allow multiple users to purchase the same item without any problems. In the first example the Orders table has a compound Primary Key of PK(user_id, item_id, time_stamp) which allows for different users to buy the same item, even at the same time. In the second example the Orders table has a compound Primary Key of PK(transaction_id, item_id), and the Transactions table has a Primary Key of PK(transaction_id) which is always unique (probably generated from a MySQL Sequence).

Answer 4

you need an another table that make relationship between these table (Users,items). I called it an order_tbl that store users_id,items_id,item_quantity,time_stamp. Time_stamp is used for time value (you doesn't need this now, but it help you near future).

Answer 5

A junction table.

Probably containing something like:

• user_id
• product_id
• timestamp
• quantity

You might want to replace user_id and timestamp with another foreign key on an orders table.