D
David
Hi list.
I have a few database-related questions. These aren't Python-specific
questions, but some of my apps which use (or will use) these tables
are in Python
Let me know if I should ask this on a different
list.
Question 1: Storing app defaults.
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?
Question 2: Backwards-compatible field addition
If you have an existing table, and apps which use it, then how do you
add new fields to the table (for new apps), but which might affect
existing apps negatively?
eg: I start with a table like this:
table1
- id
- field1
- field2
- field3
Later, I want to add a use case, where there is new behaviour, if a
new field is set in the table, like this:
table1
- id
- field1
- field2
- field3
- field4 - NEW - if unset, do old behaviour. if set, do something else
The problem is, that existing apps (besides your new app) won't know
about field4, so they will keep using the old behaviour for new
records (where field4 is set), which you don't want.
The most obvious thing to do is to update all apps using table1, so
they also check the value of field4.
Is there another, more backwards-compatible way to add field4 for the
new behaviour, without having to update all the apps?
A few things I can think of:
1) table1 becomes a view of an updated table, with a 'WHERE field4 IS
NULL' clause.
Problem with this is that some RDBMS (Postgresql specifically) don't
let you run update statements on views.
2) Apps use stored procedures for all database access.
Maybe ok for new apps, not so much for existing apps which use regular SQL.
3) All apps use the same library for accessing database
Then you update the library and all apps automagically know about the
extra field. Again, maybe ok for new apps, not so much for existing
apps.
4) Make a new table (copy of the old one), with the extra field.
Then your app checks both tables, or just the new one if applicable.
This can work, but you may end up with a lot of app-specific tables,
where the main difference between the tables is extra columns, and
which apps use the tables.
5) Have a 'db version' column in the table. Older apps only operate on
records at or before the version the programmer knew about at the
time.
This can work, but it seems like a very non-standard, hackish way of
designing database tables. Also it's a pain for all apps to have to
hardcode a db version number.
6) Find a clever way to use table inheritance
I haven't thought it through, but here are some docs on the subject:
http://www.postgresql.org/docs/8.1/static/ddl-inherit.html
Any suggestions?
Question 3: Temporal databases
http://en.wikipedia.org/wiki/Temporal_database
I haven't used them before, but I like the idea of never
deleting/updating records so you have a complete history (a bit like
source code version control).
How well do temporal databases work? Do RDBMS (like Postgresql) need
add-ons to make it effective, or can you just add extra temporal
columns to all your tables and add them to your app queries? Does this
increase app complexity and increase server load a lot?
Are there Python libraries which simplify this? (eg: add-ons for
Elixir or SQLAlchemy).
Or should apps all implement their own 'temporal data access' module,
which transparently uses the current date & time until queried for
historical data?
David.
I have a few database-related questions. These aren't Python-specific
questions, but some of my apps which use (or will use) these tables
are in Python
Let me know if I should ask this on a differentlist.
Question 1: Storing app defaults.
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?
Question 2: Backwards-compatible field addition
If you have an existing table, and apps which use it, then how do you
add new fields to the table (for new apps), but which might affect
existing apps negatively?
eg: I start with a table like this:
table1
- id
- field1
- field2
- field3
Later, I want to add a use case, where there is new behaviour, if a
new field is set in the table, like this:
table1
- id
- field1
- field2
- field3
- field4 - NEW - if unset, do old behaviour. if set, do something else
The problem is, that existing apps (besides your new app) won't know
about field4, so they will keep using the old behaviour for new
records (where field4 is set), which you don't want.
The most obvious thing to do is to update all apps using table1, so
they also check the value of field4.
Is there another, more backwards-compatible way to add field4 for the
new behaviour, without having to update all the apps?
A few things I can think of:
1) table1 becomes a view of an updated table, with a 'WHERE field4 IS
NULL' clause.
Problem with this is that some RDBMS (Postgresql specifically) don't
let you run update statements on views.
2) Apps use stored procedures for all database access.
Maybe ok for new apps, not so much for existing apps which use regular SQL.
3) All apps use the same library for accessing database
Then you update the library and all apps automagically know about the
extra field. Again, maybe ok for new apps, not so much for existing
apps.
4) Make a new table (copy of the old one), with the extra field.
Then your app checks both tables, or just the new one if applicable.
This can work, but you may end up with a lot of app-specific tables,
where the main difference between the tables is extra columns, and
which apps use the tables.
5) Have a 'db version' column in the table. Older apps only operate on
records at or before the version the programmer knew about at the
time.
This can work, but it seems like a very non-standard, hackish way of
designing database tables. Also it's a pain for all apps to have to
hardcode a db version number.
6) Find a clever way to use table inheritance
I haven't thought it through, but here are some docs on the subject:
http://www.postgresql.org/docs/8.1/static/ddl-inherit.html
Any suggestions?
Question 3: Temporal databases
http://en.wikipedia.org/wiki/Temporal_database
I haven't used them before, but I like the idea of never
deleting/updating records so you have a complete history (a bit like
source code version control).
How well do temporal databases work? Do RDBMS (like Postgresql) need
add-ons to make it effective, or can you just add extra temporal
columns to all your tables and add them to your app queries? Does this
increase app complexity and increase server load a lot?
Are there Python libraries which simplify this? (eg: add-ons for
Elixir or SQLAlchemy).
Or should apps all implement their own 'temporal data access' module,
which transparently uses the current date & time until queried for
historical data?
David.