Re: Database design: Storing app defaults

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