Wow! I would love to work on that database. It would be a vacation. We are converting to a new system called Datatel. Our alumni relation/annual giving folk were using a hosted product called "eTapestry". That was the worst possible system ever created. But, I digress...
My role was to take the exported eTap data and convert it into an Access database that could be loaded into Datatel. The files, exported from eTap, reached into the hundred-megabyte sizes. There were a total of 561 columns in each file. Basically, eTap could not export records, per se, but they could export records into columns. They turned the database "grid" 90 degrees. Not only that, but two of the columns contained data that required a "text" datatype in SQL Server. In other words, each unique value in the eTap database was converted into a column. It took me about a week to figure out how to get this data loaded into SQL w/o failing. To accomplish this task, I had to narrow down the possible bad columns one-at-a-time. I would break the number of columns in half. If 250 columns worked, then I would add 50 more columns. If that import operation broke, I would back it off by 25, etc, until I was able to find the columns that required a text datatype. Once I had the data in SQL tables, there was so much crap that you couldn't query it at once. So, I had to load all of the files, but split across three or four tables. I then created views to union all the records, but with only selected columns. Once that was done, I was feeling good. Then, I only needed to export the "cleansed" data into MS Access for Datatel to import into their software. After all that crap, I found that Datatel can only import data that originates from MS Excel spreadsheets. So, not only did I have to convert an exported file into SQL Server, then into MS Access, but then Datatel had to convert MS Access into MS Excel, then, finally, into their software. But, guess what? Excel can only handle 256 characters per field. I had to write a SQL statement that pulled only 256 chars out of these huge text fields to break up the long text into chunks that would fit into Excel. There were about 35 fields just to get the longest records. Then, and only then, were Datatel able to import the data into their system. Would I do it all again? You bet. The Datatel migration rep was a hottie! So, do I win, Erik? M!ke -----Original Message----- From: Short Fuse Media [mailto:[EMAIL PROTECTED] Sent: Friday, August 19, 2005 12:35 PM To: CF-Community Subject: Worst database... ever! Dear god - I'm staring at a db table with 109 columns, 300k records, that needs to be normalized to approximately 50k records across multiple (non existant mind you) tables. Heh, anyone else out there that have similar horror stories so I don't feel so alone out here? Heh.... -Erik ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:5:170468 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/5 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:5 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.5 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
