Search code examples
databasedatabase-design

How do multi user applications use databases?

For services that have multiple users, how does that translate at the database layer? Take a simple key-value store that would use a three column table: ID (acting as a primary key, I know that's a thing), key, value.

If you now support multiple users, would you:

  1. have the same schema and a per-user table;
  2. add an owner column to my table above;
  3. something else entirely.

My intuition leans to solution (2), and all queries would restrict with owner = "foo". But as I said, I barely have any intuition. If you do go with (2), would the ID still be an ever-increasing integer, or would (owner, ID) be a sort of composite primary key?

Solution

  • The proper term for the concept you are referring to is "tenant", not "user". The term "user" has so many other uses that it is practically unusable. (See what I did there?)

    So, the question is "how do you implement multi-tenancy in databases?"

    And the answer of course, is with a "tenant_id" field. Always.

    From a relational point of view, any solution that involves a table-per-something is wrong. No matter what that something is.