Re: Database design: Storing app defaults

On Wed, Jun 18, 2008 at 2:20 PM, Karsten Hilbert
<Karsten(dot)Hilbert(at)gmx(dot)net> wrote:
> On Wed, Jun 18, 2008 at 02:03:05PM +0200, David wrote:
>
>> 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.
> Or have a trigger on table1 transparently fetching defaults
> when necessary.
>

I'm a n00b when it comes to triggers. Can you point me to an example of this?

I'm looking for this kind of logic (I should have clarified this in my
original post):

1) App writes some default settings (to table2 or wherever)

2) App writes a record to table1 (but no corresponding table2 record)

3) App later reads table1 record, and automatically gets default values

4) App writes updated defaults to table2

5) App later reads table1 record, and automatically gets updated default values.

6) App writes a table2 record for table1

7) App later reads table1 record, and gets (non-default) values from table2

Usually I do this, by doing a SELECT join between table1 & table2,
where the foreign key matches, or table2.table1_id is NONE (or some
other condition to pull in the default value record).

This has worked for me, but I'm wondering if there is a tidier method
for defaults. Abusing NULL values in table2.table_id, to mean 'this is
a default values record' seems untidy :-)

David.