|
Had to write some query on a SQL Server database today.
Opening the database took close to a minute.
What I saw then will forever haunt me.
Table names like [01 Some Company & Partner l.l.c.$Customer Ledger item], but also [01 Some Company & Partner llc$Cust something else], because sometimes you need Customer while other times you simply need Cust.
Each table had columns with names like [Entry No_], [Customer report code 1] and [Amount (CNY)] (CNY apparently means "currency", but with column names like that why even bother to abbreviate?) 
So we've got names with spaces, dots, numbers and symbols, complex enough to pass a password complexity validation!
Now there's something particularly odd about the table name...
The part before the $ is actually a company name and it turned out this database has the same tables for 23(!) companies, and some other (un?)related tables, giving the database a staggering 41,000+ tables! 
So let me say this again so you can be sure you read it right and I spelled it correctly... OVER FORTY-ONE THOUSAND TABLES!
It was a bit over 1700 tables per company, which I already think is A LOT, combined in a single database.
SQL Server allows for int.MaxValue objects in a single database and I have a feeling this application was pushing the limit (if not, not for lack of trying) 
I'm assuming most of it is generated, but even then, WHY WOULD YOU GENERATE SUCH !G($#&#$(!#F![Elephant | [mastadon]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/elephant.gif) J!#(P!? 
Sometimes you think you've seen it all and then you run into something like this and realize there really is no limit to human stupidity
|
|
|
|
|
Clearly, I'm going to die working. When I read stuff like this, the inmates are running the asylum.
Charlie Gilley
<italic>Stuck in a dysfunctional matrix from which I must escape...
"Where liberty dwells, there is my country." B. Franklin, 1783
“They who can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety.” BF, 1759
|
|
|
|
|
It's always been the inmates!
|
|
|
|
|
|
I apologize. I was in a mood when I generated that.
Real programmers use butterflies
|
|
|
|
|
honey the codewitch wrote: I apologize. I was in a mood high when I generated that.
FTFY 
M.D.V. 
If something has a solution... Why do we have to worry about?. If it has no solution... For what reason do we have to worry about?
Help me to understand what I'm saying, and I'll explain it better to you
Rating helpful answers is nice, but saying thanks can be even nicer.
|
|
|
|
|
I must admit I thought of you, but more along the lines of "if honey generated this, it would at least look a lot better  "
And also "she probably already wrote something that could generate exactly this." 
|
|
|
|
|
and presumably none is responsible  (or they went in hiding ... )
|
|
|
|
|
I'm afraid I have been there, just not with so many tables (maybe 20) nor companies (only 3), and using MySQL.
I just made a "SQL template" script and separated the companies into separate databases. Any common change would be made to the template and would be inherited by all (when the script was run against each of the databases).
But I guess they have to justify buying new hardware when their queries are not fast enough.
Hey, at least those have their queries in the database, right? Right?! In my case they were doing all queries (even simple ones like "select * from tableA where userID=x") in a weird PHP backend because they claimed that "the database is not good enough to filter the tables the way we want it"
Anyway, I think some whipping is in order. Grab your whip and get the guy that wrote that database. Don't let him procreate more databases like that
|
|
|
|
|
ElectronProgrammer wrote: Grab your whip and get the guy that wrote that database. Don't let him procreate more databases like that
FTFY
|
|
|
|
|
ElectronProgrammer wrote: the guy that wrote that database
If it was indeed created by a human and not dynamically created by some program
Anyway, the person responsible is probably a supplier of a supplier of my customer somewhere in the 80's, but I can't say for sure
|
|
|
|
|
Never used SQL but
Could CNY be Chinese Yuan? It's the standard forex abbreviation for it.
|
|
|
|
|
Greg Utas wrote: Could CNY be Chinese Yuan?
Nope, it's actually short for CurreNcY.
Makes perfect sense if you designed that database I guess 
|
|
|
|
|
Death to abbreviations. Just die!
Also, fix your elephanting spelling errors.
Charlie Gilley
<italic>Stuck in a dysfunctional matrix from which I must escape...
"Where liberty dwells, there is my country." B. Franklin, 1783
“They who can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety.” BF, 1759
|
|
|
|
|
And I'll bet some company tables are unique so combining them will be a challenge. I made a tidy living in the 90's attempting to normalise such horrors, never quite that big and ugly though. I wonder if the original was converted from MS Access, it is definitely an end user built database.
So quote a rate and don't offer a time frame (or even a guarantee) if you are going to try and clean it up.
PS CNY is definitely the Chinese currency.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
Mycroft Holmes wrote: And I'll bet some company tables are unique so combining them will be a challenge.
No doubt, luckily there's no need to combine them
Hopefully I'll never see this database again
Mycroft Holmes wrote: it is definitely an end user built database
Don't think so, end users here are typically as a-technical as they come.
I think a lot of it was generated though.
The database is owned by another supplier and kept around for historical purposes, I don't think it's actively being used anymore.
Mycroft Holmes wrote: PS CNY is definitely the Chinese currency.
Nope, it's short for CurreNcY
The amount in the column is usually the same as that in the regular amount column, except when the customer is not a Euro country.
This supplier doesn't do anything with China specifically.
Mycroft Holmes wrote: So quote a rate and don't offer a time frame (or even a guarantee) if you are going to try and clean it up.
Already did that years ago for another project, and both my customer and I are still reaping the benefits
|
|
|
|
|
this madness looks like it could make you indispensable if you raise to the challenge!
|
|
|
|
|
No challenges here.
Another company owns the database, someone else sometimes works with it.
That someone else asked me if I could do him a favor and write that query.
Other than that the database is not used anymore and kept around for historical data.
I've got other (fun) challenges coming my way
|
|
|
|
|
Ha damn... You still sound like the man of the hour, nay, the man of the year!
|
|
|
|
|
I am!
I solve their problems, better, faster and cheaper than any other supplier they have! 
|
|
|
|
|
The only difference between a mad man and a genius is that a genius knows that there is an int.MaxValue limit.
|
|
|
|
|
I once got to look into the table and variable name structure of BPCS. It, too, was a nightmare, and I can't believe it was a successful commercial venture. It might not have been as bad as the one you looked at. I don't know, because I cussed and closed it as soon as I could!
|
|
|
|
|
David O'Neil wrote: and I can't believe it was a successful commercial venture
I always wonder about that too.
Not because the database is a mess, because users can't care less about that, but because if the database is a mess then it's likely that everything else is a mess too.
Maybe it's just this weird naming that's wrong with it, maybe the naming was generated by some tool, but the developers actually know what they're doing? 
|
|
|
|
|
Sander Rossel wrote: OVER FORTY-ONE THOUSAND TABLES!
Wilco. Tango. Foxtrot. Echo.
Freedom is the freedom to say that two plus two make four. If that is granted, all else follows.
-- 6079 Smith W.
|
|
|
|
|
whenever i'm on a project and see a mismatch in the names in the database, the back-end and the front-end i immediately get the urge to kill everyone.
The part before the $ is actually a company name and it turned out this database has the same tables for 23(!) companies, and some other (un?)related tables, giving the database a staggering 41,000+ tables!
that's like creating 150+ tables a day for an entire year. part of those tables are created dynamically?
abandon project
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
