Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    10

    Cross Reference Design Problem

    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
    Last edited by tanra; 10-19-11 at 10:54.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 )
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2011
    Posts
    10
    Hi,

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

    Thanks

    Quote Originally Posted by r937 View Post
    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 )
    );

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2011
    Posts
    10
    Thanks a lot!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •