I'm planning a database which will contain data for many projects / customers.
Data for each customer will be totally independant. However they will all have the same structure.
So I'm wondering, could making a schema for each customer (and duplicating tables in it) be a good option ?
To resume, I ponder between the two options:
- using joins : each Table has a link to the Customer table, and when querying, we check that Table.customer_id == current_customer_id
- with schemas : we make a schema for each customer. On connexion, we select the schema. Then we don't have to worry about filtering
Solution 1 is more classic. But in each API, and roughly each time we access the data, we have to make a join to the customer table.
As we use SQLAlchemy, we can't just call
everywhere we have to transform it to
Table.query.filter(Table.id == id).filter(Table.customerId == customerId).first()
Using schemas would make the code simpler and elegant. No risk of sending data to the wrong customer. However it seems to be an abuse of the concept of "schema", as each one would contain the same tables. It would make the database more complex, and more difficult to maintain (table evolutions would have to be run in each schema)
Do you have any thoughts about the question ? Would there be an alternative, allowing us to select a subgroup of data at the beginning of each transaction ?
Thanks in advance!