Re: Database design: Storing app defaults - Mailing list pgsql-general

On Wed, Jun 18, 2008 at 3:24 PM, Jonathan Bond-Caron <jbondc@gmail.com> wrote:
> Application defaults go in the application code not in the database (my
> opinion).

That's fine, until you want the defaults to be customizable, without
making an new app version. That's what my question is about :-)

>
> If you wants user, group, whatever customizable defaults, they belong in the
> database schema i.e. table user_prefs or role_prefs
>

These settings (or more precisely, app-customizable default values)
aren't user or group-specific. Also, I don't want to give apps
permission to update table schema unnecessarily.

A more concrete (toy) example to help clarify what I mean. If this
example doesn't work so well then I'll post another one :-)

employee
 - id
 - name
 - job_id
 - salary (if NULL, then use defaults for the job)
 - benefits_id (if NULL, then use defaults for the job)

job
 - id
 - description
 - default_salary
 - default_benefits_id

benefits
 - id
 - benefit_description

One (of the many) dubious thing with the above schema, is that NULL
employee.salary and employee.benefits_id means that apps should use a
default from somewhere else (but this is not immediately obvious from
the schema alone). So I would probably use a COALESCE and sub-query to
get the salary or benefits in one query.

This isn't exactly the same as my original post (where a 2nd table
uses NULLs to mean 'this is a default record'), but the principle is
similar.

> For your question about "backwards compatible database", in most cases apps
> and databases schemas are upgraded at the same time.
> If you have a requirement that old & new apps have to work on the same
> database schema then don't make database schemas changes that will not be
> backwards compatible / break older apps.

That's the obvious answer :-) But what if you need a feature before
there is time to update all the apps? And how would you design your
tables if you were expecting this to be the norm?

>
> Put those changes on hold until both apps & databases can be upgraded. Some
> solutions which may help you java (hibernate) adds a version column to each
> table, rails adds a schema_info table with database version.
>

I haven't used those before. I've mainly worked with apps which use
SQL directly. More recently I've started working with SQLAlchemy and
Elixir in Python. Do those libraries you mention automatically ignore
records which have an unexpectedly high version number? (And what if
that isn't the correct thing to do in all cases?)

Could you provide links so I can read how those schemes work? (so that
I can look into borrowing their logic for my hand-coded tables &
application SQL).

David.