From: | Shane Ambler <pgsql(at)Sheeky(dot)Biz> |
---|---|
To: | David <wizzardx(at)gmail(dot)com> |
Cc: | Jonathan Bond-Caron <jbondc(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Database design: Storing app defaults |
Date: | 2008-06-19 18:55:18 |
Message-ID: | 485AAB96.5060606@Sheeky.Biz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Shane Ambler | 2008-06-19 19:10:59 | Re: Database design: Backwards-compatible field addition |
Previous Message | Michael Shulman | 2008-06-19 18:40:26 | Re: inserting to a multi-table view |