June 11, 2010 at 11:39 am
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.
[font="Arial"]Are you lost daddy? I asked tenderly.
Shut up he explained.[/font]
- Ring Lardner
June 11, 2010 at 11:52 am
@ 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?
June 11, 2010 at 12:07 pm
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".
June 11, 2010 at 1:14 pm
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
June 11, 2010 at 1:19 pm
It amazes me how often I run into designs where people think they're being "flexible" by having several "user" tables full of varchar(max) columns so the customer can store whatever they want. Or 20 "user" columns in each table called "user1", "user2", etc... for the same reason.
One company I'd worked for had a primary piece of software that they'd then "customize" for each client. In addition to the aforementioned problem, the way they "customized" the software was to write queries that were kicked off by queries in the parent program. If the sub-query didn't exist, nothing happened. If it did, it'd run. And those queries would call queries and so on. They actually had a lot of problems from running into the limit for how many generations of child queries could be called.
--------------------------------------
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
June 11, 2010 at 3:13 pm
OK, My Turn. This is something I have suspected for some time but only recently got verification on.
In addition to offensively breaking just about every rule in DB Design, the vendor who provides or inventory software has topped all prior “I can’t believe this” actions.
Every DML statement the software uses, is auto generated from a class they created in the code. And so for example when a new data entry screen is added to the application they tell the class what tables and what columns in those tables to use and this class auto-creates the SELECT or the UPDATE/DELETE/INSERT command that the application uses. Nothing is custom designed so no query is built with efficiency in mind; just consistency. The end result of this kind of mass DML Production line approach produces DML commands that make our systems beg for death.
I have managed to work around many of these problems with custom indexing and the like but there is only so much you can do with this kind of setup. This is why I personally have a low opinion of procedural programmers writing SQL code.
Kindest Regards,
Just say No to Facebook!June 11, 2010 at 3:19 pm
On the surface engaging with dev's is easy - participate, try for incremental improvements, don't rant if they don't get it all perfect, and show they how and why. Over time things improve. Yet that just doesn't seem to reach or scale.
So, instead, we've got to find a way to get databases into the DNA of developers. I think most developers don't appreciate the complexity (and elegance) they would find under the hood on a lot of these decision points. If we can show them that, and I'm thinking 2-3 days, I wonder how much things shift.
June 15, 2010 at 7:45 am
This was a good read. It is nice to see some bad designs out there that compete with bad designs I have seen. When you are able to improve on the design, it is well worth the accomplishment.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 15, 2010 at 8:23 am
You described a nightmare I recently had.
June 15, 2010 at 8:42 am
Jon Russell
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?
The answer is management. Management creates a team consisting of developers and DBAs, and then sets some base line rules, for example - the majority of T-SQL will be stored procedures, and the developers will pass parameters to the procedure where / when required.
The stored procedures will be composed and tested by the DBAs and information pertaining to the parameters to be passed will be communicated to the developers by the DBAs.
Additional rules which I think are obvious. No actual coding proceeds until a satisfactory design document is written and approved by members of the team. And of course the design document starts with what is required by the users of the database and why it is required.
June 15, 2010 at 8:46 am
Ron,
I agree it does start with management. All too often the DBA and developer work for different managers, so it's THEIR managers that make it work or not.
I don't know that I'd agree on the rest. I think devs perfectly capable of writing procs and other SQL bits, I'd rather check for style and performance, stay out of the QA business.
June 15, 2010 at 9:58 am
MANAGEMENT - The problem (from personal experience) is that far too foten the managemnt or decission makers are often programmers themselves and unless they have at least a respect for SQL they will treat it as poorly as the rest of their staff.
The truth is that procedural programmers who did not learn and work with SQL before becoming a programmer are most likely not going to learn to respect SQL and will continue to make the same mistakes in SQL design as those who came before them.
Its funny because you'd think after as many years as the Relational DB has been around, that procedural programmers would have finally confessed and admitted that the SQL:/DBA needs to be part of the development team when the software will be using a RDBMS for storage.
Kindest Regards,
Just say No to Facebook!June 15, 2010 at 10:17 am
One really bad data modeling mistake that can appear to work fine fine for months or years in production, but then present a major headache down the road, is when the developer stores date/time values in a VarChar column.
Then problem is when the data format used doesn't sort naturally, or you have multiple applications inserting this column, each with a different date format. Even worse is when the users are allowed to enter the date/time free form with no validation on the front or back end.
If your job is to build a report, then not even a CONVERT or CAST will provide a simple solution when the data looks like this:
EST_DATE_DELIVERY
-----------------
1/11/2009
2009-4-3 12pm
2010-05-22 18:45:03
99/99/99
12/5
N/A
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 15, 2010 at 11:01 am
YSLGuru (6/15/2010)
MANAGEMENT - The problem (from personal experience) is that far too foten the managemnt or decission makers are often programmers themselves and unless they have at least a respect for SQL they will treat it as poorly as the rest of their staff.The truth is that procedural programmers who did not learn and work with SQL before becoming a programmer are most likely not going to learn to respect SQL and will continue to make the same mistakes in SQL design as those who came before them.
Its funny because you'd think after as many years as the Relational DB has been around, that procedural programmers would have finally confessed and admitted that the SQL:/DBA needs to be part of the development team when the software will be using a RDBMS for storage.
I have been writing code for a living since Sept. 1972. Much of it procedural in nature. Now from that standpoint I agree with you.
The switch to event driven was tough. The switch to set based is tough. Now that the tough is done I now have the power to choose what technique is appropriate where and when.
Making changes for some people is hard. Any change. Still one has to decide that staying with the old ways is just a recipe for early retirement.
ATBCharles Kincaid
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply