| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
08-23-10, 04:35
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
|
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 04:38.
|
08-23-10, 04:46
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
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...
|
|
08-23-10, 05:14
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
|
|
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.
|
|
08-23-10, 05:17
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
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:
Quote:
pootle flump
ur codings are working excelent.
|
|
|
08-23-10, 05:29
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
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
|
|
08-23-10, 05:33
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
Poots, are you suggesting the second method?
|
|
08-23-10, 05:42
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
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:
Quote:
pootle flump
ur codings are working excelent.
|
|
|
08-23-10, 05:54
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,516
|
|
Quote:
Originally Posted by gvee
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?
|
|
08-23-10, 06:11
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
Quote:
Originally Posted by r937
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!
|
|
08-23-10, 07:45
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
Implemented the single row table option; thanks for the input peeps 
|
|
08-23-10, 11:45
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,598
|
|
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.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Table of Default Values
