Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Bad Database Design Expand / Collapse
Author
Message
Posted Friday, June 11, 2010 10:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 6, 2014 9:10 AM
Points: 18, Visits: 220
We have a data base where a column has six atomic values stored in pairs of mixed alpha-numerical values (1A P7 S0). The data values are foreign keys. In order to retrieve the values then you have to use left/right/substring functions. The name of the column? INVENTOY_CODES. Needles to say, it has nothing to do with inventory.

But the best. On a maintenance screen is a text field associated with a record. Assume that the field would be in the same table as the main record, right? Nope. It's attached to a logging table that records about a dozen or so different transactions against a parent record. Over the years, the table contains thousands of records against the any one parent item. The little text field? It merrily follows along, repeating itself over and over and over again. And is there a ID key relationship between the text field and the actual table it's associated with? Nope again! So, as a work around we have the data entry clerks list the records manually so we can then use a function to align the parent records to the corresponding text value to generate reports.
1) Parent 1) Text
2) Parent 2) Text
3) Parent 3) Text etc.
Post #936154
Posted Friday, June 11, 2010 10:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 10:47 AM
Points: 43, Visits: 1,358
A financial services company I did some work for had an application that created most of its important tables on the fly. The app generated DDL when it needed to store a set of analysis data and ended up with thousands of tables named ANALYSIS_DATA_20091015_1, ANALYSIS_DATA_20091015_2, etc. I've seen a lot of bad designs, but this may have been the worst.

In my opinion, most bad DB designs I've seen these days are caused by developers working without a DBA. Second to this, they're caused by an admin DBA who doesn't have data modeling skills.


LinkedIn - http://www.linkedin.com/in/carlosbossy
Blog - http://www.carlosbossy.com
Follow me - @carlosbossy


Post #936158
Posted Friday, June 11, 2010 10:26 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:59 PM
Points: 23,000, Visits: 31,482
magarity kerns (6/11/2010)
The foul language generator is definitely the most humorous. The best bad design I've ever seen was designed by a sales manager and had rows for dollar sales by sales office (about 20) with columns for quarterly per product sales (~30 products). Every quarter, a new set of columns was made:

Office Q1_Prd1 Q1_Prd2 Q2_Prd1 Q2_Prd2
NWest $xxx.xxx $xxx.xxx null____ null____
SEast $xxx.xxx $xxx.xxx null____ null____
NWest null____ null____ $xxx.xxx $xxx.xxx
SEast null____ null____ $xxx.xxx $xxx.xxx

Visually, it made a giant matrix of nulls with a strip of data diagnonally from top left. Query times were slow to say the least. It was nearing the 1024 column limit when I found a new job.


Why does this remind me of a Notes database that feed into a SQL Server database at a mutual previous employer??



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #936163
Posted Friday, June 11, 2010 11:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:50 PM
Points: 327, Visits: 1,001
This has been a good chuckle, Andy. Thanks for this article. Your story is, hands down, far worse than anything I have experienced.

I am sure you all have seen these examples, but some of the things I have had to deal with are:
- No foreign keys on half the tables.
- Tables with over 20 varchar(1000) columns.
- Over use of cursors.
- Nearly all application logic in the stored procedures.
- And Jeff Moden’s favorite: RBAR. Where the code that Jeff talks about is a standard practice.

That is just to name a few. I also find some of the naming conventions used very humorous, too.
Like FK_foreigntable. Or FK_thistable. Yes, literally "thistable".

And the most humorous one was where a cursor was used to loop through rows on the left table to find matching rows on the right table. The comments in the stored procedure actually used the words "left table " and "right table". LOL. Obviously, the creator was new to the game and did know about joins.

On a serious note, I think you said it, Andy:
Andy Warren (6/11/2010)
JBailey, I don't know if it's as simple as that, but I think in general we tend to see a focus on just storing the data and not on using all the other things that can insure both data integrity and performance. Not necessarily fair to expect a developer to know all those things, but if they don't, then it's a mistake not to engage a DBA to assist. The good news in your example is that those fixes are fairly cheap compared to changing table design.

