Help! Multiple Database Architecture
I have to work with what I believe to be an appalling SQL server Architecture
but need some cold, hard facts/statistics to back up my beliefs.
Our Architecture is basically a thickish VB client front end that uses COM+
components that do no more that control transactions i.e. there is no business
logic in them as all our business logic is done in Stored Procedures which
can make life very difficult, but IMO this is not the problem. The problem
is the database architecture where we have >50 databases which are meant
to hold functionally compatible parts of the software EXCEPT that all the
data inter-relates so there are lots of columns that exist in > 1 database.
Most of these "databases" have only a few tables, one DB has one table of
five columns! One of the major headaches is that there is no referential
integrity between the same column in different databases, eg. when we did
some data cleansing on "individuals" recently we had to apply the cleansing
to 10 databases as the individuals existed in all 10 databases but with different
and even different datatypes.
What I really need are some references that will help me prove that this
architecture is far less than ideal from a design and performance point of
view, as I think I can back up the idea that development on the system is
hardly AD let alone RAD. The point of a relational database is to keep data
that relates in one database not to split relational data into different
databases. All the data is kept on one server so there are no performance
reasons for doing it, i.e. to split that data over servers or as far as I
can see any other reasons. The main reason I need help is that the man who
designed it is my boss's boss and will not have a word said against it even
though 6 months into a 3+ year project it is already causing developers and
support unnessecary headaches.