How should I design database, where I have 30 users, each who needs full access to their data?

Question

I work for something like a consulting company. I have a web application that I built for one client. Now around 30 other clients want about the same product. The problem is, I don't know where to put the data. Most of the data is transactional, and all clients can fit into the current schema. The problem is, most of our clients are SQL fluent, and want to be able to access their tables. If I throw each client's data into the same table, I obviously won't be able to give anyone access to the table (the data is quite private and can't be shared). So I am trying to think out of the box.

Based on other answers here, it would not seem wise to give each client their own tables, each being about a replicate of the last. What about giving each client their own database? What pitfalls might come from that?

Answer 1

You should not give any client access to the SQL. Give them an API instead.

If you do want to give them access then depending on your database tech there are a number of solutions

• One database each.

  This is the easiest solution

• Row level access restrictions

• Duplicate tables into multiple schema and have schema level access restrictions

You also say:

the data is quite private and can't be shared

I would go with completely separate databases. This offers the greatest level of separation of the data and is the easiest approach from a access restriction standpoint.

The downside of course is that you have to have multiple databases and presumably multiple application instances, each pointing to its own database.

I wouldn't go with a multi-tenant app if you are splitting the database unless there is a specific reason as it is effectively providing a single point of access to all the databases and undermining the separation.

Answer 2

I'm not totally sure if/how this would work... but you could perhaps have one database, and one main set of tables, but then create unique views into each table for each user/instance. You'd only grant access to that users views, and the views would limit the data they return to only return that users data.

(Not a super useful answer, but I don't have the reputation to comment yet)

Answer 3

As others have suggested, I would create a separate database for each user, given what you say about the sensitivity of the information.

Also, would it make sense to give them read-only access, so they can't trash the contents of their DB with an erroneous SQL operation?