If you have different database per customer as there is no need to share any data between them does the same apply if a customer has multiple stores ?
I believe that it is easier to do queries for statistics (some require 4-6 table join) if they are at a single schema rather than multiple schemas.
But everyone (haven't done anything yet) in team believes that cross schema queries is the way to go.They claim that postgres supports it well. Which is the way to go?

  • 1
    You give too little detail to be sure, but the idea of an "additional column per customer" is almost always very wrong. As a rule, your database schema shouldn't have to change just because new entities enter the system. See programmers.stackexchange.com/questions/218974, just asked yesterday. – Kilian Foth Nov 21 '13 at 8:56
  • Hm I didnt mean that. I mean if you customer Kilian Foth , has 10 stores that you can have a single database for your 10 stores and a single column to identify the store. But other customer X have different database – GorillaApe Nov 21 '13 at 10:44

Separating clients by database makes sense: security, uniquely manage backups/restores, customizations.

Having each location under a separate schema would give you ways to: group objects, security and possibly manage separate backups, but unless you see no other way to achieve this separation, I think it makes querying and maintenance unecessarily difficult.

It seems easier to have locations managed by the data. You can always handle record selection security by having a security table indicating which location(s) a user has access.

You may feel there are few queries/reports that combine locations at this time, but once a client gets one report that does this, they'll want more. These clients are usually paying higher licensing fees and will put additional pressure on your sales team (Which will put even more pressure on the dev team.).

  • Actually every store would have different credentials so there isnt direct database querying from customer. They consume an API. I also believe that it is very hard to combile results from multiple schemas. So you would keep single database for a customer? – GorillaApe Nov 23 '13 at 10:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.