Re: Database design: Storing app defaults

On Wed, Jun 18, 2008 at 9:30 PM, Shane Ambler <pgsql(at)sheeky(dot)biz> wrote:
> David wrote:
>>
>> 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?
>
> Yes - Foreign key constraints will ensure that a value in table1_id exists
> in table1 - it does allow null vales unless you specify that column as NOT
> NULL or UNIQUE

My problem isn't that NULLS are or are allowed. My problem is that the
schema feel a bit unnatual/hackish if you use them in the way I
described. I'm looking for a cleaner, more elegant table schema.

>
>
>>
>> This looks messy however. Is there a better way to do it?
>>
> Sounds back to front to me. table1 would be defaults with table2 user
> defined overrides (I'd also add a user_id column)

That schema was a bit unnatural. See my previous mail in this thread
for a more realistic example.

>
>> 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.
>
> Create a view returning default values when the column is null?
>

This is possible, but there are a few problems (from my pov).

1) How do you make the views writable? (so you can update/delete/insert)

Another poster mentioned triggers, but I don't know how to use those.
Also, adding triggers increases the overall complexity. I'm looking
for a database & app logic/schema which is:

- As simple as possible
- Elegant
- Not hackish

See the Zen of Python for a better idea of what I mean:

http://www.python.org/dev/peps/pep-0020/

Are there any docs on the internet which give guidelines for good db design?

2) You may need to push a lot of logic from app logic (fetching
defaults from various tables depending on the situation) into your
view logic.

You can end up with a monster view, with complex supporting triggers &
stored procedures :-) I'd like to avoid that if possible.

>>
>> Which is the cleanest way? Is there another method I should use instead?
>>
>
> I would think that the app defines default behaviour which it uses if no
> values are stored in the db. The db only holds non-default options.
>

Sometimes your defaults need to be user-configurable. See my previous
post for more info.

David.