Creating separate table for each user in Database Designer

Tagged:
  • Chris Bouveret
        Has successfully completed the online course Introduction
        Has successfully completed the online course Intermediate (200)
        Has successfully completed the online course Advanced (300)
        Has successfully completed the online course Basics (100)
        Has successfully completed the online course Advanced (320)
        Has successfully completed the Intermediate Certification
        Has successfully completed the Advanced Certification
      3 years ago #28383
      Up
      0
      Down
      ::

      Hi Nicole,

       

      you can do this, by using mostly SQL statements.

       

      Before I tell you how I just want to mention that this isn’t the best practice in SQL Database design. When you create one table for each user, you can end up with a large number of tables, depending on how many users use your application. Typically, we would suggest to have one table for all users and a second table with the detailed information of the users. They would be linked via a foreign key, so you know which detailed information belongs to which user.

       

      Since I don’t know your use case in detail, maybe separate tables are an effective way of handling your use case. So here is how you would create a table for each user:

       

      You can create new tables directly from the running app with a connector call or a business object function.

      As attachment you can find an example how a connector call could look like.

      As you can see in the picture, the create statement uses CREATE TABLE IF NOT EXISTS. So, it will only create a table if no table with this name exists.

       

      The table name would be variable and be given to the connector call via the input parameters. The name should be created for each user by the same pattern. When creating the pattern you should take in consideration, that German Names have often characters which are not supported in table names. Since you don’t want your pattern to be visible in the client, I would suggest to implement this logic of creating the name in a serverside business object.

       

      To get some user information you can use Simplifier.User.getCurrentUser(). With this you get Information from the logged in user, like the email address. Here you find the documentation of this method.

      After you created a table name you can trigger the connector call with this name directly inside the business object.

       

      This business object should be triggered after the user has finished the login into your application.

      Attachments:
      You must be logged in to view attached files.
    Viewing 2 posts - 1 through 2 (of 2 total)

    You must be logged in to reply to this topic.