SQL Design: representing a default value with overrides?

Question

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.

Answer 1

How about using a view? In the view you do an outer join to table xvalue and then use the coalesce function to return the default value if xval is null.

Answer 2

We can define default values for columns when we create the tables ...

SQL> create table t34 (
  2    id number not null
  3    , col1 varchar2(30) default user
  4    , col2 date default sysdate
  5    , col3 char(1) default 'N' )
  6  /

Table created.

SQL>

These values get applied automatically if we don't specify anything:

SQL> insert into t34
  2      (id)
  3  values
  4      (1)
  5  /

1 row created.

SQL>

Alternatively if we want to specify some values but not others we can use the DEFAULT keyword

SQL> insert into t34
  2  values
  3      (2, null, default, 'Y')
  4  /

1 row created.

SQL> select * from t34
  2  /

        ID COL1                           COL2      C
---------- ------------------------------ --------- -
         1 APC                            27-APR-10 N
         2                                27-APR-10 Y

SQL>

We can find the default values in the data dictionary view USER_TAB_COLUMNS. Unfortunately, DATA_DEFAULT is in the despicable LONG datatype, which reduces its usability.

SQL> select column_name, data_default
  2  from user_tab_columns
  3  where table_name = 'T34'
  4  /

COLUMN_NAME                    DATA_DEFAULT
------------------------------ ------------------------------
ID
COL1                           user
COL2                           sysdate
COL3                           'N'

SQL>

** override values **

Storing a set of override values - alternate defaults - is trickier. I'm afraid the foreign key you floated is a non-starter, because we cannot reference the data dictionary's metadata in our application data.

I'm not entirely clear what business problem you're trying to solve, so it is a little difficult to suggest alternatives. But most approaches will probably require dynamic SQL.

Answer 3

I believe a trigger is the best way to handle complex business rules like this. Just make sure you set it up (and test it) to work when someone inserts or updates multiple rows in one action.

Answer 4

Does this do what you intend?

SELECT TABLE_NAME, STUFF, NVL(XVAL, 17) XVAL
FROM THINGS, XVALUE
WHERE THINGS.TABLE_NAME(+) = XVALUE.TABLE_NAME