Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434

    Table of Default Values

    I have a need to let my users define their own default values.

    To be more specific; I am building a solution whose end goal is to produce some lovely output files. Sadly I am using less-than-clean data and as such some required fields will not have data. Records with missing data in required fields will have the NULLs / blanks populated with configurable default values.

    I can't decide the best way to design this solution.

    Obviously there's the key/value model: defaults(field, value)
    But in a single extract I have a dozen fields that require defaults and I'd be looking at 1 join per default value, so that's 12 extra joins for a single query! I guess the downside here is merely laziness.

    Alternatively we have this model: defaults(field_a, field_b, field_c, field_d, etc)
    Where the table would have a single row containing all the default values required. The obvious downside is that a schema change is required to add a new field and no matter how unlikely will is, it could happen as this solution develops. On the plus side; only 1 join required :s

    What do we reckon is the best solution? Any other options floating around?

    Appreciate the input, cheers!
    Last edited by gvee; 08-23-10 at 05:38.
    George
    Home | Blog

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Hmm, I guess I can use the key/value method along with PIVOT to make my queries easier...
    Code:
    ; WITH defaults AS (
      SELECT 'field_a' As field
           , 1 As value
      UNION ALL SELECT 'field_b', 2
      UNION ALL SELECT 'field_c', 3
      UNION ALL SELECT 'field_d', 4
      UNION ALL SELECT 'field_e', 5
    )
    SELECT *
    FROM   defaults
    PIVOT  (
            Max(value)
            FOR field IN (field_a, field_e)
           ) As p
    Hmmm...
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Can't believe I overlooked the obvious in the key/value model: I would have to use a "generic" (for want of a better word) data type for the value column... This method would work fine assuming there are only defaults of a single data type.

    Damn.
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Although that doesn't require a change to the schema for a user to set a default, it still requires a change to all code (including that CTE) so I would go for the efficient and simple table.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Yeah, I'm starting to dislike the key/value method
    Code:
    ; WITH defaults AS (
      SELECT 'field_a'               As field
           , Convert(sql_variant, 1) As value
           , 'decimal'               As data_type
      UNION ALL SELECT 'field_b', 'abcde'   , 'varchar'
      UNION ALL SELECT 'field_c', 3.05      , 'float'
      UNION ALL SELECT 'field_d', 4         , 'int'
      UNION ALL SELECT 'field_e', '20100101', 'datetime'
    )
    SELECT *
         , Convert(sql_variant, Coalesce(a,b,c,d,value)) As actual_value
    FROM   (
            SELECT *
                 , CASE WHEN data_type = 'datetime' THEN Convert(datetime, Convert(char(8), value)) END As a
                 , CASE WHEN data_type = 'float' THEN Convert(float, value) END As b
                 , CASE WHEN data_type = 'int' THEN Convert(int, value) END As c
                 , CASE WHEN data_type = 'decimal' THEN Convert(decimal(15,5), value) END As d
                 , Convert(varchar(50), value) As e
            FROM   defaults
           ) As x
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Poots, are you suggesting the second method?
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Table with a single row.
    Or multiple single row tables (one table per table you are providing defaults for).
    This is how I have done it for this requirement myself (default to override NULLS in reporting).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gvee View Post
    Yeah, I'm starting to dislike the key/value method
    gee, i wonder why...

    i'd use the single table with customized columns

    and when you need a default value, the join type, of course, will be CROSS JOIN, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote Originally Posted by r937 View Post
    and when you need a default value, the join type, of course, will be CROSS JOIN, right?
    of course Rudy.
    I'm mildy offended that you had to even point this out!














    George
    Home | Blog

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Implemented the single row table option; thanks for the input peeps
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Could I suggest using the key/value method with a twist? One row for the key key owner, another for the key name, a different row for the value, and a surrogate key to tie them together. I officially dub this scheme EEAV, Extreme Entity/Attribute/Value!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •