Hi!
I'm modelling an application that will have data -- financial data, human
resources, etc. -- for several hundred (even thousands) of companies. This
is for an accounting office.
I could put some kind of "company_id" column in all of my tables to separate
data in a more standard way, I could create a separate schema for each client
and then create all needed tables in there (could I? I'd be abusing schemas
here and this is part of my doubt) and, finally, I could create a "base"
schema, define all my standard tables and create an individual schema for
each client where I'd inherit from those base.tables.
This would allow me to separate all information with a "SET search_path TO
company" without having to make the restriction "by hand" (on "company_id",
for example). It would also allow me to view some complete statistics
grouping all clients by SELECTing data from the base schema. We're testing
views and functions to see how they behave with inherited tables and changes
on "search_path", and it looks like we can do that for, at least, a small
number of schemas.
Of course, this has implications on permissions as well, so there will be a
large number of groups -- probably at least one per schema + some common
groups -- and roles as well...
Is this a good idea? Would this be too bad, performance-wise, if I had
thousands of schemas to use like that? Any advice on better approaches? Any
expected problems?
TIA,
--
Jorge Godoy <jgodoy@gmail.com>