Hi Guys!

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

Or

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.

Thanks in Advance!

Edgar

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.

"Put all your eggs in one basket. And then, watch that basket!" -Mark Twain

hey what's up with this? you guys are agreeing on something???? )

Don't worry - I know what to do:
Use natural keys and make location the lead column of your clustered primary keys.

pootle flump ur codings are working excelent.

Thank Guys! this was very helpfull!