We currently have a single Microsoft SQL Server db-driven .Net web
app for a single city. It handles much data storage and statistical
analysis. Soon we will be increasing this to several other cities
in several states, each city needing its own separate database.
However, we then need to provide access from both state and national
levels for some users, allowing these users to see and query across
So my question is basically what is the best overall design to
implement for this scenerio. I have never done anything like this,
but I would assume there is a "standard" general way to implement
this. I am not asking for every detail to the answer, but need to
get started in the right direction. Whatever information/advice
someone could give me would be greatly appreciated. Even directions
to the best book or training resources would be great.
Some details that might come in handy.
1. The individual cities MUST have their own db.
2. My thought is to replicate the single dbs into one huge db each
night, allowing the individual cities' web apps to always refer to
the individual db and then having one "overall" web app refer to the
one "overall" db. Hopefully, this would allow us to use the same
web code for ALL situations.
3. I suppose the other alternative is to keep all the dbs separate
and query across multiple dbs. But this would seem to me to be too
slow and require much change in web or db coding (don't want to
support more than one app).
4. To combine multiple dbs, this would seem to necessitate many
tables to use GUIDs (yuck!) as keys so that they will still be
unique after the combining. Sounds ugly (and perhaps slow), but
don't know what else can be done.
Sorry for the verbosity of this.