Thread: chosing a database name

chosing a database name

From
Karsten Hilbert
Date:
Hi all,

we are developing GNUmed, a medical practice management
application running on PostgreSQL (you want your medical
data to be hosted by something reliable, don't you ;-)  We
are putting out our first release sometime in the next two
weeks.

The idea is to name the production database "gnumed0.1" for
version 0.1 (gnumed0.2 etc for upcoming releases). I do
realize the "." may force me to quote the database name in,
say, a CREATE DATABASE call.

The rationale is that when going from 0.1 to 0.2 I can
simply create the database "gnumed0.2" and populate it with
tables all the while gnumed0.1 is still up and running
serving users. Downtime would only be needed to actually
migrate the data (which, of course, is the bigger time sink
- unless we use Slony which may or may not possible due to
schema changes). The other advantage is that I can instruct
my doctors to "startup the previous client version if
something breaks at 3:30am and call tech support in the
morning". I *am* a doctor. I have personally been in that
very situation with commercial electronic medical record
applications. Which is part of why I am participating in
GNUmed in the first place.

Now, my question is whether I am failing to see the
*dis*advantages that may be looming from such an approach.

Anyone has any helpful thoughts on this ?

Karsten Hilbert, MD
GNUmed i18n coordinator
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: chosing a database name

From
Alvaro Herrera
Date:
On Wed, Jul 13, 2005 at 05:56:03PM +0200, Karsten Hilbert wrote:

> we are developing GNUmed, a medical practice management
> application running on PostgreSQL (you want your medical
> data to be hosted by something reliable, don't you ;-)  We
> are putting out our first release sometime in the next two
> weeks.
>
> The idea is to name the production database "gnumed0.1" for
> version 0.1 (gnumed0.2 etc for upcoming releases). I do
> realize the "." may force me to quote the database name in,
> say, a CREATE DATABASE call.

I doubt you'll have any problems with the tools, but the quoting may
prove painful.  Why not replace the dot with an underscore? gnumed0_1

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Nadie esta tan esclavizado como el que se cree libre no siendolo" (Goethe)

Re: chosing a database name

From
Berend Tober
Date:
Alvaro Herrera wrote:

>On Wed, Jul 13, 2005 at 05:56:03PM +0200, Karsten Hilbert wrote:
>
>
>
>>we are developing GNUmed, a medical practice management
>>application running on PostgreSQL (you want your medical
>>data to be hosted by something reliable, don't you ;-)  We
>>are putting out our first release sometime in the next two
>>weeks.
>>
>>The idea is to name the production database "gnumed0.1" for
>>version 0.1 (gnumed0.2 etc for upcoming releases). I do
>>realize the "." may force me to quote the database name in,
>>say, a CREATE DATABASE call.
>>
>>
>
>I doubt you'll have any problems with the tools, but the quoting may
>prove painful.  Why not replace the dot with an underscore? gnumed0_1
>
>

Or why bother including either? Just use sequential integers, maybe
left-padded with zeros to make the name the same length for the first
thousand or so releases?



Re: chosing a database name

From
Karsten Hilbert
Date:
On Wed, Jul 13, 2005 at 12:53:15PM -0400, Alvaro Herrera wrote:

> > we are developing GNUmed, a medical practice management
> > application running on PostgreSQL (you want your medical
> > data to be hosted by something reliable, don't you ;-)  We
> > are putting out our first release sometime in the next two
> > weeks.
> >
> > The idea is to name the production database "gnumed0.1" for
> > version 0.1 (gnumed0.2 etc for upcoming releases). I do
> > realize the "." may force me to quote the database name in,
> > say, a CREATE DATABASE call.
>
> I doubt you'll have any problems with the tools, but the quoting may
> prove painful.  Why not replace the dot with an underscore? gnumed0_1
Good suggestion. I will try to find a name that a) makes the
version tag unambigous and b) does not require quoting.

My main concern, however, was whether the *approach* is
sound, eg using a separate database name per release or IOW
version. One way would be to use the database name "gnumed"
regardless of release, another way would be to use
"gnumedX_Y" for release X.Y. I wonder whether the latter
approach has any drawbacks people might think of regarding
release management etc.

Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: chosing a database name

From
Karsten Hilbert
Date:
On Wed, Jul 13, 2005 at 01:18:09PM -0400, Berend Tober wrote:

> Or why bother including either? Just use sequential integers, maybe
> left-padded with zeros to make the name the same length for the first
> thousand or so releases?
A good tip, too, thanks. Would solve the ambiguity dilemma, too.

I think we'll go with

 "gnumed_0_1"

for release 0.1 and see what gives. I'm still happy to hear
people speak up and say why I am crazy to do so in the first
place. I guess it's a bit related to my using Python:

 "Explicit is better than implicit ..."

(which doesn't mean overly verbose)

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: chosing a database name

From
Vivek Khera
Date:
On Jul 13, 2005, at 1:18 PM, Berend Tober wrote:

> Or why bother including either? Just use sequential integers, maybe
> left-padded with zeros to make the name the same length for the
> first thousand or so releases?
>
>

I concur with this advice.  Just use a sequence number which happens
to correspond with your software release numbers... or not.  They can
be separate, especially once you get more stable and have more
software updaes than schema updates.

Or you could do something like Pg and require changes only for major
version number releases. :-)

Vivek Khera, Ph.D.
+1-301-869-4449 x806



Attachment

Re: chosing a database name

From
Philip Hallstrom
Date:
>>> we are developing GNUmed, a medical practice management
>>> application running on PostgreSQL (you want your medical
>>> data to be hosted by something reliable, don't you ;-)  We
>>> are putting out our first release sometime in the next two
>>> weeks.
>>>
>>> The idea is to name the production database "gnumed0.1" for
>>> version 0.1 (gnumed0.2 etc for upcoming releases). I do
>>> realize the "." may force me to quote the database name in,
>>> say, a CREATE DATABASE call.
>>
>> I doubt you'll have any problems with the tools, but the quoting may
>> prove painful.  Why not replace the dot with an underscore? gnumed0_1
> Good suggestion. I will try to find a name that a) makes the
> version tag unambigous and b) does not require quoting.
>
> My main concern, however, was whether the *approach* is
> sound, eg using a separate database name per release or IOW
> version. One way would be to use the database name "gnumed"
> regardless of release, another way would be to use
> "gnumedX_Y" for release X.Y. I wonder whether the latter
> approach has any drawbacks people might think of regarding
> release management etc.

The only thing I can think is that if the changes from v1 to v2 don't
touch the schema, then you've got a lot of extra update-work to do that
really isn't necessary.  Doesn't hurt anything though and it gives you a
nice clean way of reverting back a version if necessary.

Re: chosing a database name

From
Karsten Hilbert
Date:
On Wed, Jul 13, 2005 at 01:21:01PM -0700, Philip Hallstrom wrote:

> >My main concern, however, was whether the *approach* is
> >sound, eg using a separate database name per release or IOW
> >version. One way would be to use the database name "gnumed"
> >regardless of release, another way would be to use
> >"gnumedX_Y" for release X.Y. I wonder whether the latter
> >approach has any drawbacks people might think of regarding
> >release management etc.
>
> The only thing I can think is that if the changes from v1 to v2 don't
> touch the schema, then you've got a lot of extra update-work to do that
> really isn't necessary.
Got me !  :-)

