SQL Design: representing a default value with overrides?
- by Mark Harrison
I need a sparse table which contains a set of "override" values for
another table. I also need to specify the default value for the
items overridden.
For example, if the default value is 17, then foo,bar,baz will have
the values 17,21,17:
table "things" table "xvalue"
name stuff name xval
---- ----- ---- ----
foo ... bar 21
bar ...
baz ...
If I don't care about a FK from xvalue.name - things.name, I could simply
put a "DEFAULT" name:
table "xvalue"
name xval
---- ----
DEFAULT 17
bar 21
But I like having a FK. I could have a separate default table, but it
seems odd to have 2x the number of tables.
table "xvalue_default"
xval
----
17
table "xvalue"
name xval
---- ----
bar 21
I could have a "defaults table"
tablename attributename defaultvalue
xvalue xval 17
but then I run into type issues on defaultvalue.
My operations guys prefer as compact a representation as possible,
so they can most easily see the "diff" or deviations from the
default.
What's the best way to represent this, including the default value? This will be for Oracle 10.2 if that makes a difference.
