when to use multiple databases

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Michiel van der Kraats - 01 Apr 2004 00:20 GMT

I have an application which tracks backed up files using a very simple
schema:

table session(sess_id, date,totalsize)

table files(filename,size,date, sess_id)

Now, I need to relate sessions to users. Each user has a different
collection of files. I would normally just add a table to hold user
information and the necessary foreign/primary keys to relate users to
sessions. Some of my colleagues want to use a different, seperate
database for each user and a 'main' database for tracking user
information (which would contain just one table, the one with user
information, presently only a userid and name/password).

The DBMS I'm using (Postgresql) doesn't allow cross-database queries,
querying data accross the main and 'per-user' databases can get quite
difficult. I'm also not very comfortable with this because the
relationship between my 'main' database and per-user databases is not
directly visible in the database schema.

Putting the file information in a seperate database would be somewhat
more secure as errors to each users' 'files' table would be isolated.
Removing all of a users information would be as easy as dropping that
users's database.

When is it wise/practical to use multiple databases if all those
databases have the same schema?

Signature

Michiel

Reply to this Message

Paul - 04 Apr 2004 15:15 GMT

Well I guess in theory the entire world only needs a single database.

Of course in practice this isn't done, but I think I'm right in saying
this is for physical reasons. Namely the problems of finding a DBMS to
run it on. Also the problems of the single namespace for tables. You'd
end up encoding a kind of hierarchical naming convention in the table
names: CountyA_CompanyB_DeptC_Sales or whatever. Not to mention the
political problem of who controls access etc.

It does raise the question: does the "database" structure serve any
theoretical purpose? Or could we just have a "Universe" that contains
tables, etc.?

Going the other way, might it be useful to have databases grouped
together? But then you're going down the road of hierarchies which
aren't nice for first-order logic.

> The DBMS I'm using (Postgresql) doesn't allow cross-database queries,

Speaking more practically now, I think this is your deciding factor: use
a single database.

> When is it wise/practical to use multiple databases if all those
> databases have the same schema?

Speaking theoretically I'd say never. But there may be physical reasons
for it.

Paul.

Reply to this Message

Laconic2 - 04 Apr 2004 15:37 GMT

The entire world needs only one data model. Not one database. The data
model should pervade
all information sharing within that world. Databases and other vehicles for
information sharing should reference that model.

If there is more than one world, and they are disjoint, it doesn't matter
whether they share a data model or not. They probably won't, given the
nature of things.

If a portal forms between two previously disjoint worlds, you need an object
that I'll call a "janus" at the portal. A janus is a two faced object that
guards the portal. In each world, it behaves like any other object in that
world. But its internal state is a composite of inputs from both worlds,
integrated together. Viewed through a janus, each world thinks the other
world is encapsulated in the janus.

Eventually, if two worlds coalesce, through enough januses, a common
integrated model will emerge.

I'm sorry if this is not responsive to your issue, but its my two cents.

.

Reply to this Message

Robin Tucker - 22 Jun 2004 17:44 GMT

The only time I would say is when you are using MSDE and you have a 2 gig
limit per database. Otherwise, it seems daft to keep each users data in a
separate database. You would find it very difficult to generate queries
about users with multiple database when you cannot perform a cross DB query
(and even if you could, it would be much slower).

> I have an application which tracks backed up files using a very simple
> schema:
[quoted text clipped - 24 lines]
> When is it wise/practical to use multiple databases if all those
> databases have the same schema?

Reply to this Message