[sbaru]

We have a situation where there are 4 different applications that share some common data like (customer, units etc). My idea was to create seperate databases for each of these applications and then one common database that holds all the common information. (We are using SQL Server 2000). The problem with this approach is that I cannot create referrential integrity across databases. The other option is to replicate the common data in all the databases.
If there is a way out, I would want to implement the common database approach. So was looking for some suggestions here.

Any ideas are greatly appreciated

[ss5416]

Have you considered an OLAP DB to data mart the multi-application data? Sounds like a prime chance to try that out.

Still wouldn't solve the referential integrity part. Almost think that would require an external business layer to manage it.

Perhaps you could build your own constraints on INSERT, UPDATE, and DELETE to manage the external DB's. Afterall, RI is just natural constraints defined on related tables. Nothing says you can't build your own. Except maybe you DBMS's ability to support constraints.

Hmm...That would be how I would approach it but leave it to this community to explain any theory on the topic.

[ss5416]

Better yet, if you have the ability to pull those common tables out, customers is a prime one, into its own DB, you'd be set. That way you are not duplicating data in multiple applications. They would just be fed from one source. Guess you have to weigh the administrative considerations from cross DB joins.

Of course you still have the RI issue. For instance, if you want to cascade a delete, you would still need the custom constraint to manipulate the external DBs.

Sorry for rambling through your discussion. Brainstorming, I suppose.

[sbaru]

thanks for your time. I am not very much worried about cross DB joins, because there will be no difference in performance. And yes, my thought is to put all the common tables (like customer) in a common database. The issue comes when, let us say there is a SP in one of the application databases that queries a customer record in the common database, it finds it and then tries to insert a child record for that customer. Between the first select and the subsequent insert, the customer record in the common database is deleted, what would happen? This is one of the situations I could think of. Am looking for issues like this and the best way to handle them. One way I could think of, is to create a customer table in every database that just has only the customer ids and tie the RI to all the child tables in that database. In the main database, I should have triggers on the customer table that will replicate the change onto the customer tables in the application databases. Not sure if I am clear, but do you see where I am going?

[AdemMeral]

U can link the tables in sql server 2000 and enforce referencial integrity that way. Just my $ .02.