Database Nightmare

  • (2 Pages)
  • +
  • 1
  • 2

23 Replies - 53651 Views - Last Post: 20 August 2013 - 05:36 AM Rate Topic: -----

#1 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 441
  • View blog
  • Posts: 1,488
  • Joined: 28-April 09

Database Nightmare

Posted 12 March 2013 - 06:58 AM

So I've been supporting a web app and its database that were both developed by another developer. One of the database tables used by this application consist of 104 columns. It has a Notes column as well as Notes1, Notes2, Notes3 and Notes4 columns. I'd say at least 30 of the columns are just repeats of another column with a number added to the end of it. Of course now the client is coming back and asking if they can add a few more entries for one of these repeating columns and I get to try to decide if I should tell them too bad because the database wasn't designed in a way that would allow it to grow. I could also continue the insanity and just add a few more columns. Or I could try to convince my boss to allow me to do a complete database redesign which would also mean redesigning the application to work with the new database. Ugh.

Is This A Good Question/Topic? 0
  • +

Replies To: Database Nightmare

#2 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6039
  • View blog
  • Posts: 23,438
  • Joined: 23-August 08

Re: Database Nightmare

Posted 12 March 2013 - 07:14 AM

The facepalm I just did was so epic my progeny will be feeling it.
Was This Post Helpful? 0
  • +
  • -

#3 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 441
  • View blog
  • Posts: 1,488
  • Joined: 28-April 09

Re: Database Nightmare

Posted 12 March 2013 - 07:29 AM

*
POPULAR

How this makes me feel...
Posted Image
Was This Post Helpful? 7
  • +
  • -

#4 Koyume  Icon User is offline

  • D.I.C Head

Reputation: 15
  • View blog
  • Posts: 89
  • Joined: 19-February 13

Re: Database Nightmare

Posted 12 March 2013 - 12:53 PM

It's always surprising to see how poorly people design their databases. It's like "Hey, take a blasted database management 101 course here people!"

And as Jack done, I facepalmed too :P
Was This Post Helpful? 0
  • +
  • -

#5 Takk  Icon User is offline

  • D.I.C Head

Reputation: 40
  • View blog
  • Posts: 111
  • Joined: 08-March 13

Re: Database Nightmare

Posted 12 March 2013 - 01:29 PM

Horizontal scroll bars should be replaced with a robotic smacking arm once you get to around 3000px :/

My taskbar is hiding down there and this hurts my brain.

How many SELECT *'s do you think you could find in the code? :) hah or better yet SELECT -,-,-,-, x 104, I hate the term noob with a passion, but everything else that goes to explain something like this is far more offensive.
Was This Post Helpful? 0
  • +
  • -

#6 Koyume  Icon User is offline

  • D.I.C Head

Reputation: 15
  • View blog
  • Posts: 89
  • Joined: 19-February 13

Re: Database Nightmare

Posted 12 March 2013 - 01:35 PM

I never understood someone who did "Select x,x,x,x,x,x,x,x,x,x + 143 to the nth power" type of deal. It's just easier to do "Select * FROM" type and just pull the rows that you need.
Was This Post Helpful? 0
  • +
  • -

#7 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 441
  • View blog
  • Posts: 1,488
  • Joined: 28-April 09

Re: Database Nightmare

Posted 12 March 2013 - 02:11 PM

View PostKoyume, on 12 March 2013 - 03:35 PM, said:

I never understood someone who did "Select x,x,x,x,x,x,x,x,x,x + 143 to the nth power" type of deal. It's just easier to do "Select * FROM" type and just pull the rows that you need.


The reason you do "Select x,x,x From Blah" is so that you are only getting the data you need rather than pulling back all data for that row. Take, as an example, the 104 column table I mentioned above, if I only needed 10, or even 50, of the columns it is beneficial to me to only query for those specific columns because it equates to less data that I have to transfer to my web page.

This post has been edited by Nakor: 12 March 2013 - 02:13 PM

Was This Post Helpful? 3
  • +
  • -

#8 Koyume  Icon User is offline

  • D.I.C Head

Reputation: 15
  • View blog
  • Posts: 89
  • Joined: 19-February 13

Re: Database Nightmare

Posted 12 March 2013 - 02:19 PM

View PostNakor, on 12 March 2013 - 03:11 PM, said:

View PostKoyume, on 12 March 2013 - 03:35 PM, said:

I never understood someone who did "Select x,x,x,x,x,x,x,x,x,x + 143 to the nth power" type of deal. It's just easier to do "Select * FROM" type and just pull the rows that you need.


The reason you do "Select x,x,x From Blah" is so that you are only getting the data you need rather than pulling back all data for that row. Take, as an example, the 104 column table I mentioned above, if I only needed 10, or even 50, of the columns it is beneficial to me to only query for those specific columns because it equates to less data that I have to transfer to my web page.

I'm well aware of that. But doing the whole table should just be with *. Normally if you are just pulling a couple of items out, the Select x,x is good. But if you are going to use the whole table, use the wild card. Appologies for not making myself clear enough :)
Was This Post Helpful? 0
  • +
  • -

#9 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 441
  • View blog
  • Posts: 1,488
  • Joined: 28-April 09

Re: Database Nightmare

Posted 12 March 2013 - 02:25 PM

I also forgot to mention there's at least one multi-valued column in it. This is where the user selects multiple values from a series of checkboxes on the web page and then the values get saved into the database as a comma separated string. And these people wonder why the reports they pull against this data never seems to be right. I swear someone went out of there way to try to break every database design rule they could.

View PostKoyume, on 12 March 2013 - 04:19 PM, said:

View PostNakor, on 12 March 2013 - 03:11 PM, said:

