Search Postgresql Archives

Re: Database design: Storing app defaults

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux