Обсуждение: Data partitioning
We offer a web based application to companies. By keeping a company_id in the schema we differentiate the data amongst companies. e.g the user table has a company_id field to distinguish users between companies. However, most companies are feeling "insecure" about their data not being stored separately from others. Also from a maintenance perspective it seems it might be better. e.g restoring the data of an individual company will be impossible. Is there a way to handle this? Preferably using a single database? Thanks, -- Ravi.
I don't know what your application looks like, but we're handling a similar situation by using several databases in a single cluster. In our case, the apps are JDBC-based, so it is possible to open a connection pool to each database & easily point the same application code at different sources. This does *not* allow queries across the databases, but *does* allow us to use a single instance of the application to serve up data from different databases depending on who is asking for it. Would this address your needs? -Nick -------------------------------------------------------------------------- Nick Fankhauser nickf@ontko.com Phone 1.765.935.4283 Fax 1.765.962.9788 Ray Ontko & Co. Software Consulting Services http://www.ontko.com/ > -----Original Message----- > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Ravindra Wankar > Sent: Thursday, December 20, 2001 2:02 AM > To: pgsql-admin@postgresql.org > Subject: [ADMIN] Data partitioning > > > > We offer a web based application to companies. By keeping a company_id > in the schema we differentiate the data amongst companies. e.g the user > table has a company_id field to distinguish users between companies. > > However, most companies are feeling "insecure" about their data not > being stored separately from others. Also from a maintenance perspective > it seems it might be better. e.g restoring the data of an individual > company will be impossible. > > Is there a way to handle this? Preferably using a single database? > > Thanks, > -- Ravi. > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
We do something similar. What we do is create tables for the different companies (with an underscore as first char). Then we create a meta-lookup table that maps the customer_id to the correct table name. There are some real big problems with this. 1. Tier 1 looks ugly, very ugly. 2. It takes some of the functionality that should be in the RDBMS, and shoves it out the programmers, who make mistakes. 3. One more lookup, before you even put together your query. 4. If you have relations between products and other tables :( (look at #2) We are moving away from this model to something more like what you are doing. You clients may not like it, but they'll hate a really buggy system more. Christian Brink CTO ONSITE! Technology www.onsitetech.com 503.233.1418 cb@onsitetech.com Taking e-Business and Internet Technology To The Extreme! > -----Original Message----- > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Ravindra Wankar > Sent: Wednesday, December 19, 2001 11:02 PM > To: pgsql-admin@postgresql.org > Subject: [ADMIN] Data partitioning > > > > We offer a web based application to companies. By keeping a company_id > in the schema we differentiate the data amongst companies. e.g the user > table has a company_id field to distinguish users between companies. > > However, most companies are feeling "insecure" about their data not > being stored separately from others. Also from a maintenance perspective > it seems it might be better. e.g restoring the data of an individual > company will be impossible. > > Is there a way to handle this? Preferably using a single database? > > Thanks, > -- Ravi. > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)