My last job featured a database written by me, which had to be done because there was no way to pull proper data out of the company database.
The problem was that there was several people over several years who used the company database and they all had their own silly opinions about how to update and enter information. There was a few things that were firmly set by the system. For example, once you entered any information on the UI, the letters auto-capped so the data had some integrity.
The biggest problem was the product entries. The logical way to create the UI would be:
SKU:
Color:
Product Name:
etc.
This is how it actually was:
SKU:
Product Name:
And whilst there was a color field, it was buried so deeply in the morass of entry fields that I didn't even know they were there for the first 6 months I worked there.
So, the data was entered like this:
123-BLUE
123-RED
123-BLACK
123-BLK
123-BLU
etc....
I come in and am asked to compute sales history by SKU, Color, Product Type, and all sorts of ugly things. As you can see, there was no way to even get things by SKU, much less by any other important metric. It didn't help that it was all GUI and no one had access to the raw SQL.
This was the time that Excel came to the rescue in spades. I exported everything off the database, pushed everything into CSV and hand-separated all of the data into something that I could use, then pushed it into Postgres on my machine.
Moral of the story for me: Yeah, Excel isn't the best thing, but a poorly designed database system made by a half-baked company with 15 years of rot creates a nightmare situation as well, and at least in this experience, having the ability to examine the raw data, do find-replaces in wordpad, and other odd ends like that was the only thing that saved me and my job.
After I had left, I was asked to return for a week to encode my job to the best of my ability. There was just no way to ensure that the uploads had the correct integrity considering the size and time-constraints of the project.
I knew it wouldn't go well because my replacement was partly tasked with helping me clean the data for the next importation, which I tasked her to do since she kindly deleted the entire database that I spent the prior year building up. I told her that x, y, z, etc, had to be done manually and she couldn't understand the simple concept that you cannot navigate an Excel document with a mouse, so she kept handing me broken files and blowing up the imports.
I'm sure you can figure out what happened next: first time she uses it, despite stupid-simple step-by-step instructions, she destroyed the database and I sat at home tearing my hair out on how to import all the data back in. I really didn't have time to get this part perfect. I just docked it as a non-feature I knew I'd regret later and hoped against hope.
Companies should never ever hire people to do anything semi-technical that has anything to do with tech.
Why were they able to destroy your database repeatedly? Where there no backups? I can imagine they might not understand, but wouldn't you have sneaked some backups in a corner some where?
Yeah, I had some CSVs backed up in a dark corner they couldn't touch the first time, but I didn't have a full dump because, honestly, I thought they'd never use it again, and it was highly unlikely they'd ever find someone who can do SQL after I left.
The second time was some messy issue with encoding so the backups nor CSVs wouldn't load in. After trying absolutely everything I could find, I just build a script that wrapped all the backed up data in INSERT clauses and sent a raw .sql file over. I couldn't physically go in at the time, so I was trying to be phone support on my own trashy product.
The problem was that there was several people over several years who used the company database and they all had their own silly opinions about how to update and enter information. There was a few things that were firmly set by the system. For example, once you entered any information on the UI, the letters auto-capped so the data had some integrity.
The biggest problem was the product entries. The logical way to create the UI would be:
SKU:
Color:
Product Name:
etc.
This is how it actually was:
SKU:
Product Name:
And whilst there was a color field, it was buried so deeply in the morass of entry fields that I didn't even know they were there for the first 6 months I worked there.
So, the data was entered like this:
123-BLUE
123-RED
123-BLACK
123-BLK
123-BLU
etc....
I come in and am asked to compute sales history by SKU, Color, Product Type, and all sorts of ugly things. As you can see, there was no way to even get things by SKU, much less by any other important metric. It didn't help that it was all GUI and no one had access to the raw SQL.
This was the time that Excel came to the rescue in spades. I exported everything off the database, pushed everything into CSV and hand-separated all of the data into something that I could use, then pushed it into Postgres on my machine.
Moral of the story for me: Yeah, Excel isn't the best thing, but a poorly designed database system made by a half-baked company with 15 years of rot creates a nightmare situation as well, and at least in this experience, having the ability to examine the raw data, do find-replaces in wordpad, and other odd ends like that was the only thing that saved me and my job.
After I had left, I was asked to return for a week to encode my job to the best of my ability. There was just no way to ensure that the uploads had the correct integrity considering the size and time-constraints of the project.
I knew it wouldn't go well because my replacement was partly tasked with helping me clean the data for the next importation, which I tasked her to do since she kindly deleted the entire database that I spent the prior year building up. I told her that x, y, z, etc, had to be done manually and she couldn't understand the simple concept that you cannot navigate an Excel document with a mouse, so she kept handing me broken files and blowing up the imports.
I'm sure you can figure out what happened next: first time she uses it, despite stupid-simple step-by-step instructions, she destroyed the database and I sat at home tearing my hair out on how to import all the data back in. I really didn't have time to get this part perfect. I just docked it as a non-feature I knew I'd regret later and hoped against hope.
Companies should never ever hire people to do anything semi-technical that has anything to do with tech.