I notice that some people use multiple databases for a project. I am curious as to why other then performance?

And if you do use them, what kinda setup do you go with. For instance, does each division of the company have their own database and how do you keep data taht is common to both syncronized on each database?

Answer wrote:

I notice that some people use multiple databases for a project. I am curious as to why other then performance?

And if you do use them, what kinda setup do you go with. For instance, does each division of the company have their own database and how do you keep data taht is common to both syncronized on each database?

You have to make a distinction between different schema's and different catalogs. On Oracle for example, it's common to use different schema's for various 'independent' groups of data, which can be used by 1 app. On Sqlserver you have schemas INSIDE a catalog, and it's often more appropriate to create schemas inside the catalog than different catalogs. However some people don't know this and think there can be just 1 schema in a catalog and use different catalogs for the situations with which they should use 1 catalog with different schemas.

There can be also a situation where you have different applications which tend to use the same sort of data or at least, related data. An example is our CMS + our customer database. These two are separate databases and support different applications. The thing is though that both contain IP numbers of people visiting the site, downloading demos etc. So it would be cool if there was a relation between the two so a 3rd app could give more insight in this. A multi-catalog project would be the solution then.

This is what is being implemented now in the upgrade currently in development: Multiple catalogs for SqlServer and multiple schemas for oracle per project. On Oracle it finds FK constraints which are defined accross schemas automatically. On Sqlserver you can't define an FK accross catalogs but you can of course define a relation between entities which are in different catalogs

You never cease to amaze me!

So how does inventory system with its own database and "products" table stay synconized with say a sales system in a different building and its "products" table.

Both tables are identical.

Or is this situation avoided at all costs? I am program as more a hobby with small odd jobs here and there, if you couldnt tell

In that situation, you won't keep them in sync. Better is to use 1 products database on a server in the network and to use a service (via remoting) to access that database, OR, you can use a linked server inside sqlserver (you can link a server inside another server so you can use the tables from one server in the other server, which allows you to access both databases on the same sqlserver instance. You then have 1 products database but it looks like you have 2.