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
