I am thinking about scaling issues with many-to-many join tables.

I have a constructed example as following:

  • Many users have many wishlists.
  • On every wishlist are many items.

If I scale this table to million of users the wishlist_items table will get very big:

  • 1000000 users
  • 100 wishlists for every user
  • around 1000-10000 items on every wishlist (bad example, but you get the point)

Therefore table size for the join table will be:

users * wishlist_per_user * items_per_wishlist

I think this is a very common situation in RDBM design. I am afraid that this users * wishlist_per_user * items_per_wishlist relation will grow out of bound.

Are there any similar experiences with such big tables and how to query them fast? How would I design my tables to prepare for better scaling?

  • 2
    You could partition the junction table on some logical bounds, although if the table is well-designed and has appropriate statistics and indexes, you'd be surprised how big you can make it and still have efficient access. – Max Vernon Feb 4 '16 at 12:47
  • 3
    To get meaningful answers you need to include the actual requirements not some construed example. – Max Vernon Feb 4 '16 at 12:50
  • 2
    Is it really your intention that one wishlist can "belong to" multiple users? Not one user per wishlist? – Daniel Hutmacher Feb 4 '16 at 13:19
  • 5
    Not to be nitpicking, but what you're doing is not many-to-many but rather one-to-many (one user, many wishlists; one wishlist, many items). Like @MaxVernon said, there are very powerful setups that will accommodate a huge number of records, but if you need specifics, please provide us with things like schema, what platform and version you're on, etc. – Daniel Hutmacher Feb 4 '16 at 13:56

I think using separate wishlists for separate users will be your best option. It will be efficient and will not contain millions of rows.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.