CODEWITHSUNDEEP
databasepostgresqlsecuritymulti-tenant
G. Oliveira
G. Oliveira

Reputation: 38

Should I use a single database or a database per user in this scenario?

I am designing an inventory control web-app where, initially, I was going to use a single database per customer/client. I have now the information that some clients will want some customization, so they might want some specific information that my base application might not have. On top of that, some customers might, in the future, want this app to be stored locally in their store/commerce server, so they might need all their information. I don't think that performance will be a problem if I use a single database, at least not with a few customers. Using a database per user however, it might complicate things performance wise when considering connection pools.

So what might be the best approach here? I will provide more information if needed.

Also, if I used a database per user, how will I manage connnection strings? If a user logs in, how would my back-end know that this user has to access a specific database? Would I store the connection string in a database? If so, would't this be a security issue?

I am planning on using postgresql and node.js for my back-end..

Thank you guys!

Upvotes: 0

Views: 208

Answers (1)

Edward Romero
Edward Romero

Reputation: 3106

I see two problems that you are trying to solve

  1. How can my app be developed in such a way that any user can host it in their own environment

    You should use one db for this case scenario so that you can make sure it's easily portable

  2. How can my app support multiple users on a centralized host

    You should still use one db but figure out how to create different ways of storing the data for which to join on a per-customer unique id basis. This will get big and complicated though depending on how different the data is per customer. Also, your app will need to handle this dynamic state, so the problem is not just choosing db but how to orchestrate the dynamic data.

Overall I wouldn't get into juggling connection strings on a per-user basis, but instead, keep a group of customers in the basic setup (static predetermined models), and figure out how to handle the complex customers (dynamic models) as you get them. Eventually, once you have a lot of sample cases, then you can slowly move to develop a bigger app that can handle all of the different cases. Trying to design an app to handle dynamic data that you don't have yet is not trivial.

Upvotes: 1

Related Questions