I'm working on a web application that will be a hosted, multi-user solution when it is finished. I'm trying to figure out the best way to handle the database design for my app. Specifically, I need to figure out how to handle multiple, separate accounts.
The way I see it, there are a few options: 1) Have one set of database tables. In each table, include a 'user' column or something similar which will map each row to the proper user account. 2) Create a totally separate database for each user. This doesn't seem like a terribly great idea for performance reasons. 3) Create a separate schema for each user within a single database. Each schema will contain the tables for each user.
How would you handle this problem? Is there an option I'm missing? I'm using PostgreSQL as my database if that makes any difference in how you would handle this problem.