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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of David Sent: June 18, 2008 8:03 AM To: pgsql-general@postgresql.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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
