CODEWITHSUNDEEP
database-design
finspin
finspin

Reputation: 4061

How to keep track of bought items?

I want to keep track of how many times a user bought certain item. My item table currently looks like this:

ID  |  NAME  |  USER_ID  |
1   |  bread |  1        |
2   |  milk  |  1        |
3   |  bread |  2        |

What would be the recommended way of keeping track of bought items? Do I simply add a counter column and increase the value every time the item is bought?

ID  |  NAME  |  USER_ID  |  COUNTER  |
1   |  bread |  1        |  5        |
2   |  milk  |  1        |  1        |
3   |  bread |  2        |  3        |

Upvotes: 0

Views: 839

Answers (4)

Umesh .A Bhat
Umesh .A Bhat

Reputation: 587

I would recommend inserting new rows as more products are purchased rather than updating the counters of the products.

PID  |  NAME  | CREATED_ON
1    |  bread | 05-12-2012 9:45 AM
2    |  milk  | 05-12-2012 11:09 AM


ID  |  PID  |  USER_ID  | CREATED_ON
1   |  1    |   1       | 31-12-2012 10:05 AM
2   |  2    |   1       | 31-12-2012 10:05 AM
3   |  1    |   2       | 31-12-2012 11:05 AM

Why ?

  1. You can always sum upto a particular date (via filters) to the get count / implement a view to do the same.

  2. It is better to insert a row rather than update existing count, both performance and transaction wise.

  3. Finally, It is recommended you add a CREATED_ON column (default GETDATE()) as a record timestamp.

Upvotes: 2

Luigi
Luigi

Reputation: 8847

I would have 3 tables:

create table users (
  id number primary key,
  name varchar
)

create table items (
  id number primary key,
  description varchar,
  price double
)

and the 3rd table would be:

create table purchases (
  id number primary key,
  user_id number,
  item_id number,
  date timestamp, 
  foreign key user_id references users(id),
  foreign key item_id references items(id)
)

so you can check how many times a user bought an item by

select count(*) from purchases where user_id = 123 and item_id = 456;

Upvotes: 0

Kim Hansson
Kim Hansson

Reputation: 501

I would consider spliting this into two tables: item and purchase

item:

ID  |  NAME  | 
1   |  bread | 
2   |  milk  |

purchase:

ID  |  ITEM_ID  |  USER_ID  | 
1   |    1      |     1     |
2   |    2      |     1     | 
3   |    1      |     2     |

And as for the number of purchases of each item by a specific user (to find number of "milks" bought by user # 1):

select count(*) from purchase where USER_ID = 1 and ITEM_ID = 2

Upvotes: 0

Matti Virkkunen
Matti Virkkunen

Reputation: 65126

If all you ever need to know is the count, then by all means do that. Some applications might require another table listing each "purchase", for instance to be able to tell when each item was bought (in case the user buys the same item multiple times).

Also shouldn't that table rather be named user_item or something? The name item rather makes me think of a table that has information for each type of item (bread, milk) and has nothing to do with users.

Upvotes: 1

Related Questions