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.