Sorry for posting here but I belive that this group is read by a lot of
people who could share their experience.

I am facing the issue of large system built possibly in different
technologies and using several databases. I am to develop the architecture
of such system.

What I did in the beginning was rejecting the idea of a huge single
database, mainly because of maintenance and conservation issues but also
because I think that as the system will grow up, new applications should be
built using newborn technologies yet unknown. I do not like to idea of
binding to one database server and one particular architecture of data
access.

However, building a system which spans across several databases is a
challenge to me since I can see at least two differen strategies of linking
data in two (or more) databases:

1. replication / synchronization where entities are periodically copied /
updated between databases. this way the integrity of each single database is
maintained, however the need for constant replication / synchronization
seems burdersome and I am afraid that will be very expensive (meaning human
and hardware resources)

2. maintaing "virtual" links between tables, so that for example I have
Customer table in one database and Orders in the other and the ID_USER
column in the Orders table refers to the ID field in the Customer table but
no foreign key is defined in the database server (because tables exist in
different databases, possibly on different servers). this way there are no
replicaiton issues but the business logic of the application must be
responsible for maintaining and resolving reference integrity.

are there other possibilities?

My experience shows that the first possibility is reliable but burdensome
and "not agile". After two or three applications following this pattern I
would like to try the other possibility but am not sufficiently sure that it
is worth to try it.

I do not expect any problems in database access in such "multiple databases"
scenario - our case studies show that using a object-relational mapping we
can easily maintain separate sessions targeting different databases and add
properties linking objects from different databases manually so that
relations are properly resolved across multiple databases.

I would like someone to share his/her experience on this issue.

Thanks in advance,
Wiktor Zychla

Hi Wiktor,

Probably the C# group is not the best place to be asking this
question, as the largest part of the code of your apps doesn't care
often about the type of database. If you try the online community at
all, perhaps it's better to go to database- or IT-Architecture
newsgroups? In any case it seems you have a large project on your
hands, if it comes with a large budget it would probably pay off very
much to hire a database-architect consultant on this subject for some
help.

More specific to your question, and on what approach to take, we would
need to know more details on the domain/problem you have. Consider
also comparing your situation to others with large database systems
(amazon, wikipedia, myspace, google search, etc) and trying to find
out how they've architected things.

Good luck,
Jeroen

Hello Wiktor Zychla [C# MVP],

W> 2. maintaing "virtual" links between tables, so that for example I
W> have Customer table in one database and Orders in the other and the
W> ID_USER column in the Orders table refers to the ID field in the
W> Customer table but no foreign key is defined in the database server
W> (because tables exist in different databases, possibly on different
W> servers). this way there are no replicaiton issues but the business
W> logic of the application must be responsible for maintaining and
W> resolving reference integrity.

who is gonna maintain these links?
I'm not sure that using this approach allows you to turn the database normally.
because as I suppose your will lost all advantages of standard indexes, CTE,
and other stuff.


W> are there other possibilities?

I'd thiking about moving all logic to conceptual layer, adopting Entity Framework
to your needs.

Some times ago I was working on similiar task and we built the prototype
of the DataBus.
You can find my thoughts about this there http://laflour.spaces.live.com/blog/cns!7575E2FFC19135B4!662.entry


---
WBR,
Michael  Nemtsev [.NET/C# MVP] :: blog: http://spaces.live.com/laflour

"The greatest danger for most of us is not that our aim is too high and we
miss it, but that it is too low and we reach it" (c) Michelangelo

Design (gui)
Function Keys Saving
Using "fscan" Equivalent in C#
Using CSharpCodeProvider to create MANY assemblies
Assigning a reference to a variable