Have been developing a multi-tenancy application for years now and have yet to put in production yet.
I am now getting cold feet over the basic design of the backend Postgres database.
Each table has a tenantid column which Alpha 5 filters on for each and every dialog, grid, query, report, etc. based on the user login information. This is the fundamental one database, one schema design for multi-tenancy. Works fine and I get it.
After some pretesting and short starts, I am now concerned about performance down the road for some of the tables, particularly timesheet and workflow tracking that will have hundreds of thousands of entries in each table potentially from each tenant. This won't be an Alpha 5 issue, but a Postgres performance issue. Since I have no practical way of knowing how many entries will be made or even how many tenants will use the system, I am gravely concerned of building my castle on sand when my app takes off like FaceBook.
After reading many of the posts on this forum and elsewhere regarding multi-tenancy, I wonder if I should be going back to the one database/tenant design and use dynamic data connections instead. Nearly everybody recommends against this because of maintenance/update/backup issues going forward, but seems like the best solution for scalability, security (no intermixed data), and simplistic programming.
The real question is my understanding of schemas, particularly in relation to Postgres and the interaction with Alpha 5. I was hoping that schemas could have a master layout of employees, customer, orders, etc. and be able to setup a schema based system that would automatically have grids, dialogs etc. refer to table like this:
company1.employees
company2.employees
company3.employees
company1.customer
company2.customer
........
You would then have grids, etc. refer to the table through a dynamic connection that would enable a variable based on the login to interject the <company>.table as a datasource for each grid.
But, what I can tell of Alpha 5, it refers to the schema tables as:
employees.company1
employees.company2
employees.company3
customer.company1
customer.company2
......
#1 So my bottom line understanding/question is, a dynamic data connection only works for a database, not a schema correct?
#2 Is there something I am missing to be able to assign grids and dialogs dynamically based on the user login?
#3 If #2 is possible, I assume the same can be done with custom xBasic and reports too?
Thanks for reading through my long winded question.
Andy
I am now getting cold feet over the basic design of the backend Postgres database.
Each table has a tenantid column which Alpha 5 filters on for each and every dialog, grid, query, report, etc. based on the user login information. This is the fundamental one database, one schema design for multi-tenancy. Works fine and I get it.
After some pretesting and short starts, I am now concerned about performance down the road for some of the tables, particularly timesheet and workflow tracking that will have hundreds of thousands of entries in each table potentially from each tenant. This won't be an Alpha 5 issue, but a Postgres performance issue. Since I have no practical way of knowing how many entries will be made or even how many tenants will use the system, I am gravely concerned of building my castle on sand when my app takes off like FaceBook.
After reading many of the posts on this forum and elsewhere regarding multi-tenancy, I wonder if I should be going back to the one database/tenant design and use dynamic data connections instead. Nearly everybody recommends against this because of maintenance/update/backup issues going forward, but seems like the best solution for scalability, security (no intermixed data), and simplistic programming.
The real question is my understanding of schemas, particularly in relation to Postgres and the interaction with Alpha 5. I was hoping that schemas could have a master layout of employees, customer, orders, etc. and be able to setup a schema based system that would automatically have grids, dialogs etc. refer to table like this:
company1.employees
company2.employees
company3.employees
company1.customer
company2.customer
........
You would then have grids, etc. refer to the table through a dynamic connection that would enable a variable based on the login to interject the <company>.table as a datasource for each grid.
But, what I can tell of Alpha 5, it refers to the schema tables as:
employees.company1
employees.company2
employees.company3
customer.company1
customer.company2
......
#1 So my bottom line understanding/question is, a dynamic data connection only works for a database, not a schema correct?
#2 Is there something I am missing to be able to assign grids and dialogs dynamically based on the user login?
#3 If #2 is possible, I assume the same can be done with custom xBasic and reports too?
Thanks for reading through my long winded question.
Andy
Comment