Re: Database design: Storing app defaults

Application defaults go in the application code not in the database (my
opinion).

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

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.

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.

The are many ways of managing applications upgrades, it's an mainly an
application challenge not a database design issue.

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of David
Sent: June 18, 2008 8:03 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Database design: Storing app defaults

Hi list.

If you have a table like this:

table1
- id
- field1
- field2
- field3

table2
- id
- table1_id
- field1
- field2
- field3

table1 & table2 are setup as 1-to-many.

If I want to start providing user-customizable defaults to the
database (ie, we don't want apps to update database schema), is it ok
database design to add a table2 record, with a NULL table1_id field?

In other words, if table1 has no matching table2 record, then the app
will use the table2 record with a NULL table1_id field to get
defaults.

This looks messy however. Is there a better way to do it?

A few other ways I can think of:

1) Have an extra table1 record (with string fields containing
'DEFAULT'), against which the extra table2 record is linked.

2) Have a new table, just for defaults, like this:

table2_defaults
- field1
- field2
- field3

Which is the cleanest way? Is there another method I should use instead?

David.