Sql-server – Database design : multiple category of users login into same system

Some constraints are best enforced in the database, and some are best enforced in the application.

Constraints that are best enforced in the database are usually there because they are fundamental to the structure of the data model, such as a foreign key contraint to ensure that a product has a valid category_id.

Constraints that are enforced in an application might not be fundamental to the data model, such as all FooBar products must be blue - but later someone might decide that FooBars can also be yellow. This is application logic that doesn't really need to be in the database, though you could create a separate colours table and the database can require that the product reference a valid entry from that table. BUT the decision that the only record in colours has the value blue would still come from somewhere outside the database.

Consider what would happen if you had no constraints in the database, and required them to all be enforced in the application. What would happen if you had more than one application that needed to work with the data? What would your data look like if the different applications decide to enforce contraints differently?

Your example shows a situation where it might have been more beneficial to have the constraint in the application rather than in the database, but perhaps there was a fundamental problem with the initial data model being too restrictive and inflexible?

This is the old "how to handle multi-tenancy" question for which there is no one right answer. You have covered a good selection of the key points. I can't provide an answer beyond "it depends" but here are a couple of other points for you to consider:

• mixing data of different clients ... if there is a bug, its easier for someone to end up accessing some else's data and Come up with a scheme associating each installation [correct] database - the risks here are very similar if the various databases are on or can be accessed by the same hosts, especially from the point of view of a defending against deliberate attacker, so that part of the decision comes down to which collection of potential security concerns you feel you can efficiently design around most effectively. If you feel your SQL skills are lacking that might push you in the multiple database direction due to concerns about large data sizes become inefficient as your client base grows.

• For each installation, buy a DB from the hosting company for a SaaS solution you have an inappropriate hosting arrangement if you are paying per database or similar. It sounds like traditional shared web hosting were you should be looking at dedicated machines (virtual or physical) or a PaaS based platform.

• Consider carefully how you intend the application to scale. The multiple databases option gives you the freedom to split clients between database servers as your needs grow, but the single database option is easier to manage in a PaaS environment if you design is appropriate, letting the platform provider worry about the physical reality of scaling as you pay for more resources.

• Also on the scalability matter, make sure consider your administrative responsibilities: how will you backup the data, restore parts of it if there is an error/accident, or restore all of it in the event of an application/platform wide catastrophe, and what timescales do you want/need to work to in such events?

For more information see https://msdn.microsoft.com/en-us/library/aa479086.aspx, https://en.wikipedia.org/wiki/Multitenancy, and search for similar terms for further discussion. (that first link is from MS, but covers the matter from a general design perspective rather than being specific to MS SQL Server, it has also been around a while but the matters concerned haven't changed as much as you might think the would have in the last decade)