View PostKoyume, on 12 March 2013 - 03:35 PM, said:

I never understood someone who did "Select x,x,x,x,x,x,x,x,x,x + 143 to the nth power" type of deal. It's just easier to do "Select * FROM" type and just pull the rows that you need.


The reason you do "Select x,x,x From Blah" is so that you are only getting the data you need rather than pulling back all data for that row. Take, as an example, the 104 column table I mentioned above, if I only needed 10, or even 50, of the columns it is beneficial to me to only query for those specific columns because it equates to less data that I have to transfer to my web page.

I'm well aware of that. But doing the whole table should just be with *. Normally if you are just pulling a couple of items out, the Select x,x is good. But if you are going to use the whole table, use the wild card. Appologies for not making myself clear enough :)/>/>


ah, yeah, if you're pulling everything anyway I'd just use the * too.

This post has been edited by Nakor: 12 March 2013 - 02:32 PM

Was This Post Helpful? 0
  • +
  • -

#10 Takk  Icon User is offline

  • D.I.C Head

Reputation: 40
  • View blog
  • Posts: 111
  • Joined: 08-March 13

Re: Database Nightmare

Posted 12 March 2013 - 07:12 PM

Yeah... I was napping around that time, that's the point I was trying to make, incorrect usage. A large majority of the time since things aren't running directly in front of you, people ignore memory usage entirely. To go with that, the bad practice I guess would be using the same option for ever instance instead of intelligently using what you need, of course the * when it makes sense, but someone that really doesn't know what they're doing is likely not to know that the one tutorial they read about how to get this didn't give them every option or adequately explain your options. -shrug
Was This Post Helpful? 0
  • +
  • -

#11 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Database Nightmare

Posted 13 March 2013 - 01:50 PM

Nakor I feel for ya, I've seen them too. There was even one that I actually got to sit down with the original developer and when I asked why he had 20 columns in the table named exp1...exp20 which were empty and never referenced in the code, his reply was, they're expansion columns so that if they need to store something else, I can use one of them.

But then I started reviewing the code deeper and decided to pass on helping to re-write the software when I noticed not a single function or sub outside of the events existed. I swear there was one group of code that was identical and used in 12 different places with the only thing changing was the name of the textbox it was affecting.

This post has been edited by CharlieMay: 13 March 2013 - 01:51 PM

Was This Post Helpful? 0
  • +
  • -

#12 Takk  Icon User is offline

  • D.I.C Head

Reputation: 40
  • View blog
  • Posts: 111
  • Joined: 08-March 13

Re: Database Nightmare

Posted 13 March 2013 - 07:31 PM

View PostCharlieMay, on 13 March 2013 - 01:50 PM, said:

Nakor they're expansion columns so that if they need to store something else, I can use one of them.


Lol, he read the tutorial for creating tables, but not the one for modifying them :D All hail the google age.
Was This Post Helpful? 0
  • +
  • -

#13 CodingSup3rnatur@l-360  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 991
  • View blog
  • Posts: 971
  • Joined: 30-September 10

Re: Database Nightmare

Posted 18 March 2013 - 03:30 AM

I definitely feel your pain on this one, Nakor. I'm current trying to integrate a simple (or at least it should have been) data entry application into my new software at work that has been very poorly designed. For example,

* It uses a single table with 215 columns. Like in your case, many of the columns are name1, name2, name3 etc.
* For the slightly larger fields on the GUI, each field is split by line into about 5 different database columns, for no obvious reason.
* Each column is so poorly named that you can hardly tell what their purpose is. For example, Num, Co and ComI12
* The person who designed it doesn't seem to have heard of the bit data type in SQL Server, as they insisted on using strings for simple boolean values (as in 'Yes'/'No', and a few other domain specific booleans).
* There is also the occasional violation of 1NF, just to keep me on my toes.
* Finally, pretty much all the input was unconstrained, so there is all sorts of crap in the fields that shouldn't be there, and indeed there is data missing when there shouldn't be..

Honestly, it's torture just looking at it, never mind trying to do anything with it :crazy:.

This post has been edited by CodingSup3rnatur@l-360: 18 March 2013 - 10:43 AM

Was This Post Helpful? 0
  • +
  • -

#14 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 441
  • View blog
  • Posts: 1,488
  • Joined: 28-April 09

Re: Database Nightmare

Posted 20 March 2013 - 05:29 AM

View PostCodingSup3rnatur@l-360, on 18 March 2013 - 05:30 AM, said:

* The person who designed it doesn't seem to have heard of the bit data type in SQL Server


Yeah, I run into that as well. I also commonly see a column that is repeated in multiple tables but has a different data type from one table to another even though it's storing the same data. So you never know if you're working with an int, varchar, or some other data type.
Was This Post Helpful? 0
  • +
  • -

#15 lordofduct  Icon User is offline

  • I'm a cheeseburger
  • member icon


Reputation: 2531
  • View blog
  • Posts: 4,631
  • Joined: 24-September 10

Re: Database Nightmare

Posted 22 March 2013 - 08:08 AM

View PostNakor, on 12 March 2013 - 04:25 PM, said:

I also forgot to mention there's at least one multi-valued column in it. This is where the user selects multiple values from a series of checkboxes on the web page and then the values get saved into the database as a comma separated string. And these people wonder why the reports they pull against this data never seems to be right. I swear someone went out of there way to try to break every database design rule they could.


Just to play devil's advocate here...

what if the person came from a multi-valued database and had little experience with a relational database?

Before I got any heavy experience with relational databases, I had been using multi-valued for a while. So when I designed up a SQL database I was like, "wait, huh... how the fuck am I supposed to do this?"

Of course, I then googled about relational databases and spent a few hours researching them and learning the standards before moving forward.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2