0

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:

  1. using joins : each Table has a link to the Customer table, and when querying, we check that Table.customer_id == current_customer_id
  2. 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

Table.query.get(id)

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!

1

If I am understanding you correctly you have customers each customer, for example, can have different locations (offices) and invoices. They may have other tables also but for arguments sake I am keeping it simple. In example 1 you are suggesting 3 tables: Customers, Locations, and Invoices. In suggestion 2 you are proposing the number of customers times 2 (CustomerA_Locations, CustomerA_Invoices, CustomerB_Locations, CustomerB_Invoices, etc).

I would recommend option 1 because if the schema ever changes in any way then you would have to replicate that change in more places. Also the concept of keeping like data together is defeated in version 2. Customer information would be replicated more than one place with version 2 which would require synchronization of any change.

If you are worried about having to constantly join I would recommend using views to "join once." Views are not supported out of the box by SQLAlchemy but can be added per: https://stackoverflow.com/questions/9766940/how-to-create-an-sql-view-with-sqlalchemy.

If you have any questions I would be happy to expand.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.