June 10, 2010 at 9:25 pm
Comments posted to this topic are about the item Bad Database Design
June 11, 2010 at 2:14 am
this is the worst piece of design i have ever heard about!
my experience just includes not having foreign keys on tables, having varchar as the primary column!
June 11, 2010 at 2:57 am
We had a BI project once where I had to extract relevant data from a sql 2000 db with (literally) the following naming convention.
Table1
Table2
Table3
etc
To make it worse all the columns in the tables where names as such
Column1
Column2
Column3
etc
....................
Where to start 🙁
There was a happy ending, as we put that DB gently to sleep on a quiet december morn, approximately 8 weeks after that first viewing.
June 11, 2010 at 4:59 am
That sounds very familiar. We are wrapping up the first phase of a project that has lasted about a year to get away from that very same 3rd party design. It had about 90% of what you mentioned plus it has an inheritance design with all objects deriving from the same base table so that table became very contentious. In addition to that there were no keys on the table and it took a minimum of about 6-12 months before most were able to start to understand the design.
We fortunately have it mostly under control now. We've taken saves and opens from 10's of thousand calls in sql profiler down to about 100. We used .Net DataTables and SQL Server DataTable Types. We've got the save/load times down by abuot 80% and prob about 90% or more when we can get rid of the third party parts completely.
With the right brain power, and a little bit of elbow grease, it is possible to tame the beast. The one positive thing about the poor database design was that my SQL Skills have improved tremendously because you really had to rack your brain to find the data you're looking for.
June 11, 2010 at 5:40 am
Ah yes, the base 36 conversion to "bad" words. Probably most people have run across this at least once in their lifetime.
It reminds me of something I did with a secure HR document system that could make the actual files look inconspicuous to a wouldbe thief (be it internal or external) who hacked into our system. We used the primary key and did a base 36 conversion for the file name and then changed all the extensions to .001 so that the thief wouldn't know what they had if they came across it. We built a COM object to do the conversions to the "proper file name" when moving the files to a web-based viewer. Besides the "bad" words, which really didn't matter to us because no one would see them, we ran across "reserved" words in Windows (like LPT, PRN, etc.) that rendered the files unusable -- as in, we couldn't open them, we couldn't delete them, and we couldn't move them to be viewed. A simple lead zero filling fixed this.
Probably the worst database mess I ever saw was one with no keys on anything. Typically the first thing I'll do when working with a new database is to simply draw a diagram using Enterprise Manager or some similar tool. This particular database had about 300 tables in it, and when the diagram finished, I didn't see anything on the screen. As I started zooming in, I noticed a "dotted line" spanning for miles -- every table had been lined up side by side because there were no foreign key relationships in the entire database. Their DBAs also never joined two tables together using inner joins; everything required a left join because there was no referential integrity. This database drove a major piece of software being used for income tax returns, and my short one month stay there left me shaking my head and pulling out my hair. Customer's data would appear on the screen and the drop-down lists that should've defaulted with the "current values" had nothing selected because the definition for PaymentID = 6 had no related value in the Payment table.
June 11, 2010 at 6:40 am
Great stories, good to know I'm not the only one who has experienced them.
I had to watch while consultants destroyed a project. It read two files for orders, the order itself and the client who ordered it with a simple key relationship. Simple enough. They decided to complicate things by shuffling the data into several files they designed and add their own keys. They read the client, and called a function that got the key from their key table and incremented it. then read the order and to get the foriegn key that pointed to the client, called a different function that got a different key from their key table and incremented it.
Somehow it worked for a couple months. They never provided design documents with any detail, so I was not aware of this until during parrallel testing it created orders that had the wrong return address on every order. (We sent orders as if they came from the client).
June 11, 2010 at 7:58 am
This one hit close to home, but also made me feel a bit better. We hired a team of developers to build us a custom service ASP.NET application. I had already created an Access version and they asked for it as an idea of where to start. Well, it turned out they used my design right down to table names and relationships. But worse, they didn't enable referential integrity, they created situations where foreign ids weren't saved to the tables correctly and did other small, but nagging things that shouldn't have been overlooked. Does this kind of thing point to an industry wide problem where developers pay more attention to building the front end than they do ensuring the database is properly designed?
June 11, 2010 at 8:38 am
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.
June 11, 2010 at 9:32 am
Eoin (6/11/2010)
We had a BI project once where I had to extract relevant data from a sql 2000 db with (literally) the following naming convention.Table1
Table2
Table3
<snip>
Where to start 🙁
I worked with major product that came from AS/400. If it was a "program" it started with a P. If it was a table it started with an F. The 400 had its own database engine which got incorporated into DB2. Tables were files, hence the F. The next 4 digits (yup, digits) of the table name was the major function area. If the table was header information then it ended in 001. Details were in 010. So F4201001 was purchase order headers I think. There was even a set of tables that I found that could be used as a dictionary so I wrote a table documenter.
I spend quite a bit of time writing data imports from legacy back end systems into our sales force / field force mobile systems. There is a big player that has a pricing table the the key column is customer number, followed by a space, followed the item number. Well most of the time. Sometimes there is a modifier stuck in between the two. But not always.
M<y current hobby is Quantum Mechanics. Soething simple and relaxing.
ATBCharles Kincaid
June 11, 2010 at 9:33 am
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.
June 11, 2010 at 10:01 am
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.
June 11, 2010 at 10:16 am
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
June 11, 2010 at 10:26 am
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?? 😛
June 11, 2010 at 11:24 am
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 [/url]favorite: RBAR[/url]. 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.
June 11, 2010 at 11:33 am
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
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply