1

I'm a (self-taught) solo-developer trying to build my first full stack app. I can't really find much information about the topic, maybe I`m following the wrong path:

What I`ve achieved:

  • I created a CRUD app, with one MySql "user" linked table;
  • I created a login/registration system with dedicated "login" MySql table which once the user is logged in will redirect the user to the CRUD app page linked to the MySQL table and let the user perform all the CRUD operations;

What I want to achieve:

  • Every user has their own user table;

At the moment once the users are registered and logged in they can access to a only one common CRUD user table. So any user can modify the common CRUD user table. But what I want is that each user has his private CRUD user table.

The stack of the app is:SCSS; Node.js;Express.js;Handlebars;MySql

I willing to learn any other tool to go ahead with the app. Thanks for any roadmap/suggestions!

Cesare Mannino
  • 41
  • 9
  • why do you want each user to have its own mysql table? – J_K Oct 10 '21 at 09:33
  • Creating tables dynamically in sql is considered a bad practice. Usually you create a table per data type and associate it to a user via a field. – Rani Sharim Oct 10 '21 at 09:34
  • @J_K So the other users cannot modify the information inserted. So user 1 have access to project 1 but user 2 cannot have access to project 1, only to his own project for example project 2. – Cesare Mannino Oct 10 '21 at 09:39
  • 1
    @CesareMannino once a user is logged in, you can easily identify which user is performing actions. They would only be able to change their own data, not another user's data. Creating tabes for each user is bad practice. It doesn't make sense. – J_K Oct 10 '21 at 09:40
  • @RaniSharim, ok, so I will not create the table dinamically, thank you for the suggestion. You said `Usually you create a table per data type and associate it to a user via a field` Do you have any github repo or articles where I can find some information how to do that? – Cesare Mannino Oct 10 '21 at 09:44
  • Been a while since I've learned it, but you could google "mysql table design tutorial". This for example: https://www.lucidchart.com/pages/database-diagram/database-design i thing has a decent explanation. Specifically "Creating relationships between entities ". – Rani Sharim Oct 10 '21 at 09:52

2 Answers2

1
  1. You can implement data restriction using sessions.
  2. For each user to have a private CRUD table, you can implement that by writing SQL query to create a table for each user on registration.
Junta
  • 97
  • 2
  • 7
  • Thanks, `For each user to have a private CRUD table, you can implement that by writing SQL query to create a table for each user on registration.` I would like to follow this path. this is a connection query on my app for the table 'user': `connection.query('SELECT * FROM user WHERE status="active"', (err, rows) => {` Now if I add for every user a table it will have a different name. How I can perform the query then? Do you have a github repo/article. – Cesare Mannino Oct 10 '21 at 09:52
  • I don't have any article/blog at the moment but I can recommend a few resources that may help. I don't think you've to change your user table name, you can maintain and give the new table a different name i.e user_info. To achieve this, you can run multiple SQL statements (to insert in user table & also create a new table) in one query or even multiple queries at once then after you link/join those two tables to avoid redundancy. – Junta Oct 10 '21 at 11:47
  • https://www.ibm.com/docs/hu/qmf/11.1?topic=statements-including-multiple-sql-in-one-query – Junta Oct 10 '21 at 11:48
  • https://stackoverflow.com/questions/6212663/how-to-run-multiple-sql-queries/6212730 – Junta Oct 10 '21 at 11:48
  • https://www.codegrepper.com/code-examples/php/run+multiple+sql+queries+at+once+in+php – Junta Oct 10 '21 at 11:49
  • https://learnsql.com/blog/how-to-join-two-tables-in-sql/ – Junta Oct 10 '21 at 11:49
1

If you must use this table-per-user application design, you will do the following:

  1. create the user's table at the time each new user registers on your app. You can name the table using the user's autoincrementing id in the shared table containing the users.
  2. when a user logs into your site, you'll use that same table name in your code when you create the various SQL statements to drive your app.

But, this application design does not scale up well. MySql does ok with hundreds of tables on a single server, but not with thousands or tens of thousands. That's because each concurrently open table takes significant RAM, CPU, and IO resources. All those resources are expensive and limited. Please, consider an alternative design with shared tables with useridcolumns.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Hi @O.Jones I`m following the path of the shared table with the userid columns. I open another stackoverflow question: https://stackoverflow.com/questions/69608138/sharing-user-id-value-between-two-mysql-table. Thanks for taking time to answer my question. – Cesare Mannino Oct 18 '21 at 06:17