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: 359
        
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