Thread: Database design: Storing app defaults

Database design: Storing app defaults

From
David
Date:
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.

Re: Database design: Storing app defaults

From
Karsten Hilbert
Date:
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.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: Database design: Storing app defaults

From
Sam Mason
Date:
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?
>
> 2) Have a new table, just for defaults, like this:
>
> table2_defaults
>  - field1
>  - field2
>  - field3

Without knowing anything else about the problem, this is probably what
I'd do.  The fact that the shape of the table is very similar to the
table specific defaults is only incidental.  The main reason I'd do this
is because it's solving a different problem, that of providing global
defaults vs. table specific defaults.

I'm not sure how you'd get all the types to match up, but then again
this whole scheme of providing defaults seems a little alien to anything
I've done before.


  Sam

Re: Database design: Storing app defaults

From
David
Date:
On Wed, Jun 18, 2008 at 2:20 PM, Karsten Hilbert
<Karsten.Hilbert@gmx.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.

Re: Database design: Storing app defaults

From
"Jonathan Bond-Caron"
Date:
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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] 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


Re: Database design: Storing app defaults

From
Shane Ambler
Date:
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

Re: Database design: Storing app defaults

From
David
Date:
On Wed, Jun 18, 2008 at 3:24 PM, Jonathan Bond-Caron <jbondc@gmail.com> wrote:
> Application defaults go in the application code not in the database (my
> opinion).

That's fine, until you want the defaults to be customizable, without
making an new app version. That's what my question is about :-)

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

These settings (or more precisely, app-customizable default values)
aren't user or group-specific. Also, I don't want to give apps
permission to update table schema unnecessarily.

A more concrete (toy) example to help clarify what I mean. If this
example doesn't work so well then I'll post another one :-)

employee
 - id
 - name
 - job_id
 - salary (if NULL, then use defaults for the job)
 - benefits_id (if NULL, then use defaults for the job)

job
 - id
 - description
 - default_salary
 - default_benefits_id

benefits
 - id
 - benefit_description

One (of the many) dubious thing with the above schema, is that NULL
employee.salary and employee.benefits_id means that apps should use a
default from somewhere else (but this is not immediately obvious from
the schema alone). So I would probably use a COALESCE and sub-query to
get the salary or benefits in one query.

This isn't exactly the same as my original post (where a 2nd table
uses NULLs to mean 'this is a default record'), but the principle is
similar.

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

That's the obvious answer :-) But what if you need a feature before
there is time to update all the apps? And how would you design your
tables if you were expecting this to be the norm?

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

I haven't used those before. I've mainly worked with apps which use
SQL directly. More recently I've started working with SQLAlchemy and
Elixir in Python. Do those libraries you mention automatically ignore
records which have an unexpectedly high version number? (And what if
that isn't the correct thing to do in all cases?)

Could you provide links so I can read how those schemes work? (so that
I can look into borrowing their logic for my hand-coded tables &
application SQL).

David.

Re: Database design: Storing app defaults

From
David
Date:
On Wed, Jun 18, 2008 at 9:30 PM, Shane Ambler <pgsql@sheeky.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.

Re: Database design: Storing app defaults

From
Shane Ambler
Date:
David wrote:

> One (of the many) dubious thing with the above schema, is that NULL
> employee.salary and employee.benefits_id means that apps should use a
> default from somewhere else (but this is not immediately obvious from
> the schema alone). So I would probably use a COALESCE and sub-query to
> get the salary or benefits in one query.
>

I guess part of it depends on your programming environment and personal
preference. Personally I would have a view containing the coalesces etc
and have my app select from that and then insert/update to the person
table directly. Some environments like access would make this awkward as
they tend to base all actions on a specified table not program generated
sql for an action.

>
>> 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.
>
> That's the obvious answer :-) But what if you need a feature before
> there is time to update all the apps? And how would you design your
> tables if you were expecting this to be the norm?
>
Dropping tables or columns will break any existing app. Adding columns
will only break old apps that request every column and try to process
all of them. As I mentioned before when you use SELECT col1,col2... then
old apps will continue to run until you drop a column they depend on.

To change the use of an existing column can have a similar affect. A
little thought before you make changes to consider how the change will
affect existing apps can prevent these issues.




--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz