The company I work for is made up of 8 divisions, such as accounting, workers comp, sales, etc. Right now, each division has their own seperate application and database. We are now begining to talk about integrating these applications into one main application. Is it typical to have one database that houses each divisions tables or would you still keep a seperate database for each division? Right now, there is a Client database that stores all of our clients, so each database needs to read from this centralized database whenever we need client info, which is fine and dandy, but how do you handle situations like enforcing referential integrity across databases? I know each company is different on how they do these types of things, but how do you guys think the majority handles this (one main database or multiple smaller databases)?

Thanks!

If these departements have relations with each other (which is the case since they all reference the Clients databse) and there is no valid reason to separate them, I'll combine them all in only one Database. This will save a lot of efforts and hasseles. This is easier to develop against & easier to maintain.

For the record: Cross-database referential integrity can be achieved through the use of triggers. If still want to use the other option.

KristianP wrote:

it typical to have one database that houses each divisions tables or would you still keep a seperate database for each division?

Yes, it's typical. But I wouldn't hesitate to do the right thing and combine them. Probably there will be a lot of work involved, as applications that work with the databases will need to be modified, unless everyone just happens to track exactly the same information about a customer.

Thanks for the information! What's your guys thoughts on creating multiple llbl data access components for the same database based on the divisions?

any thoughts?

It's possible, you may also consider having one data access component to minimize the maintainance and updates on the data layer, and create a facade for each departement that only exposes what this departement needs.