Ugly databases
-
I think I've ranted about my companies databases before, but seriously, why on earth would you create identical tables to hold data separately for each department. It's so denormalised it actually hurts. e.g. Say we have a products table, that holds all the products we make. There will be a Products1 table for department 1, a Products2 table for department 2, a Products3 table for department 3. (We have 8 departments!). Each of the products table holds all of the products that department makes. Most departments have some crossover, so there are loads of duplicated entries.
Simon
I maintain a database and application that stores an extremely large amount of historical trading information. I have trades going back to the prior millenium, in fact. :) However most of the daily operations that occur, only require dated trades from within the last 12 to 24 months. So to improve efficiency, there are two trades tables. One is called historical trades and the other is called current trades. The tables are identical and there are database procedures that allow trades to be moved from one table to another depending upon whether a business function requires it or not. So while some of the previously mentioned examples are definitely ugly and present unique difficulties in maintaining, full and complete normalization is not an end that always justifies itself. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
-
I think I've ranted about my companies databases before, but seriously, why on earth would you create identical tables to hold data separately for each department. It's so denormalised it actually hurts. e.g. Say we have a products table, that holds all the products we make. There will be a Products1 table for department 1, a Products2 table for department 2, a Products3 table for department 3. (We have 8 departments!). Each of the products table holds all of the products that department makes. Most departments have some crossover, so there are loads of duplicated entries.
Simon
Simon Stevens wrote:
It's so denormalised it actually hurts
Denormalized, demoralized, what's the difference?
Software Zen:
delete this; -
Well, to cut a long story short. The database has over 100,000 tables. It's the back end of some MRP system that is rather antiquated. The IT department think creating new tables is the answer to every problem. replacing it is not an option at the moment unfortunately.
Simon
Simon Stevens wrote:
The database has over 100,000 tables.
i wouldnt want to debug anything connected to that... :~
Harvey Saayman - South Africa Junior Developer .Net, C#, SQL
you.suck = (you.Passion != Programming & you.Occupation == jobTitles.Programmer)1000100 1101111 1100101 1110011 100000 1110100 1101000 1101001 1110011 100000 1101101 1100101 1100001 1101110 100000 1101001 1101101 100000 1100001 100000 1100111 1100101 1100101 1101011 111111 -
I maintain a database and application that stores an extremely large amount of historical trading information. I have trades going back to the prior millenium, in fact. :) However most of the daily operations that occur, only require dated trades from within the last 12 to 24 months. So to improve efficiency, there are two trades tables. One is called historical trades and the other is called current trades. The tables are identical and there are database procedures that allow trades to be moved from one table to another depending upon whether a business function requires it or not. So while some of the previously mentioned examples are definitely ugly and present unique difficulties in maintaining, full and complete normalization is not an end that always justifies itself. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
I have to ask... why not have a separate Database to hold archive data instead of just a table (or however many tables store historical data)? Much easier to handle, IMHO, especially when it comes to space requirements -- the historical data can be moved to nearline or offline storage without affecting the production (12-24 month) data. Although I guess your system is simpler to manage... :) Peace!
-=- James
Please rate this message - let me know if I helped or not! * * *If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
See DeleteFXPFiles -
Simon Stevens wrote:
It's so denormalised it actually hurts
Denormalized, demoralized, what's the difference?
Software Zen:
delete this;Well, if you're not a developer (i.e., management), one of those is hidden by a SEP field. For those who don't know, an SEP field is a "Somebody Else's Problem" field. It hides items from sight because it's not your problem. EDIT: misspelling
Have faith in yourself; amateurs built the Ark, professionals built the Titanic.
-
I think I've ranted about my companies databases before, but seriously, why on earth would you create identical tables to hold data separately for each department. It's so denormalised it actually hurts. e.g. Say we have a products table, that holds all the products we make. There will be a Products1 table for department 1, a Products2 table for department 2, a Products3 table for department 3. (We have 8 departments!). Each of the products table holds all of the products that department makes. Most departments have some crossover, so there are loads of duplicated entries.
Simon
;P
Cheers, Vıkram.
"You idiot British surprise me that your generators which grew up after Mid 50s had no brain at all." - Adnan Siddiqi.
-
I maintain a database and application that stores an extremely large amount of historical trading information. I have trades going back to the prior millenium, in fact. :) However most of the daily operations that occur, only require dated trades from within the last 12 to 24 months. So to improve efficiency, there are two trades tables. One is called historical trades and the other is called current trades. The tables are identical and there are database procedures that allow trades to be moved from one table to another depending upon whether a business function requires it or not. So while some of the previously mentioned examples are definitely ugly and present unique difficulties in maintaining, full and complete normalization is not an end that always justifies itself. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
Oh, I wasn't suggesting that 100% normalization is a good thing. My current databases have some level of denormalisation to reduce the load on the server. That sounds pretty cool actually, shifting historical data out to a separate table to reduce data retrieval times. Not something I've ever thought of doing. Nice.
Simon
-
I have to ask... why not have a separate Database to hold archive data instead of just a table (or however many tables store historical data)? Much easier to handle, IMHO, especially when it comes to space requirements -- the historical data can be moved to nearline or offline storage without affecting the production (12-24 month) data. Although I guess your system is simpler to manage... :) Peace!
-=- James
Please rate this message - let me know if I helped or not! * * *If you think it costs a lot to do it right, just wait until you find out how much it costs to do it wrong!
Remember that Professional Driver on Closed Course does not mean your Dumb Ass on a Public Road!
See DeleteFXPFilesVery good question. The reason to keep the historical data within the same database was so that the data could be made available again in the trades table very quickly, should it be needed. Ocassionaly, a request will be made to provide a monthly trade report for March 2002 for example and there isn't any reporting capability with the historical tables (yet!). It was decided that being able to move the data from table to table would be quicker and less prone to errors, than some form of export/load operation that would be used for a database to database operation. Fortunately, we haven't run into a space problem, though as you point out, that could become an achilles heel sometime in the future.
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
-
I think I've ranted about my companies databases before, but seriously, why on earth would you create identical tables to hold data separately for each department. It's so denormalised it actually hurts. e.g. Say we have a products table, that holds all the products we make. There will be a Products1 table for department 1, a Products2 table for department 2, a Products3 table for department 3. (We have 8 departments!). Each of the products table holds all of the products that department makes. Most departments have some crossover, so there are loads of duplicated entries.
Simon
-
This is a common practice... In an application that I supposedly had to buy (and that I didn't) there were tables with the complete date: yyyymmdd, and tables with the year only and tables with the month only and tables with the day only... of course all of them had their primary keys in order to relate the different parts of the date... the most stupid part is that the programmer was proud of it because (and I'm saying his words) "In this way I avoid to manage the amount of information that is a date, doing that I don't loose time looking for the year or any other part of the date when I want only one part of the information..." X| This is too horrible to go to the coding horrors section...
-
Well, to cut a long story short. The database has over 100,000 tables. It's the back end of some MRP system that is rather antiquated. The IT department think creating new tables is the answer to every problem. replacing it is not an option at the moment unfortunately.
Simon
Simon Stevens wrote:
The database has over 100,000 tables
Good Lord. Do they all have a single row?
Software Zen:
delete this;
Fold With Us![^]