Re: Table partitioning for cloud service?

On 5/21/20 8:29 AM, Israel Brewster wrote:
> I’m working on my first cloud service, which will be backed by a 
> postgresql database. Currently I only have a single customer, but of 
> course I want to design with the possibility of multiple customers in 
> mind. In that vein, I’m wondering what is “typical” in terms of 
> designing the DB structure to make sure that one customer doesn’t 
> “accidentally" get data for another customer? At the moment I am leaning 
> towards giving each customer their own set of tables, with a unique 
> prefix for each. This would provide a “hard” separation for the data, 
^^^^^^^^^^^^ I think that is called a schema:)
Or set up a separate database for each in the cluster.

> but would also increase maintenance efforts, as if I needed to add a 
> field I would have to add it to every table. On the other hand, keeping 
> everything in the same set of tables would mean having to be VERY 
> careful with my SQL to make sure no customer could access another’s data.
> 
> How is this typically done?
> 
> ---
> Israel Brewster
> BrewsterSoft Development
> http://www.brewstersoft.com <http://www.brewstersoft.com/>
> Home of EZPunch and Lyrics Presenter
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com