when to use multiple databases

From: Michiel van der Kraats <michiel_at_backup.nl>
Date: Thu, 01 Apr 2004 00:20:19 +0100
Message-ID: <michiel-CC1182.00201801042004_at_corp.supernews.com>



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?

-- 
Michiel
Received on Thu Apr 01 2004 - 01:20:19 CEST

Original text of this message