> Doesn't hurt anything though and it gives you a
> nice clean way of reverting back a version if necessary.
That's what I hope to achieve. Doesn't free me from backup
before upgrade but still.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: chosing a database name

From
Richard_D_Levine@raytheon.com
Date:

pgsql-general-owner@postgresql.org wrote on 07/13/2005 02:59:02 PM:

> On Wed, Jul 13, 2005 at 12:53:15PM -0400, Alvaro Herrera wrote:
>
> > > we are developing GNUmed, a medical practice management
> > > application running on PostgreSQL (you want your medical
> > > data to be hosted by something reliable, don't you ;-)  We
> > > are putting out our first release sometime in the next two
> > > weeks.
> > >
> > > The idea is to name the production database "gnumed0.1" for
> > > version 0.1 (gnumed0.2 etc for upcoming releases). I do
> > > realize the "." may force me to quote the database name in,
> > > say, a CREATE DATABASE call.
> >
> > I doubt you'll have any problems with the tools, but the quoting may
> > prove painful.  Why not replace the dot with an underscore? gnumed0_1
> Good suggestion. I will try to find a name that a) makes the
> version tag unambigous and b) does not require quoting.
>
> My main concern, however, was whether the *approach* is
> sound, eg using a separate database name per release or IOW
> version. One way would be to use the database name "gnumed"
> regardless of release, another way would be to use
> "gnumedX_Y" for release X.Y. I wonder whether the latter
> approach has any drawbacks people might think of regarding
> release management etc.

