SQL Design: representing a default value with overrides?

Posted by Mark Harrison on Stack Overflow See other posts from Stack Overflow or by Mark Harrison
Published on 2010-04-26T23:15:54Z Indexed on 2010/04/26 23:33 UTC
Read the original article Hit count: 285

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.

© Stack Overflow or respective owner

Related posts about sql

Related posts about database-design