Multi-user Web Application Database Design

Question

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.

Answer 1

As a general rule of thumb, you pretty much never want to have multiple tables (or databases) with identical structures. If you find yourself considering the creation of separate stuff_for_user_a and stuff_for_user_b tables (which is what your options #2 and #3 sound a lot like), then you probably want to just create a stuff table which includes a user column (i.e., your option #1).

Answer 2

I've almost always gone with option #1. If you design it right you may only need your 'user' column in a few key tables that are your entry point and then everything else can be joined off of those key tables.