-3

PIM (product information manager) Database design query

Lets presume 1000 users with 100,000 products per user. Each user needs to store their own 100k products in the database which can be modified independantly by each user (non shared data)

Looking for the best way to store this data in the mysql table, taking into account that is expandable / future proof should the user base significantly increase to say 10,000 users Also taking into account speed / corruption

At the moment i'm thinking of creating independent stock tables with the user_id added to table name e.g. stock_1001, stock_1002 (where 1001 + 1002 are the user id's)

Another idea was to create complete seperate databases for each user e.g. db_1001, db_1002, but i'm thinking this might be slow / bad design as the rest of the database contains other tables such as users / permissions / settings for the main application so getting getting seperate databases to talk to each other may be poor design / slow?

Does independent stock tables for each user sound the best design method?

Shadow
  • 33,525
  • 10
  • 51
  • 64
just_testing_magento
  • 15
  • 5
  • 'can be modified independantly for each user ' OR can be modified independantly BY EACH user ? 'so getting getting seperate databases to talk to each other may be poor design / slow?' why would you want to do this when no user can access another's data – P.Salmon Aug 24 '23 at 11:05
  • @P.Salmon each user can modify their own data. As for seperates not something i would do unless people with more experience advise is best. I'm putting suggestions on the table and will eliminate as necessary – just_testing_magento Aug 24 '23 at 11:10
  • Please post SHOW CREATE TABLE table_name; as currently designed so we have some idea the scope of data to be stored for your users. What is expected lifetime of the one user's data? In days/weeks/years that a user will CARE about this data? Is user LIMITED to 100,000 MAX allowed rows? – Wilson Hauck Aug 24 '23 at 14:12

0 Answers0