Re: Database design: Storing app defaults

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

>
> 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)

> 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?

>
> 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.

I would think that one table is sufficient for the scenario you describe.

--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz