March 17, 2011 at 11:39 am
A classic example where you encounter typeless columns is the vertical all-purpose table that fits anything.
referral_id datatype_id data_value
100 4 9/3/2003
100 2 12.75
100 9 Y
101 4 Jan 15, 2007
101 2 $10.50
101 9 Y
Also, I see this thing a lot in datawarehouses where the data model is designed by someone who knows the business, but their expertise not really data modeling. Or perhaps a junior developer looks at the requirements for a report, which state that invoice date must be formatted as MM/DD/YYYY, and he confuses a data presentation requirement for a data storage requirement.
I freely admit to having done that! I didn't go very far with it and it never made it to production. I realized that I was going to have to write a database management system if I was going to do it right. I looked up from the keyboard, realized that I already had that system, and started deleting tables. Sadly, I do still have that one lookup table to rule them all in production. I didn't see the error of my ways until it was too late and nobody has ever authorized the necessary rewrite.
March 18, 2011 at 12:43 pm
Eric M Russell (3/17/2011)
If an application must use VarDate columns, then they should at least code it using ISO standard format YYYYMMDD...
But if you're going to do that, wouldn't it be better to use an int rather than a char(8)? It only takes half the space, and you can't put any non-numeric values in it.
CREATE TABLE MyTable
(
MyDate int not null check
(
(MyDate % 100) Between 1 And 31 -- Day
And
((MyDate / 100) % 100) Between 1 And 12 -- Month
And
(MyDate / 10000) Between 1900 And 2100 -- Year
-- or whatever range you require
)
)
The only issue might be that it takes one extra step to convert to or from a real date. But if you're using real dates, why not store them as dates in the first place?!
March 18, 2011 at 1:16 pm
richardd (3/18/2011)
Eric M Russell (3/17/2011)
If an application must use VarDate columns, then they should at least code it using ISO standard format YYYYMMDD...
But if you're using real dates, why not store them as dates in the first place?!
I would never design a table containing dates in a varchar column. I would only suggest ISO formatted dates in a situation where a legacy application is already using VarDate columns, but they are doing it inconsistently and for whatever reason want to keep the dates as varchars. There are some situations (like the vertical table I mentioned in an earlier post) where the application is containing dates, currency, text descriptions, booleans, and xml inside the same varchar column, so just changing it's datatype to a real Date datatype is not an option without serious retrofitting of the database and application.
There are also some fringe situations where an application needs to store dates from the distant past that won't fit in a DateTime datatype. For example, try casting '1750-01-01' as a DateTime and you'll get an "out-of-range datetime value" error. Banks and governments (particularly in Europe) may routinely have a need to record transactions reaching back that far. Of course, SQL Server 2008 has extended datatypes for containing dates.
So, when considering the option of a mass update to convert all "date" values in a VarChar column into a standard format, only then I'd suggest using ISO YYYYMMDD.
Also when extracting data from the database to something like text files or Excel, I'd suggest an ISO format.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply