Would anyone be interested in a class on one way use MYSQL to support a multi-tenant web site?
I learned a way to do this years ago that allows me to use a single DB schema for a large number of tenants with very little (or no) change to the application layer. Tenants use the same DB tables, but do not have DB access to rows owned by other tenants (enforced by MYSQL). The web site I learned this from seems to no longer exist and I haven’t seen this technique described anywhere else.
If there is interest, the class would be held in late September.
Why not create different databases/schemas per tenant? I assume you’re doing either table name prefixes, or an identifier on each record, but both seem like a cumbersome way to accomplish the same thing that multiple schemas would accomplish.
Some hosting plans either have a limited number of mysql instances, or per instance fees. Or consider that for lightly used sites, your memory footprint is smaller, not having multiple instances in your memory footprint, and especially on rarely used instances, you are much less likely to wait for your instance to swap back in from disk.
A multi-tenant site is one where there is one instance of a web application serves multiple customers with completely different and separated data.
For example, this Talk forum is using a web application can discourse (I think) and you can access it through talk.dallasmakerspace.org. What if you could also go to the same instance of discourse through talk.othermakerspace.org and see a completely different forum with different users and different messages.
That is multi-tenant. One server system for multiple, but distinct, customers. Multi-tenant is popular because you don’t need to maintain multiple duplicated servers for each customer.
It’s a trade off. If you use multiple schemas then you have to keep them all updated and in-sync. Whereas if you use a single schema all of the tenant tables are automatically in sync. (e.g. there is only one users table for all tenants, therefore it can’t be out of sync.)
If you have 1000 schemas and you need to roll out an emergency hotfix it’s going to take a lot longer than if you had one schema.
Both methods have their pros/cons. Using multiple schemas is straight forward and there really isn’t a need for a class. Using one schema requires some creativity.
Ah, I think I see what you’re getting at - something like a SaaS website where clients have their own logins, but it’s all hosted on one shared system. I was thinking multiple applications sharing the same database and tables.