I think a better approach is to handle configuration management with a
table in each schema.  Update the schema, update the table.  This works
well with automating database upgrades as well, where upgrades are written
as scripts, and applied in a given order to upgrade a database from release
A to C, or A to X, depending on when it was archived.  A script naming
convention (e.g. numerical) can determine order, and each script can
register in (write a line to) the configuration management table.  This
allows for error analysis, among other things.

Rick

>
> Thanks,
> Karsten
> --
> GPG key ID E4071346 @ wwwkeys.pgp.net
> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly


versioning schema changes was: chosing a database name

From
Karsten Hilbert
Date:
On Wed, Jul 13, 2005 at 03:53:26PM -0500, Richard_D_Levine@raytheon.com wrote:

> I think a better approach is to handle configuration management with a
> table in each schema.  Update the schema, update the table.
We already do that anyways. Our schema scripts have their
CVS version tag embedded in an INSERT statement which
updates the schema revision tracking table with file name
and revision.

From our first release (== CVS release tag) onwards we will
employ the change-script-only technique described in Elein's
latest Tidbits.

I have now added a database revision table which enforces to
have only one single row which holds the "database schema
version". That row holds the md5 hash of the (ordered)
concatenation of the  file name/revision rows in the table
described above. Thereby, when the schema changes, the hash
changes, too. We might then employ a lookup table/function
where the client can match its version against the database
version thereby determining whether it can work with that
database. Of course, any user with sufficient access rights
can manually screw up this construct ...

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: chosing a database name

From
Tim Allen
Date:
Karsten Hilbert wrote:
>
> My main concern, however, was whether the *approach* is
> sound, eg using a separate database name per release or IOW
> version. One way would be to use the database name "gnumed"
> regardless of release, another way would be to use
> "gnumedX_Y" for release X.Y. I wonder whether the latter
> approach has any drawbacks people might think of regarding
> release management etc.

Others have given you some of the advice I would have given. One more
suggestion - does your database fit in just one "schema" in the gnumed
database? If so, then an easy option would be to have all versions of
your software use the same database, but each one has its own unique
schema within that database. This gives you the same ease of reversion
to a previous version, and gives you the added advantage that it's much
easier to write the scripts that update from one version to another,
since they can just operate within the same database, eg selecting data
from one schema and inserting it into another. Your users would then
drop old schemas on whatever basis they feel comfortable with.

What we do in practice is upgrade the database in situ when we upgrade
the software. We use a schema version number to automatically determine
what scripts to run - similar, I think to what Rick Levine was
describing. However, we have no easy way to revert to an old version if
required - so your plan will be better than ours in that regard.

Tim

--
-----------------------------------------------
Tim Allen          tim@proximity.com.au
Proximity Pty Ltd  http://www.proximity.com.au/

Re: chosing a database name

From
Karsten Hilbert
Date:
On Wed, Jul 13, 2005 at 04:18:34PM -0400, Vivek Khera wrote:

> I concur with this advice.  Just use a sequence number which happens
> to correspond with your software release numbers... or not.  They can
> be separate, especially once you get more stable and have more
> software updaes than schema updates.
Truly, words of wisdom. I have added that concept to our
revision handling.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: chosing a database name

From
Karsten Hilbert
Date:
On Thu, Jul 14, 2005 at 11:18:30AM +1000, Tim Allen wrote:

> Others have given you some of the advice I would have given. One more
> suggestion - does your database fit in just one "schema" in the gnumed
> database?
It would, for the time being, "size-wise". However, we have
conceptually separated the data into parts, eg clinical
data, demographic data, reference data, BLOB data.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346