I´m beginning the development of a new project and it will be a WEB application in ASP.NET, so I probably will use MS SQL.
The information stored on the database will be access from many different locations BUT it need to keep it separated for each location. In other words, each location only can access their records. Each location potentially will generate millions of records and will be thousands of locations.
This is my question:
In your opinion, what is better/practical/easy to maintain?
One Huge database, with security (o simple queries) to filter the data for each location
One database for each location
My concern is performance, but also how easy to maintaining will be.
For example: I’m afraid of making modifications to a table then need to replicate it to thousands of databases, in that case one database is more practical.
At this moment I´m not worry (yet) about infrastructure, servers, replication and that kind of stuff.
One database and one set of tables is definitely easier to maintain. It should also be much easier to implement the security you want if you have all the data in one place.
The main reason to have more than one database would be if you have very different availability or backup requirements or if you want to split the data over multiple servers. Otherwise put everything in one place.