Cross Reference Design Problem

tanra · #1 (**permalink**) 10-19-11, 09:49

I need help in databse design. It sounded very simple but somehow I am not able to come up with a good solution.

In database, we have only two items, user-id and item-id. The only goal is that we should be quickly able to query:

1) Given the user-id, the list of item-id which user has purchased
2) Given the item-id, the list of use-ids who purchased this item

There are more than million unique userids and more than million unique item-ids. Each user might be owning thousands of items. The brute-force approach would be to have duplicate rows but datasize will be too big in that case and I am hoping that someone will point me to a better design.

We use MySQL.

Thanks

r937 · #2 (**permalink**) 10-19-11, 09:59

perhaps you neglected to declare your indexes?

Code:

CREATE TABLE user_items
( user_id INTEGER NOT NULL
, item_id INTEGER NOT NULL 
, FOREIGN KEY ( user_id ) REFERENCES users ( user_id )
, FOREIGN KEY ( item_id ) REFERENCES items ( item_id )
, PRIMARY KEY ( user_id, item_id )
, INDEX item_users ( item_id, user_id )
);

tanra · #3 (**permalink**) 10-19-11, 10:09

Hi,

Can you please explain it a bit more, how the query will look like.

Thanks

Quote:

Originally Posted by r937

perhaps you neglected to declare your indexes?

Code:

CREATE TABLE user_items
( user_id INTEGER NOT NULL
, item_id INTEGER NOT NULL 
, FOREIGN KEY ( user_id ) REFERENCES users ( user_id )
, FOREIGN KEY ( item_id ) REFERENCES items ( item_id )
, PRIMARY KEY ( user_id, item_id )
, INDEX item_users ( item_id, user_id )
);

r937 · #4 (**permalink**) 10-19-11, 10:20

1) Given the user-id, the list of item-id which user has purchased

Code:

SELECT item_id
  FROM user_items
 WHERE user_id = 937

2) Given the item-id, the list of use-ids who purchased this item

Code:

SELECT user_id
  FROM user_items
 WHERE item_id = 42

tanra · #5 (**permalink**) 10-26-11, 01:18

Thanks a lot!!

Internet Services

Web Development

Digital Marketing

Consumer Tech