On Wed, Jun 18, 2008 at 3:24 PM, Jonathan Bond-Caron <jbondc@xxxxxxxxx> 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.
