What are the best practices procedure to store data in a high volume SAAS based Web App?
The database will be in the form of Single Instance MultiTenant. One database and one schema for all tenants.
All tenants will share the same tables and it will be a tenantID on each table to filter the information.
Is there a way to create dynamically, automatically or programmatically a new filegroup based on the tenant geographical information? for example each filegroup will contain the data for all tenants that matches the following geographical information: country/[state or province]/ [zipcode or postalcode].
Any suggestions will greatly appreciate.