Обсуждение: Database design: Storing app defaults
Hi list. 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? David.
On Wed, Jun 18, 2008 at 02:03:05PM +0200, David wrote: > 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. Or have a trigger on table1 transparently fetching defaults when necessary. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Wed, Jun 18, 2008 at 02:03:05PM +0200, David wrote: > 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? > > 2) Have a new table, just for defaults, like this: > > table2_defaults > - field1 > - field2 > - field3 Without knowing anything else about the problem, this is probably what I'd do. The fact that the shape of the table is very similar to the table specific defaults is only incidental. The main reason I'd do this is because it's solving a different problem, that of providing global defaults vs. table specific defaults. I'm not sure how you'd get all the types to match up, but then again this whole scheme of providing defaults seems a little alien to anything I've done before. Sam
On Wed, Jun 18, 2008 at 2:20 PM, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote: > On Wed, Jun 18, 2008 at 02:03:05PM +0200, David wrote: > >> 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. > Or have a trigger on table1 transparently fetching defaults > when necessary. > I'm a n00b when it comes to triggers. Can you point me to an example of this? I'm looking for this kind of logic (I should have clarified this in my original post): 1) App writes some default settings (to table2 or wherever) 2) App writes a record to table1 (but no corresponding table2 record) 3) App later reads table1 record, and automatically gets default values 4) App writes updated defaults to table2 5) App later reads table1 record, and automatically gets updated default values. 6) App writes a table2 record for table1 7) App later reads table1 record, and gets (non-default) values from table2 Usually I do this, by doing a SELECT join between table1 & table2, where the foreign key matches, or table2.table1_id is NONE (or some other condition to pull in the default value record). This has worked for me, but I'm wondering if there is a tidier method for defaults. Abusing NULL values in table2.table_id, to mean 'this is a default values record' seems untidy :-) David.
Application defaults go in the application code not in the database (my opinion). If you wants user, group, whatever customizable defaults, they belong in the database schema i.e. table user_prefs or role_prefs 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. 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. The are many ways of managing applications upgrades, it's an mainly an application challenge not a database design issue. -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of David Sent: June 18, 2008 8:03 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Database design: Storing app defaults Hi list. 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? David. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
David wrote: > Hi list. > > 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? Yes - Foreign key constraints will ensure that a value in table1_id exists in table1 - it does allow null vales unless you specify that column as NOT NULL or UNIQUE > > This looks messy however. Is there a better way to do it? > Sounds back to front to me. table1 would be defaults with table2 user defined overrides (I'd also add a user_id column) > 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. Create a view returning default values when the column is null? > > Which is the cleanest way? Is there another method I should use instead? > I would think that the app defines default behaviour which it uses if no values are stored in the db. The db only holds non-default options. I would think that one table is sufficient for the scenario you describe. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz
On Wed, Jun 18, 2008 at 3:24 PM, Jonathan Bond-Caron <jbondc@gmail.com> 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.
On Wed, Jun 18, 2008 at 9:30 PM, Shane Ambler <pgsql@sheeky.biz> wrote: > David wrote: >> >> Hi list. >> >> 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? > > Yes - Foreign key constraints will ensure that a value in table1_id exists > in table1 - it does allow null vales unless you specify that column as NOT > NULL or UNIQUE My problem isn't that NULLS are or are allowed. My problem is that the schema feel a bit unnatual/hackish if you use them in the way I described. I'm looking for a cleaner, more elegant table schema. > > >> >> This looks messy however. Is there a better way to do it? >> > Sounds back to front to me. table1 would be defaults with table2 user > defined overrides (I'd also add a user_id column) That schema was a bit unnatural. See my previous mail in this thread for a more realistic example. > >> 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. > > Create a view returning default values when the column is null? > This is possible, but there are a few problems (from my pov). 1) How do you make the views writable? (so you can update/delete/insert) Another poster mentioned triggers, but I don't know how to use those. Also, adding triggers increases the overall complexity. I'm looking for a database & app logic/schema which is: - As simple as possible - Elegant - Not hackish See the Zen of Python for a better idea of what I mean: http://www.python.org/dev/peps/pep-0020/ Are there any docs on the internet which give guidelines for good db design? 2) You may need to push a lot of logic from app logic (fetching defaults from various tables depending on the situation) into your view logic. You can end up with a monster view, with complex supporting triggers & stored procedures :-) I'd like to avoid that if possible. >> >> Which is the cleanest way? Is there another method I should use instead? >> > > I would think that the app defines default behaviour which it uses if no > values are stored in the db. The db only holds non-default options. > Sometimes your defaults need to be user-configurable. See my previous post for more info. David.
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