Re: Separation of clients' data within a database

Maybe I'm not understanding what you're getting at, so I'll throw out an
example:

--------------------------
With my current architecture, smartlowe logs in, but his login is
handled at the application layer, so his database connection is simply
"foo". He inserts a hundred records in the invoice table, which is the
public table invoice...these invoice records are automatically tagged
with the client_id associated with his login. When he runs a report,
the program forces a filter, aso by the client_id associated with his
login.

Now joe_user logs in, once again the "foo" db user, and inserts 100
records, tagged with his client_id. When he runs a report, it is
forceably filtered by his client_id. smartlowe and joe_user are writing
to the same database table, but the application forces the separation of
their data and reporting.

HOWEVER, when user big_daddy logs into the application, he just just
run a global query on the invoice table and pull all invoices. This
requires no special knowledge by the app of what clients exist in the
system...The SQL query handles the organization of the report by company.
--------------------------

I don't understand how I could implement what you're describing without
massive changes to my existing (5+ years in development) application.
Even factoring out that there are literally hundreds of people actually
logging into this system, I will just address a hypothetical if each
company only logs in with one user.

company_a logs in and inserts 100 records into tbl_invoice which
automagically becomes company_a.tbl_invoice. In his report, this also
automagically becomes company_a.tbl_invoice.

company_b logs in and inserts 100 records into tbl_invoice which
automagically becomes company_b.tbl_invoice. In his report, this also
automagically becomes company_b.tbl_invoice.

big_daddy logs in and wants to view *all* invoice data. In the invoice
report, this becomes big_daddy.tbl_invoice, which has nothing in it
because big daddy doesn't invoice. He wants the data from all of the
different companies. How would the system know to aggregate the
company_a.tbl_invoice UNION company_b.tbl_invoice UNION (etcetera)

As I said, we're talking about a pretty big system here, I don't have
the luxury of gutting the entire thing. Of course, I may just not
understand what I'm talking about with schemas, but that's why I'm asking ;)

Scott Marlowe wrote:

>That's just the point of search_path.
>
>For me, it can be:
>
>alter user smarlowe set search_path='common','smarlowe';
>
>for joe user it might be
>
>alter user joe_user set search_path='common','joe_user';
>
>and all you have to change is the connection statement for your app
>depending on who logged in. voila!
>
>