Think about a mobile phone manager system that consists of information for each user like a table that stores the applications he has installed on the telephone, auditing particulars, notification information etc. Could it be smart to produce a seperate schema for every user using the corresponding tables? The amount of tables is big for any single user amounting to around 30 tables each. Will it be better to possess a seperate schema where all this post is place into these tables (consequently creating enormous tables?) or possess a schema for every user?
Thanks ahead of time
I wish to see which technique is more effective when it comes to querying within the database.
Inside a multi-tenant database, querying is just area of the problem. Other significant parts are cost, data isolation and protection, maintenance, and disaster recovery.
Multi-tenant solutions vary from one database per tenant (shared nothing) to 1 row per tenant (shared everything).
"Shared nothing", "separate database", a treadmill database per tenant
- Most costly per client. (Large amounts of clients imply large amounts of servers.)
- Greatest amount of data isolation.
- Disaster recovery for any single tenant is straightforward and simple.
- Maintenance is theoretically harder, because changes have to be completed in each and every database. However your dbms would probably support running saved methods in every database. (SQL Server comes with an undocumented system saved procedure, sp_msforeachdb, for instance. You are able to most likely write your personal.) "Shared nothing" is easily the most easily easy to customize, too, but which boosts more maintenance issues.
- Cheapest quantity of rows per table. Querying speed is near optimal.
"Shared everything", or "shared schema", or "one database per planet"
- Least costly per tenant.
- Cheapest amount of data isolation. Every table includes a column that identifies which tenant a row goes to. Since tenant rows are included every table, it's easy to accidentally expose other tenant's data.
- Disaster recovery for any single tenant is comparatively complicated you need to restore individual rows in lots of tables.
- Structural maintenance now is easier, considering that all tenants share the tables. Zinc heightens the communication load, though, because you need to communicate and coordinate each change with every tenant. It isn't easily easy to customize.
- Greatest quantity of rows per table. Quick querying is harder, however it is dependent on the number of tenants and just how many rows. You can easily fall over into VLDB territory.
Between "shared nothing" and "shared everything" is "shared schema".
- Tenants share a database, but each tenant has it's own named schema. Cost falls between "shared nothing" and "shared everything" large systems typically need less servers than "shared nothing", more servers than "shared everything".
- Far better isolation than "shared everything". Less than just as much isolation as "shared nothing". (You are able to GRANT and REVOKE permissions on schemas.)
- Disaster recovery for any single tenant require rebuilding one of several schemas. This really is either relatively simple or fairly hard, based on your dbms.
- Maintenance is simpler than "shared nothing" not as simple as "shared everything". It's easy to create a saved method that will execute in every schema inside a database. It's simpler to talk about common tables among tenants compared to "shared nothing".
- Usually more active tenants per server than "shared nothing", meaning they share (degrade) more assets. Although not badly as "shared everything".
Microsoft includes a piece of content on multi-tenant architecture with increased particulars. (The hyperlink would be to only one page of the multi-page document.)