W
Wiktor Zychla [C# MVP]
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
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