Database Nightmare

  • (2 Pages)
  • +
  • 1
  • 2

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

#16 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Database Nightmare

Posted 29 March 2013 - 05:02 PM

That could be a reason, Lordofduct, except for the part where someone is asking the developer to add more columns to add more of what would be attributes in a MV system. Whoever it was didn't have your "WTF" moment there and should have.

It sounds more like the individual who put this together didn't go to the trouble of studying even the first normal form of relational database modeling. To be honest, it sounds like that individual would have had difficulty understanding it anyway.

I would redesign the thing. Making the investment will turn a profit over time in total cost of ownership.
Was This Post Helpful? 1
  • +
  • -

#17 haseren4  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 12-April 13

Re: Database Nightmare

Posted 12 April 2013 - 12:57 AM

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

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:/>/>.

That is just unforgivable.
Was This Post Helpful? 0
  • +
  • -

#18 jawooga  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 3
  • Joined: 11-August 13

Re: Database Nightmare

Posted 11 August 2013 - 08:35 AM

On the subject of
select *
it is hugely tempting to write in a stored procedure as an easy way of code reuse i.e same core function behind every table query in the api. However i would never write this on account of maintainability - developers knowing at a glance what the function does and moreover a change to a table should highlight every dependent function as not compiling.

Imagine a stored proc whose logic is based on something like
insert into tableA
Select * from tableB 


You change the structure of tableB and break your software the first time it's called.
Was This Post Helpful? 1
  • +
  • -

#19 jawooga  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 3
  • Joined: 11-August 13

Re: Database Nightmare

Posted 11 August 2013 - 08:41 AM

I would also point out that there are legitimate reasons for de normalising a relational database - performance mainly. Though you wouldn't if extensibility was desirable as in this case. To be fair, this sounds horrendous though. Sounds like a bit of code review with other database developers is required.
Was This Post Helpful? 0
  • +
  • -

#20 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: Database Nightmare

Posted 11 August 2013 - 08:07 PM

104 columns... that's all you got?

Quote

EmployeeID, RowID, Integer001, Integer002, Integer003, Integer004, Integer005, Integer006, Integer007, Integer008, Integer009, Integer010, Integer011, Integer012, Integer013, Integer014, Integer015, Integer016, Integer017, Integer018, Integer019, Integer020, Integer021, Integer022, Integer023, Integer024, Integer025, Integer026, Integer027, Integer028, Integer029, Integer030, Integer031, Integer032, Integer033, Integer034, Integer035, Integer036, Integer037, Integer038, Integer039, Integer040, Integer041, Integer042, Integer043, Integer044, Integer045, Integer046, Integer047, Integer048, Integer049, Integer050, Integer051, Integer052, Integer053, Integer054, Integer055, Integer056, Integer057, Integer058, Integer059, Integer060, Integer061, Integer062, Integer063, Integer064, Integer065, Integer066, Integer067, Integer068, Integer069, Integer070, Integer071, Integer072, Integer073, Integer074, Integer075, Integer076, Integer077, Integer078, Integer079, Integer080, Integer081, Integer082, Integer083, Integer084, Integer085, Integer086, Integer087, Integer088, Integer089, Integer090, Integer091, Integer092, Integer093, Integer094, Integer095, Integer096, Integer097, Integer098, Integer099, Integer100, Integer101, Integer102, Integer103, Integer104, Integer105, Integer106, Integer107, Integer108, Integer109, Integer110, Integer111, Integer112, Integer113, Integer114, Integer115, Integer116, Integer117, Integer118, Integer119, Integer120, Integer121, Integer122, Integer123, Integer124, Integer125, Integer126, Integer127, Integer128, Integer129, Integer130, Integer131, Integer132, Integer133, Integer134, Integer135, Integer136, Integer137, Integer138, Integer139, Integer140, Integer141, Integer142, Integer143, Integer144, Integer145, Integer146, Integer147, Integer148, Integer149, Integer150, Decimal001, Decimal002, Decimal003, Decimal004, Decimal005, Decimal006, Decimal007, Decimal008, Decimal009, Decimal010, Decimal011, Decimal012, Decimal013, Decimal014, Decimal015, Decimal016, Decimal017, Decimal018, Decimal019, Decimal020, Decimal021, Decimal022, Decimal023, Decimal024, Decimal025, Decimal026, Decimal027, Decimal028, Decimal029, Decimal030, String001, String002, String003, String004, String005, String006, String007, String008, Date001, Date002, Date003, Date004, Date005, Date006, Date007, Date008, Date009, Date010, Boolean001, Boolean002, Boolean003, Boolean004, Boolean005, Boolean006, Boolean007, Boolean008, Boolean009, Boolean010, Lookup001, Lookup002, Lookup003, Lookup004, Lookup005, Lookup006, Lookup007, Lookup008, Lookup009, Lookup010, Lookup011, Lookup012, Lookup013, Lookup014, Lookup015, Currency001, Currency002, Currency003, Currency004, Currency005, Hierarchy001, Hierarchy002, Hierarchy003, Hierarchy004, Hierarchy005, Hierarchy006, Hierarchy007, Hierarchy008, Hierarchy009, Hierarchy010, Hierarchy011, Hierarchy012, Hierarchy013, Hierarchy014, Hierarchy015, User001, User002, User003, User004, User005, User006, User007, User008, User009, User010, Plan001, Plan002, Plan003, Plan004, Plan005, Hyperlink001, Hyperlink002, Hyperlink003, Hyperlink004, Hyperlink005

Note carefully, the column "RowID", just in case you used up all the attributes types and need to store moar infos!!!

And the boss wonders why 98% of the execution plan is taken up referencing this table...
Was This Post Helpful? 1
  • +
  • -

#21 BlueMelon  Icon User is offline

  • D.I.C Head

Reputation: 40
  • View blog
  • Posts: 187
  • Joined: 27-April 10

Re: Database Nightmare

Posted 12 August 2013 - 05:44 AM

Nightmare.In.Database
Was This Post Helpful? 0
  • +
  • -

#22 Nakor  Icon User is offline

  • Professional Lurker
  • member icon

Reputation: 444
  • View blog
  • Posts: 1,492
  • Joined: 28-April 09

Re: Database Nightmare

Posted 12 August 2013 - 11:38 AM

View Poste_i_pi, on 11 August 2013 - 10:07 PM, said:

104 columns... that's all you got?

Quote

EmployeeID, RowID, Integer001, Integer002, Integer003, Integer004, Integer005, Integer006, Integer007, Integer008, Integer009, Integer010, Integer011, Integer012, Integer013, Integer014, Integer015, Integer016, Integer017, Integer018, Integer019, Integer020, Integer021, Integer022, Integer023, Integer024, Integer025, Integer026, Integer027, Integer028, Integer029, Integer030, Integer031, Integer032, Integer033, Integer034, Integer035...

Note carefully, the column "RowID", just in case you used up all the attributes types and need to store moar infos!!!

And the boss wonders why 98% of the execution plan is taken up referencing this table...


Don't know whether to laugh or cry at that.
Was This Post Helpful? 0
  • +
  • -

#23 depricated  Icon User is online

  • DLN-000

Reputation: 717
  • View blog
  • Posts: 2,434
  • Joined: 13-September 08

Re: Database Nightmare

Posted 20 August 2013 - 05:00 AM

I know your pain.

Months ago when we were still developing what is now the core of the application I manage at work, I had to design customer<->resource tables. As you might imagine, I created a table of customers, a table of resources, and a table tying the two together.

My boss offered to build the tables while I worked on the front end. So ok, I handed him the design documents and left it at that. He comes back an hour later and is like "I built the table. I think this will work better for what we want."

So, with trepidation, I pull it up. He's added the resources as boolean values to the customer table. I immediately started arguing with him about it, telling him it's a terrifyingly bad idea and that we should use the design I put together because it's more scalable. His response? "Yea any time we need to add a resource we can just go into the table and add a new column! And this way we don't need to create new tables!" I kept telling him this was a terrible idea til he finally said "shut up and do it" so I just buckled under and started coding a way to try and handle the fact that data was being stored in the column name, essentially. Oh yea, that's the other side of it - 200+ boolean values and the only way to know what they were was to check every time you needed to know if said customer had said resource.

So I come in the next morning, and he's been in since 5am, rebuilding the tables to my specifications. He said he asked about it online and someone told him "your database design is going to put someone's eye out"

So he's got integrity and is willing to admit when he's wrong, really cool imo. He has since seen the value in the standard many-to-many design as we've brought on new customers (over 300% increase in customers since we rolled my application into production! woo!)

Sometimes bad design isn't necessarily the programmer's fault. The whole numbered notes field bit reminded me of this story.
Was This Post Helpful? 1
  • +
  • -

#24 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 463
  • View blog
  • Posts: 3,166
  • Joined: 12-January 10

Re: Database Nightmare

Posted 20 August 2013 - 05:36 AM

im still stuck on the 104 column table... couldnt get past that
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2