Schemas and tables versus user-ids in a single table using PostgreSQL

Posted by gvkv on Stack Overflow See other posts from Stack Overflow or by gvkv
Published on 2010-05-03T10:03:43Z Indexed on 2010/05/03 10:08 UTC
Read the original article Hit count: 138

Filed under:
|

I'm developing a web app and I've come to a fork in the road with respect to database structure and I don't know which direction to take. I have a database with user information that I can structure one of two ways. The first is to create a schema and a set of tables for each user (duplicating the structure for each user) and the second is to create a single set of tables and query information based on user-id. Suppose 100000 users. Here are my questions:

  1. Considering security, performance, scalability and administration where does each choice lie?
  2. Would the answers change for 1000000 or 10000?
  3. Is there a set of best practices that lead to one choice or the other?

It seems to me that multiple schemas are more secure since it's trivial to restrict user privileges but what about performance and scalability? Administration seems like a wash since dumping (and restoring) lots of schemas isn't any more difficult than dumping a few.

© Stack Overflow or respective owner

Related posts about database-design

Related posts about postgresql