|
|
Posted by avinashraj on 10/01/76 11:42
Hello,
I wanted your opinion on a technique that we want to use and hear from
you if you know of or, have experienced performance or other issues in
such a scenario.
We have a business application that has two parts to it - 1. A part
that works on things such as managing users, roles, authorization,
application level settings etc. This part of the application will
require its own set of tables in the database.
Additionally, and more importantly we have a part that provides the
core business value. And this part will need its own set of tables etc
in the database.
All of this was thought to live within one ms sql database
(2000/2005/Express we need to support all three). Now the Business part
of the database is expected to have a lot of data. In some tables - we
may have as much as 1 to 4 Million records. Ofcourse the number of such
tables is very few. Probably between 1 and 3.
So thats about the application. Now, we need to allow the users to
actually be able to create several named versions of the business side
of the database and use them. An example may be - the user may start
with lets say "My Simulation First Cut" and work with it for a while.
He may then choose to create a new version such as "My Second Attempt"
etc. The number of versions may not be too many. May be 5 to 15 at any
time may be present.
We were initially thinking of acheiving this by having one catalogue
database that manages the user info and a table that keeps track of
versions, while each version may be a sql database in itself. The
application was thought to be able to provide the ability to create
versions and allow the user to select the context in which he would
like to work in.
This is a solution that we need to port to DB2 on iseries aswell, and
now we are thinking of actually having just one database having many
schemas within it. One schema would hold the user information and we
are thinking of dynamically creating versions by creating additional
schemas on the single database. This would be somethng that the
applicaton would manage.
Some additional features that we would need to provide would be the
ability to quickly copy one of the existing schema/version into a new
schema/version. The application mostly works on dynamic sql and so has
very few stored procedures and functions etc... Therfore the multiple
schemas/versions would amount to very little redundant db objects.
Overall, we seem to think the multiple schemas (being created
dynamically) route is much better than the multiple databases(being
created dynamically) route. Do you forsee any problems with this
approach ?
Thanks in advance, Regards,
Avinash
Navigation:
[Reply to this message]
|