Anvil Community Forum

Database Table Management

What I’m trying to do:
I am trying to create a dynamic management app whereby the main database can be used by 1…m users who manage a Client. Should a new Client be added, then this would in effect require a new table to be created dynamically so that a user-journey whereby user creates a new Client and therefore the Client info is kept in a seperate table to different Clients. The purpose for this is to segregate client data so that we can apply encryption for compliance reasons and therefore use seperate keys per client. Currently this feature to create new tables to the existing DB doesn’t seem evident?

What I’ve tried and what’s not working:
Search all of the Documentation and here on the forum so asking out load on a suggestive approach to my problem?

I’m confused as to why separate tables are needed. If the goal is to use separate encryption keys for each client, that can be done with a single table that has a client field to tell you which client the row belongs to. The row data will decrypt only with that client’s key.

Is there a regulatory need for separate tables?

There is a feature request for being able to create data tables with code ( Create Data Tables with Code - #11 by splinter ), but as far as I know there’s no way to do it yet.

1 Like

Hi @jshaffstall, specifically the requirement for me is the compliance to have seperate tables that contain unique info per Client and segregated in this fashion. Thanks for pointing out the FR, for now I can develop using a single table with a field representing the Client.

Welcome to the Forum, @contactus!

Anvil has an additional feature that can help keep your users’ data separate. Even though they share a table, it doesn’t have to look that way to your browser-side code. See Views.

1 Like

One solution, viable only if m is very low and the clients are added manually (no online registration) is to create one environment per client, each with its own database, or to clone the original app. Both are better than creating the new table manually, because it requires no code editing, but both do require your manual intervention.

I understand that often developers need to comply rather than argue with requirements coming from the clients, but this is not black and white, we are in a gray area.

  1. Your app has access to all the tables, so they are not really separated
  2. Telling your app to access one client’s data by providing the name of the table rather than the client id for the all-clients table doesn’t make the app more secure
  3. A table is a way to organize data stored in a database, just like the views mentioned by @p.colbert
  4. Anvil tables are under the hood views of one single table, so tables don’t really exist
1 Like