Multitenant application: several tables or several IDs?

Question

We are designing a common product to serve several companies. The most convenient way to do this, technically speaking, is to deliver a database to each customer.

But we predict that, depending on the customer size, deliver a database to each customer can be prohibitive (because of the cost of a cloud-based database, as we do not work with local databases), but we would like to serve the smaller companies too. So we are discussing the use of just one database to several customers.

If we decides to do so, what would be the best approach?

1. Apply a "master identifier" to all tables.

   All tables will have a column that we call, lets say "customer_id". So, each register recorded in the database will carry the customer ID, and we can differ between what belongs to which customer.

2. Prefix tables.

   All tables will be replicated to each customer (in the same database), in which each one will receive a prefix, pointing to what customer the table belongs. In the backend, easily we add the "prefix" feature to differ between customers.

Of course, each approach can be applied to reach the same objective, but we would like to know "the best approach". What are the implications to each approach, covering questions like performance, scalability, development complexity, easiness of administration, etc.?

Answer 1

The best approach is a mixture of allowing multiple databases and having a master identifier within the table.

This allows you to host multiple smaller customers in one database, but also to use multiple databases (so you can scale). Because there may be a point where you want to have more data than you can handle in one database.