SQL Design: representing a default value with overrides?

I need a spars开发者_C百科e 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.

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.

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.

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.

Does this do what you intend?

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

First, use the default value setting for the column definitions.

If for some reason you can't do that, do one of several other things:

1. create a duplicate of your table structure that contains one record that holds your default values. On insert, merge the data in that table with the data you were passed. (hardest of the 3).
2. write a trigger and handle this there
3. if all inserts/updates are down via stored procs (my normal recommendation) then handle it in your proc.
4. if you are creating the sql in code, see the very first line of this answer.

I can't really think of a situation in which I would store the defaults in a name/value pair table. Too hard to work with for this.