|
|
Posted by james.igoe@gmail.com on 06/22/06 16:32
I recently recommended a multiple database solution, since my
assessment of the client's needs and data were that they needed the
flexibility of separate databases per data set. Also, my deadline for
completion was very short, and this product was not considered to be
used for longer than the near future.
Each external client's (about 20 clients) data set is different - even
the same client data set could vary - and the process was to massage
each set of data via stored procedures. I Initially opted for one
database per client, to allow for reuse of lookup data (holidays,
fiscal periods, accounts, etc.), but later realized that what I was
designing, an Excel workbook to analyze accounting data, would be
easier to modify if I kept everything the same and simply modified the
connection string.
Ultimately, the choice of multiple servers keeps the stored procs for
the XLW, and the XLW itself, the same, while the only modification
occurs in the stored procedures for building the data, since the data
requirements could vary wildly. The multiple database approach also
allows for multiple data sets to be massaged at the same time -
building the final data set could take several days, so some problems
in concurrency could develop - and with varying procedures for such the
independence was a benefit, albeit creating a lot of redundancy.
Given enough time to design a solution that preserved security - no
client specific information, nor information useful for hacking the
server data could be local, other than the connection string - and ran
as a single database, I could certainly design a solution for them.
James Igoe
james.igoe@gmail.com || http://code.comparative-advantage.com
Jason Kester wrote:
> As everybody has mentioned, 500k records and 100 users is small by any
> definition. But regardless, performance should not be a consideration
> until it forces itself to become so.
>
> Think about your idea from a maintenance perspective. At some point
> you'll need to add a column to one of those tables, or even make a
> simple stored procedure change. Imagine the pain this will cause you
> down the road, trying to synchronize changes in all those databases.
> Multiply every small hassle you'll ever come across in the future by
> the number of "Projects" in your universe. Yikes!
>
> Stick to one database per Application and you'll live a long and
> healthy life.
>
> Jason Kester
> Expat Software Consulting Services
> http://www.expatsoftware.com/
>
> ---
> Get your own Travel Blog, with itinerary maps and photos!
> http://www.blogabond.com/
Navigation:
[Reply to this message]
|