On Tue, Nov 07, 2000 at 02:31:40AM -0600, Callum Lerwick wrote:
> So, I'm planning on developing a web site that uses user accounts,
> like say slashdot or something. Whats the best way to deal with this
> on the DB back end? I want to have a main DB with user accounts and
> session management and such covering the whole site, but I would
> like to put certain things in their own DB for sanity's sake, but I
> still want to do table joins with the user account tables. Is there
> any way to do table joins across DB's in Postgres? Or is there
> another sane way to deal with this? I'll throw it all in one DB if I
> have to. :P

If you want to have persistent users, enforce the logic that all
authentication and capabilities of that user ties back to the master
list, wherever you place it.  Your users should be identified by a
unique identifier, an integer, for integrity and congruency.

Another tip I would suggest is to have a unique identifier for EVERY
row of the database.  You may hear some argument against this, but the
space of an additional integer in the database per record will save
you a LOT of time when trying to identify and reference unique
records.  This is very important if you want to implement any type of
historical record keeping.

Also consider saving true deletes for informational records in the
database for "purge" events.  In other words, write yourself a special
routine for "purging" a table of old data.  One way to do this is to
provide a datatime stamp field for these critical records.  To
reference the "active" record, simply take the MAX(timestamp column)
of the select statement for that record.

Following the above two suggestions can save you a lot of time and
headache.  Whether or not you can cross databases depends upon whether
or not you're using an application server that allows you to have
multiple connections to the database.  If not, you will likely be
jockying your DB connection from one DB to another, saving the
information retrieved in local variables.

    * Select user list from DB A.  Process result set.  Save locally.
    * Run "use <DB B>" to switch to different db or whatever
      useful API is provided by your app server.
    * Use list to select from tables in DB B.
    * Process result set.

-- 
Chad "^chewie, gunnarr" Walstrom <chewie at wookimus.net>
             http://www.wookimus.net/
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 232 bytes
Desc: not available
Url : http://shadowknight.real-time.com/pipermail/tclug-list/attachments/20001107/89380c48/attachment.pgp