I too see a "focus on just storing the data and not on using all the other things that can insure both data integrity and performance." I don't expect developers to know everything, but I do expect the basics, like putting a foreign key on a table. I think it benefits us DBAs in the long run to educate and help developers use “all the other things that can insure both data integrity and performance”. After all, the better the code that goes into production, the easier it is to administer.

We have a DBA review process in our SDLC where I can provide some guidance, if needed. And if there are any table additions or modifications, the review is required. We also have code review where the DBA can review the final SQL code.



Post #936196
Posted Friday, June 11, 2010 11:33 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 8, 2014 10:08 PM
Points: 358, Visits: 397
Lynn Pettis (6/11/2010)
Why does this remind me of a Notes database that feed into a SQL Server database at a mutual previous employer??


No comment!!





PS - I recall it being in Access
Post #936200
Posted Friday, June 11, 2010 11:39 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 7, 2013 8:44 AM
Points: 339, Visits: 642
We recently purchased a company and are taking over their existing apps, one shocking revelation at a time. My favorite is the fairly simple transactional system for recording orders, approvals and processing dates. The way the various people involved in a transaction are linked to the transaction record is via a table called TransactionStamping. It has a TransNbr, an OrderNbr, I think an ApprovalNbr, and then, oh yes, FIELD1 FIELD2 thru FIELD12 and then DATA0 DATA1 thru DATA15. Depending on the client, you use different combinations of fields to link to things. I began getting hostile when the developers (still from the old company because while we could afford to buy them, we couldn't afford to rewrite their apps) tried to split the same link between tables to two fields for the same customer. (If it's this product get the Budget number from DATA11, but if it's that one use FIELD9.) When I threw a flag, the lead offered to add a DATA16 and also responded "Finally, I do not consider our data model “sketchy” simply because certain complex report requirements test the limits of our transaction stamping capabilities. The good news is for almost clients we have more than enough fields available to accommodate this." Argh.

Are you lost daddy? I asked tenderly.
Shut up he explained.

- Ring Lardner
Post #936205
Posted Friday, June 11, 2010 11:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:50 PM
Points: 327, Visits: 1,001
@ Cris E - Another example to Andy's point, "a focus on just storing the data and not on using all the other things that can insure both data integrity and performance." But you bring up a very important and related topic: how does the DBA engage developers in a constructive way?


Post #936215
Posted Friday, June 11, 2010 12:07 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:59 PM
Points: 23,000, Visits: 31,482
magarity kerns (6/11/2010)
Lynn Pettis (6/11/2010)
Why does this remind me of a Notes database that feed into a SQL Server database at a mutual previous employer??


No comment!!





PS - I recall it being in Access


I was thinking Marketing and its "Pipeline".



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #936228
Posted Friday, June 11, 2010 12:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 1:55 PM
Points: 45, Visits: 624
@Dennis Wagner-347763

Your story was sad, but so comical in the delivery, thanks for the laugh :)
Post #936259
Posted Friday, June 11, 2010 1:14 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:22 PM
Points: 2,751, Visits: 7,157
magarity kerns (6/11/2010)
The foul language generator is definitely the most humorous. The best bad design I've ever seen was designed by a sales manager and had rows for dollar sales by sales office (about 20) with columns for quarterly per product sales (~30 products). Every quarter, a new set of columns was made:

Office Q1_Prd1 Q1_Prd2 Q2_Prd1 Q2_Prd2
NWest $xxx.xxx $xxx.xxx null____ null____
SEast $xxx.xxx $xxx.xxx null____ null____
NWest null____ null____ $xxx.xxx $xxx.xxx
SEast null____ null____ $xxx.xxx $xxx.xxx

Visually, it made a giant matrix of nulls with a strip of data diagnonally from top left. Query times were slow to say the least. It was nearing the 1024 column limit when I found a new job.


I worked on a job where there were tables like this. To make matters worse, each month they'd change the name of the table to append the previous month to the name (Data becomes Data201006) and create a new Data table with 6 new columns for the values for that month. Every so often, they'd just get rid of a few months worth of columns at the beginning of the table in the new table to keep the columns down to a few hundred.

Fortunately, I was allowed to fix this "design".


--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Post #936261
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse