When normalization goes wrong. Horribly.
-
Now I don't pretend to know it all, so when I have questions I'm prepared to ask someone who might already have encountered a similar problem and listen to their suggestions. Unlike the muppets I work for. Today's project saw me looking at the feasibility of porting an existing Access database to SQL Server. Easy, thought I, but then I actually saw the "database" in question. To give you just one example of the horrors I'm looking at: The "Machine" table has a "Date Added" column. Now, if you're like me, you'd expect this to be a DateTime column. So I was surprised to see this as a numeric field featuring values such as 1,2,3,4 etc. I was even more surprised to see that this field is related to a "DateAdded" table, whose sole purpose is to give index values to dates. It currently has a seperate entry for each and every day up to 31/12/2015. :wtf:
"It was the day before today.... I remember it like it was yesterday." -Moleman
-
Now I don't pretend to know it all, so when I have questions I'm prepared to ask someone who might already have encountered a similar problem and listen to their suggestions. Unlike the muppets I work for. Today's project saw me looking at the feasibility of porting an existing Access database to SQL Server. Easy, thought I, but then I actually saw the "database" in question. To give you just one example of the horrors I'm looking at: The "Machine" table has a "Date Added" column. Now, if you're like me, you'd expect this to be a DateTime column. So I was surprised to see this as a numeric field featuring values such as 1,2,3,4 etc. I was even more surprised to see that this field is related to a "DateAdded" table, whose sole purpose is to give index values to dates. It currently has a seperate entry for each and every day up to 31/12/2015. :wtf:
"It was the day before today.... I remember it like it was yesterday." -Moleman
That's abnormalization right there. I assume this is some weird Y2.016K issue here.
Please visit http://www.readytogiveup.com/ and do something special today. Deja View - the feeling that you've seen this post before.
-
Now I don't pretend to know it all, so when I have questions I'm prepared to ask someone who might already have encountered a similar problem and listen to their suggestions. Unlike the muppets I work for. Today's project saw me looking at the feasibility of porting an existing Access database to SQL Server. Easy, thought I, but then I actually saw the "database" in question. To give you just one example of the horrors I'm looking at: The "Machine" table has a "Date Added" column. Now, if you're like me, you'd expect this to be a DateTime column. So I was surprised to see this as a numeric field featuring values such as 1,2,3,4 etc. I was even more surprised to see that this field is related to a "DateAdded" table, whose sole purpose is to give index values to dates. It currently has a seperate entry for each and every day up to 31/12/2015. :wtf:
"It was the day before today.... I remember it like it was yesterday." -Moleman
martin_hughes wrote:
:wtf:
Yeah, totally. What was the reasoning behind it?
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
-
Now I don't pretend to know it all, so when I have questions I'm prepared to ask someone who might already have encountered a similar problem and listen to their suggestions. Unlike the muppets I work for. Today's project saw me looking at the feasibility of porting an existing Access database to SQL Server. Easy, thought I, but then I actually saw the "database" in question. To give you just one example of the horrors I'm looking at: The "Machine" table has a "Date Added" column. Now, if you're like me, you'd expect this to be a DateTime column. So I was surprised to see this as a numeric field featuring values such as 1,2,3,4 etc. I was even more surprised to see that this field is related to a "DateAdded" table, whose sole purpose is to give index values to dates. It currently has a seperate entry for each and every day up to 31/12/2015. :wtf:
"It was the day before today.... I remember it like it was yesterday." -Moleman
That's similar to an Excel-based puddle-of-crap I have to support now. Each worksheet has entries for dates up to early this September, there's a formula for determining which row to work on for each date. The problem is that I don't think I can add more rows, so to extend the supported timeframe I'll have to delete the oldest data. Be glad you are at least dealing with a ::cough:: database ::cough::. "Always look on the bright side of life." -- Monty Python
-
Now I don't pretend to know it all, so when I have questions I'm prepared to ask someone who might already have encountered a similar problem and listen to their suggestions. Unlike the muppets I work for. Today's project saw me looking at the feasibility of porting an existing Access database to SQL Server. Easy, thought I, but then I actually saw the "database" in question. To give you just one example of the horrors I'm looking at: The "Machine" table has a "Date Added" column. Now, if you're like me, you'd expect this to be a DateTime column. So I was surprised to see this as a numeric field featuring values such as 1,2,3,4 etc. I was even more surprised to see that this field is related to a "DateAdded" table, whose sole purpose is to give index values to dates. It currently has a seperate entry for each and every day up to 31/12/2015. :wtf:
"It was the day before today.... I remember it like it was yesterday." -Moleman
Is there a DateUpdated table too, or do the two columns share a table, Gasp! You need to fix any such rampant denormalization before you migrate the "database"
I can imagine the sinking feeling one would have after ordering my book, only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
-
Is there a DateUpdated table too, or do the two columns share a table, Gasp! You need to fix any such rampant denormalization before you migrate the "database"
I can imagine the sinking feeling one would have after ordering my book, only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
Andy Brummer wrote:
fix any such rampant denormalization before you migrate the "database"
Right on. Anyone up for denormalizing? :rolleyes:
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
-
martin_hughes wrote:
:wtf:
Yeah, totally. What was the reasoning behind it?
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
I shall endeavour to find out... but I fear the answer :-D
"It was the day before today.... I remember it like it was yesterday." -Moleman
-
Is there a DateUpdated table too, or do the two columns share a table, Gasp! You need to fix any such rampant denormalization before you migrate the "database"
I can imagine the sinking feeling one would have after ordering my book, only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
Andy Brummer wrote:
You need to fix any such rampant denormalization before you migrate the "database"
Having spent a couple of hours ruminating, I'm thinking about returing an "unfeasible" on this feasability report :)
"It was the day before today.... I remember it like it was yesterday." -Moleman
-
Now I don't pretend to know it all, so when I have questions I'm prepared to ask someone who might already have encountered a similar problem and listen to their suggestions. Unlike the muppets I work for. Today's project saw me looking at the feasibility of porting an existing Access database to SQL Server. Easy, thought I, but then I actually saw the "database" in question. To give you just one example of the horrors I'm looking at: The "Machine" table has a "Date Added" column. Now, if you're like me, you'd expect this to be a DateTime column. So I was surprised to see this as a numeric field featuring values such as 1,2,3,4 etc. I was even more surprised to see that this field is related to a "DateAdded" table, whose sole purpose is to give index values to dates. It currently has a seperate entry for each and every day up to 31/12/2015. :wtf:
"It was the day before today.... I remember it like it was yesterday." -Moleman
TheDailyWTF taught me that a "Date" table as such is not uncommon in business applications. Since it's expensive to calculate holidays, business days, etc. they are calculated upfront and put into a date table that aids queries such as "next business day after" (maybe through a trigger when adding a previously unknown date, or through a script generating all dates up to 2015).
We are a big screwed up dysfunctional psychotic happy family - some more screwed up, others more happy, but everybody's psychotic joint venture definition of CP
My first real C# project | Linkify!|FoldWithUs! | sighist -
TheDailyWTF taught me that a "Date" table as such is not uncommon in business applications. Since it's expensive to calculate holidays, business days, etc. they are calculated upfront and put into a date table that aids queries such as "next business day after" (maybe through a trigger when adding a previously unknown date, or through a script generating all dates up to 2015).
We are a big screwed up dysfunctional psychotic happy family - some more screwed up, others more happy, but everybody's psychotic joint venture definition of CP
My first real C# project | Linkify!|FoldWithUs! | sighistYikes! Although I can see a specific reason for adding a calendar of company holidays, what you've found out scares me silly! Afterall, I might encounter more of this shite in the years to come! :D
"It was the day before today.... I remember it like it was yesterday." -Moleman
-
Yikes! Although I can see a specific reason for adding a calendar of company holidays, what you've found out scares me silly! Afterall, I might encounter more of this shite in the years to come! :D
"It was the day before today.... I remember it like it was yesterday." -Moleman
Actually, I found the most interesting exercise in reading TDWTF is reading the comments until someone comes up with a perfectly possible situation where the horrid solution is the one/usual/only/common way to go.
martin_hughes wrote:
Afterall, I might encounter more of this shite in the years to come!
Love waht you can't change :)
We are a big screwed up dysfunctional psychotic happy family - some more screwed up, others more happy, but everybody's psychotic joint venture definition of CP
My first real C# project | Linkify!|FoldWithUs! | sighist -
Yikes! Although I can see a specific reason for adding a calendar of company holidays, what you've found out scares me silly! Afterall, I might encounter more of this shite in the years to come! :D
"It was the day before today.... I remember it like it was yesterday." -Moleman
martin_hughes wrote:
Yikes!
Exactly what I thought after reading the post by peterchen. People must think it is better to have a lookup table rather than computing the dates...
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
-
Yikes! Although I can see a specific reason for adding a calendar of company holidays, what you've found out scares me silly! Afterall, I might encounter more of this shite in the years to come! :D
"It was the day before today.... I remember it like it was yesterday." -Moleman
Oh, well that's alright then. But the keys should be GUIDs and there should be additional columns to indicate whether it's a government or religious holiday (and if religious, then which) and all sorts of things like that... Sounds like my NumberAttribute class (or whatever the name is). :-D
-
TheDailyWTF taught me that a "Date" table as such is not uncommon in business applications. Since it's expensive to calculate holidays, business days, etc. they are calculated upfront and put into a date table that aids queries such as "next business day after" (maybe through a trigger when adding a previously unknown date, or through a script generating all dates up to 2015).
We are a big screwed up dysfunctional psychotic happy family - some more screwed up, others more happy, but everybody's psychotic joint venture definition of CP
My first real C# project | Linkify!|FoldWithUs! | sighistA Date table is daft. An Exclusive set is easier to manage than Inclusive set. Better off having a table with all the holidays in it, and manage the weekend checks in code. But i don't know the caveats. I'm sure dumping every single date from now till the end of time into a table is not an efficient way of doing it.
------------------------------- Carrier Bags - 21st Century Tumbleweed.
-
Andy Brummer wrote:
You need to fix any such rampant denormalization before you migrate the "database"
Having spent a couple of hours ruminating, I'm thinking about returing an "unfeasible" on this feasability report :)
"It was the day before today.... I remember it like it was yesterday." -Moleman
Have you thought of beginning the report with the phrase "Oh my God! What idiot..."??
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
Andy Brummer wrote:
You need to fix any such rampant denormalization before you migrate the "database"
Having spent a couple of hours ruminating, I'm thinking about returing an "unfeasible" on this feasability report :)
"It was the day before today.... I remember it like it was yesterday." -Moleman
-
A Date table is daft. An Exclusive set is easier to manage than Inclusive set. Better off having a table with all the holidays in it, and manage the weekend checks in code. But i don't know the caveats. I'm sure dumping every single date from now till the end of time into a table is not an efficient way of doing it.
------------------------------- Carrier Bags - 21st Century Tumbleweed.
Hell I just rewrote a function I found that was calculating a Spot Date (2 Working days forwrd from the Deal date in FX trading). It worked on an Exclusive set as you say, but instead of getting all of the holidays for the specific currencies (of which there is always 2 and always known in advance) and working with that. It looped adding a day until it did not get a count > 0 from an SQL statement like this and it is a weekday. Select count(CurrencyId) from Holiday Where holidayDate = '2007/01/01' where it changed the date forward. To work out one spot date over the christmas period it would open, query close anything up to 4 or 5 times per confirmation. Crazy I tell ya. You will be pleased to note that it no longer does this ;-)
-
Have you thought of beginning the report with the phrase "Oh my God! What idiot..."??
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007Dave Kreskowiak wrote:
Have you thought of beginning the report with the phrase "Oh my God! What idiot..."?
:laugh: I am sure that would go over well with some PHB management wienie..
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
-
Dave Kreskowiak wrote:
Have you thought of beginning the report with the phrase "Oh my God! What idiot..."?
:laugh: I am sure that would go over well with some PHB management wienie..
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
Paul Conrad wrote:
wienie..
The key word being "wienie". If he/she's a wienie, then they have no sense of humor and I would have to leave the company because of this personal condition, which I find hostile to my own creativity and motivation.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
Paul Conrad wrote:
wienie..
The key word being "wienie". If he/she's a wienie, then they have no sense of humor and I would have to leave the company because of this personal condition, which I find hostile to my own creativity and motivation.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007Dave Kreskowiak wrote:
If he/she's a wienie, then they have no sense of humor and I would have to leave the company because of this personal condition, which I find hostile to my own creativity and motivation.
I hear ya. I cannot imagine going through life without having a daily hearty laugh. I usually denote those kind of laughs in these forums with :laugh::laugh::laugh:
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus