1

I am trying to design a database where each user can save up to ten lists of cards. Each list could theoretically have tens of thousands of items, but will likely only have hundreds.

Should I create a new table for each user? I feel like this would make the database really hard to manage.

Alternatively, if I just have one table, it could have 10*number of cards(tens of thousands)*number of users of entries, which could be millions. And if each user is adding to and querying the table very frequently, it seems like that could get very bloated.

Maybe it is obvious, but I am really new to database and relational design, and I need something that could scale to quite a lot of users (likely tens of thousands).

I have looked at several different posts, and the theme I am noticing seems to be to just have one table, but it is likely that this table would have millions of entries, and possibly close to a billion, and it would be queried very frequently.

If there is another way of designing this, besides one table for each user/one table for all users, I haven't found that either, but I am open to suggestions.

1
  • 5
    Most modern relational databases can handle millions of rows without a problem, assuming you know the basics of indexing and such. The reason you are seeing examples with a single table is because that is definitely the easiest way to get moving, and more than likely you won't out-grow that approach. And if you do then CONGRATS! Now you have the good kind of problem (ie, too many sales, not too few). If you keep your design simple up front, you'll be able to change it later when you know for sure what your actual problems are. – Graham Oct 13 '20 at 20:17
0

Every piece of software/hardware has a limit. I think you can come up with a schema to store users, lists, and items, that is not the problem.

The second part is how to scale, which is the real problem. There are two ways to scale, horizontally and vertically. In this case, horizontal scaling is probably the best choice.

For example, one could split the users by region or some other attribute. As part of your system, storing some attributes and using those attributes to partition data/resources allows your system to grow over time. As more users enter the system more resources are allocated.

This allows to test the system and set a limit. Maybe based on hardware only 10,000 users per server are allowed based on your performance metrics.

So, when user 10,001 is added a new instance is started up. Then you have proven performance and can scale infinitely.

Same schema across all instances, but load is divided. There is an additional part of system decides where the user's data lives and how to get it (routing).

0

Do not create a table for each user!

Probably you need something like the following tables

  • Users
  • Users Lists
  • Cards
  • Cards to Lists (if a card can be on multiple lists)

Hard to know without understanding your requirements.

You should not really run into performance issues but you'd start to look at partitioning. Again, without knowing, partition by user seems reasonable.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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