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 Thursday, June 10, 2010 9:25 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 9:16 AM
Points: 6,784, Visits: 1,895
Comments posted to this topic are about the item Bad Database Design

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #935815
Posted Friday, June 11, 2010 2:14 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:51 AM
Points: 2,028, Visits: 369
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!
Post #935869
Posted Friday, June 11, 2010 2:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 7, 2014 9:49 AM
Points: 272, Visits: 739
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.



Post #935885
Posted Friday, June 11, 2010 4:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 14, 2014 1:54 PM
Points: 48, Visits: 176
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.

Post #935921
Posted Friday, June 11, 2010 5:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 5:43 AM
Points: 48, Visits: 203
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.
Post #935938
Posted Friday, June 11, 2010 6:40 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 1:58 PM
Points: 65, Visits: 259
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).
Post #935960
Posted Friday, June 11, 2010 7:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 2, 2010 4:00 PM
Points: 1, Visits: 5
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?
Post #936029
Posted Friday, June 11, 2010 8:38 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 9:16 AM
Points: 6,784, Visits: 1,895
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.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #936079
Posted Friday, June 11, 2010 9:32 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
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.


ATB

Charles Kincaid

Post #936129
Posted Friday, June 11, 2010 9: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
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.
Post #936130
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse