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?

closed as too broad by LowlyDBA, mustaccio, RDFozz, hot2use, McNets Oct 24 '17 at 8:20

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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.

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