Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Bad Database Design


Bad Database Design

Author
Message
Carlo Clausius
Carlo Clausius
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 234
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.
Carlos Bossy
Carlos Bossy
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 1583
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24285 Visits: 37993
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?? :-P

Cool
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)
Jon Russell
Jon Russell
Old Hand
Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)

Group: General Forum Members
Points: 335 Visits: 1166
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.



magarity kerns
magarity kerns
Old Hand
Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)Old Hand (384 reputation)

Group: General Forum Members
Points: 384 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?? :-P


No comment!!





PS - I recall it being in Access
Cris E
Cris E
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 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
Jon Russell
Jon Russell
Old Hand
Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)

Group: General Forum Members
Points: 335 Visits: 1166
@ 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?



Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24285 Visits: 37993
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?? :-P


No comment!!





PS - I recall it being in Access


I was thinking Marketing and its "Pipeline".

Cool
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)
The Natrix
The Natrix
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 653
@Dennis Wagner-347763

Your story was sad, but so comical in the delivery, thanks for the laugh Smile
Sioban Krzywicki
Sioban Krzywicki
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1144 Visits: 8067
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search