StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You mentioned Postgres as the database backend and that has the feature called schemas. This is something where you have one physical database and multiple schemas inside the database. My experience with this comes from Rails, but the concepts are the same. This method avoids mashing people's data together in the same set of tables which sounds like your primary concern. I know you're using Java, but <a href="http://www.bestechvideos.com/2009/03/26/acts_as_conference-2009-writing-multi-tenant-applications-in-rails" rel="nofollow noreferrer">watch this talk on Multi-tenant apps in Rails to get a background from Guy Naor</a> on how this works, trade-offs, etc.</p> <p>Here are some concrete steps to get you started down this path of using Postgres schemas:</p> <ol> <li>There is a public schema in Postgres that is the default. This would be where you put your user authentication tables and any other generic meta-data tables about user logins, etc. <a href="http://www.postgresql.org/docs/9.2/static/ddl-schemas.html" rel="nofollow noreferrer">See Postgres docs for more info on how schemas work</a></li> <li><p>Come up with a naming convention for each schema you will create (e.g. user_001, user_002, etc.). Pre-allocate a bunch of empty schemas with all the tables setup and when the user registers or logs in for the first time, you assign them a schema and store the schema name in their user record in the public schema and in the user object that you have in HttpSession. There would be no need to run table creation scripts for a first time user - that would be a performance drag in a web app. You just need to stay ahead of the rate of new users. For example you could have a bunch of empty user_standby_1 ... user_standby_100 schemas and then when someone logs in or registers, you would run this sql: </p> <p><code>myquery = "ALTER SCHEMA user_standby_? RENAME TO user_?"; myquery.setString(1,standby_id); myquery.setString(2,user_id);</code></p></li> <li><p>When you create your DB bean (use a superclass for this, see below), pass in the schema name from the User object from the HttpSession, then execute this SQL before every operation to isolate them to their schema only:</p> <p><code>myquery2 = "SET search_path TO ?";<br> myquery2.setString(1,user.search_path);</code></p></li> <li><p>If you have an empty full schema in public, then you want to omit public from the search path otherwise you will have 2 tables with the same name in the search path. If you want the users search path to include <code>SET search_path TO user_001,public</code> then after creating tables, drop all the data tables from public other than users and any meta-info you need.</p></li> <li>For maintenance, write a script you can run via command line to drop empty user_standby schemas, create new user_standby schemas and do the equivalent of Rails <a href="https://stackoverflow.com/questions/131020/migrations-for-java">Migrations for Java</a> for minor table changes.</li> <li>For large maintenance activities it might be best to create new schemas, e.g. user_v2_001, for each user and then write scripts to migrate their data in. This depends on how complex the changes are to your tables.</li> </ol> <p>If you go the alternative route and have all users data in one set of tables, then the best approach is to have user_id in <em>every</em> table and write your SQL to use that <em>every</em> time. If you use traditional normalization and do joins to get your user_id, then you better make sure you don't accidentally miss a join or users will start seeing each others data.</p> <p>The Postgres schema feature allows you to lock users access to only their own data. After figuring out the basics, use a superclass in Java to write step 3 above so every MyTableDBBean extends from MasterDBBean and uses the super class constructor to isolate the search path to the user's schema. Then you only have 1 place in your code where this is done and you don't have to remember for every table or query to do anything more than the business logic. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload