On Wed, Jun 18, 2008 at 9:30 PM, Shane Ambler <pgsql@xxxxxxxxxx> 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.
