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?
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.
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.?