- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
I had a near-death experience reading this. Great WTF! :)
Admin
Didn't we have one similar to this a ways back (as in, within the last year or so)?
It was where they created a table for everything that should be a "row" so that they wouldn't be constrained by a change in the DB schema.
Either way, I'm not complaining. You could have one of these twice a year and I'd be happy because it'd be keeping this design off the streets... maybe.
Admin
So, did Michael's co-worker explain why the tables were "designed" this way?
Admin
One long database query maybe, but many calls to the directory functions to get the table filenames. Since the latter call do not get considered, the design is considered efficient. Is that about it?
Sincerely,Gene Wirchenko
Admin
Oh dear God! I thought relational databases were day one of all CS degrees!
Admin
Stuff like this seems to happen alot. It also seems to get posted on TheDailyWTF alot as well. Gotta say that I'm not surprised after seeing it a billion times.
The real WTF: The fact I can't think of anything witty for the real wtf!
Admin
I get the feeling Michael failed to share his coworker's sense of mirth.
Admin
<FONT face=Georgia>To quote the young boy in "Slingblade":</FONT>
<FONT face=Georgia>"Not funny as in ha-ha, but funny as in queer."</FONT>
<FONT face=Georgia>>BiggBru</FONT>
Admin
You have never been in one of those degrees things, have you?
</sarcasm>
Admin
Uhhhhhh.... no, duh! You have an xml file on the server that has a list of all the tables.
Admin
I think he needs a table in that database dedicated to keeping track of all the other tables.
I can't even imagine what he was possibly doing that made him get 3200+ tables. Probably where most people would just insert a row into a table he was just inserting a whole table or something like that.
Admin
I dunno about now, but not what I got mine. Relational Databases was an elective. Which I didn't take (instead I snored thru Networks).
Still, despite a lack of formal training, it is possible to turn out good databases just by using a mere 13 brain cells....
I cry for Michael.
Admin
same thing at the company that i just left today!
they have 305+ domains, and at one point had 305+ databases... all for the same basic site.
it took them 2-3 years to bring it all together, which left the now "current" main DB with a few tabes for each site, and tons of relational tables that are only used to coalesce a #siteID#-#uniqueID# for each account.
when i got there the 6 years of legacy code and almost zero documentation made me want to kill myself.
Admin
To the credit of the original designer, SQL Enterprise Manager loads slowly for me even when the database has 20 tables in it.
But I digress. When I started working in ASP years ago, I was doing SELECT and INSERT statements for a long time before I got comfortable enough to move on to CREATE TABLE and stuff like that. This guy had to do extra research, and it never occurred to him to just have this number in a separate column. Now that's Javascript-enterprisey-brillant-FILENOTFOUND-ish.
Admin
When I see WTFs like this, the only conclusion I can come to is the "application designer" has never seen an RDBMS with a good data model...maybe he is self-taught and just hacked together this solution as it was the best thing he could think of.
Admin
This seems to be very common - even Wordpress.com (http://photomatt.net/2006/03/01/wordpress-and-lyceum/) does this. WTF aside, is there any real reason why this is a good idea?
Admin
Third year course at University of Pennsylvania. Not a required one at that.
Admin
[sarcasm] That way is better because he could track any changes to the record in the same table as the record! [/sarcasm]
id 1 is original record. id 2 is first update ;-)
Maybe he hadn't learned the WHERE clause yet so he was stuck with:
EXEC ('SELECT address1 FROM infoset' + @intCustomerID)
Admin
At Northwestern University (the real one in Evanston, IL), there's only one DB class, it's optional (as are the compiler, OS, and networking classes; you pick 15 from a pool of classes), and it's taught by whoever gets stuck with it (atleast, 2 years ago this was the case).
Admin
When I got my degree, RDBMS was an elective, but I digress...
I wonder if this was designed for a customer base on the order of 10, and then the business took off? No excuse for it, of course, but I've seen systems designed for one audience, and then have them pushed waaaaaaaay beyond the designers' original expectations (just a couple days ago, wasn't there a post about 500 sql cnx from an excel spreadsheet?)
Imagine the nightmare of adding a column to the table-design - 34xx times? (smirks)
Admin
Sr. Dev #1: Hey man, check this out. I gave that new intern Matthew a task to do some work on the "funny" application.
Sr. Dev #2: Michael, I think the new intern's name is Michael.
Sr. Dev #1: Whatever. You should see him now.
Sr. Dev #2: Why? What's he doing?
Sr. Dev #1: He's just walking the halls muttering "three thousand four hundred twenty seven tables" over and over.
Sr. Dev #2: Heh... man, you're pure evil.
Sr. Dev #1: Yeah, I know. Wanna go get a latte?
Sr. Dev #2: Sure, you buying?
Admin
I daresay it's because his "coworker" was an "English Major."
No alternative options hold up.
Admin
The Horror.... The Horror.....
Admin
DBMS is a required course at Rutgers (300 level) but it is also one that most students do incredibly poorly in, to the point that the grading curve/scale is insane.
Example: On the midterm, I got a 47 out of 80. I was hoping that this would be a C (I didn't prepare enough for the exam). Turns out that this was an A. Anything 20 or higher was passing. So even though the class is required, learning is optional.
Admin
It could be worse.....they could've set their tables up like Remedy....
Admin
There's something very 'BOFH' about this reply.
Admin
C'mon, this is brilliant. Looking up an infoset by customer id is an O(1) operation, as opposed to the O(log n) search if the id were a column. It's a performance optimization. Just not a very good one.
Admin
Not hardly. Granted I was computer engineering, not computer science, so I basically only took the core programming classes, everything else was hardware. Still, I interned for 3 years as the personal code monkey the database administration group at a large US corporation. I learned good database design through those years of being mentored by a dozen DBAs who collectively had a couple centuries of experience with all the major players (MS, Sybase, Oracle, IBM and others).
The only way I would say that partitioning the dataset like this would make sense is that if each customer had a hideous amount of data in each table (I'm talking tens of millions of rows or more). Then, and only then, could this possibly be justified. Otherwise, I'd say this is another case of premature "optimization".
Admin
Really? It wasn't that bad for us. I had it spring 2004 w/ Borgida. By this point, CS enrollment was down enough that he could grade "normally" instead of doing it by percentages.
I mean, I didn't do that well in it, but still. That sounds awful funny.
I might add that I work with databases every day with my current job. It certainly prepared me, at least to some degree.
Admin
So, there are numerous reasons why a system could look like that,
the most obvious is, Are the customer's able to dictate their own data format?
Lets say they have 700 customers, and the customers have their own data format, they don't want to glue all the customers to one huge table with the fields [customer id, fieldname, value], so they create tables to map to the customer's data requirments. This allows the customer to use sql to scan their own tables, to have indexes for their data and all other nice little features. Over time, this causes there to be many, many tables in the system, all with different schemas.
I do think there probably (hopefuly) is a table that holds [tablename, customer_id] and perhaps another with [tablename, field_id, field_type]. If you go so far to add to the first table with [tablename, customer_id, database, login], you can even seperate customers to different database machines/clusters you could limit each customer to a tablespace and limit their visibility to each other's data.
But then perhaps, the ASP model is lost on many people here.
Admin
Yeah, in my experience the average CS department wouldn't deign to teach anything as "vocational" as how to use a relational database. I had a fun time at my first real job.
Admin
Well, yeah. We started with 4 customers to be exact, but back then each customer had its own Excel spreadsheet. When we got to 18 customers, an outside consultant suggested we move to a fancy enterprisey database. So we did.
So what's the WTF? Aren't we supposed to use a database?
BTW, here's a helpful hint for others migrating from Excel to a database: You can save a lot of time just copying each entire spreadsheet contents into one big field in each table. No tedious copying and pasting of each cell value!
--Rank
Admin
Maybe the programmer wanted better performance when processing a customer and didn't realise about that rarely used feature known as indices.
I'm not going think about that.
Admin
EXEC ('SELECT address1 FROM infoset' + @intCustomerID)
Yeah, that's a great idea! I never liked cached execution plans anyways!
Admin
Why, it's simple. They just used a bunch of shell scripts to loop over all possible indices and to aggregate and postprocess the data. They're Enterprise after all ;-)
I'm wondering if it's possible to query this monstrosity any "normal" way???
Admin
<shudder>I have seen this done for large tables as a performance optimization when the company is too cheap to buy the Oracle partitioning package. Of course they never consider what the development costs implied by this are.</shudder>
Admin
The table schema aren't shown, but if you put the ID in the table as well as the table name and then use UNION ALL to string the tables together you are good to go for a normal query.
Yes, I work for an enterprise company. <The Shame>
Admin
I wonder what such people would do with Oracle's partioned tables feature...
Admin
Serendipitoous timing. We just got a copy of "Enterprise Integration with Ruby", by Maik Schmidt (publ. The Pragmatic Bookshelf).
From the Introduction:
"Have you ever worked for a big enterprise? Do you remember your expectations as you walked into work on that first day? Whistling as the sun shone brightly, you might have been thinking, 'It will be great to workk for <company name here>. They will have a professional environment where coffee is free and where every system has been specified accurately, implemented carefully, and tested thoroughly. ..."
"After your fifth cup of free coffee (around 9:07) you came to realize that the real world looks completely different from your expectations."
Admin
What I want to know is what >Bad< ideas the guy threw out as not serviceable...
On second thought, maybe I don't want to know.
In regards to the discussion about DB design being mandatory, at the college I attended (Worcester State College) it was a requirement for graduation.
Admin
select Jobs from Monster.com where EmployeeSatisfaction > 1
Admin
Um, UNION ALL for hundreds or thousands of tables? <shudder/>
Admin
Curve grading it nice until you get people outside the nice curve. I was taking my drafting class and out of 35 students we had 3 professional draftsmen. Amazingly enough they scored 135% of the possible points, while the rest of us were running around the low 80 mark.
Admin
In a stored procedure , query INFORMATION_SCHEMA for all table names that match 'infoset%'. Aggregate those together in a dynamic SQL statement like 'select * from infoset1 union select * from infoset2 union...', then execute the dynamic SQL and return. Piece o'cake, and I'm sure it will run in less than 10 minutes on a reasonably fast machine.
Admin
Huh... I don't know how those "enterprise" dbms solutions handle primary keys, but Postgresql makes an implicit unique index for PKs. I would imagine SQL server 2000 would handle that in a similar fashion.
This arrangement might speed up operations when certain sets of data are locked for editing, because, you know, nobody in their right mind would want to use row locking!
Admin
Erm, what?
Software engineering degrees or similar, maybe.
Admin
Everyone here should join together and create a consulting firm called the GarbageMen. And have ads like this:
"Does your newly hired DBA, Sysadmin, or Web Developer emit a blood curtling scream when looking at your system for the first time? If the answer is yes, the GarbageMen will clean up your system better than new, or India."
Admin
And here I am thinking that non-sensical clichés like "we gotta give 110% effort" were just figures of speech.
Admin
No, no, no. That's way too much work. Just use a cursor on the Information schema tables. And if the infoset tables don't all have exactly the same columns, well, you can do a cursor in each infoset table, and store the results in a master temp table that you can (wait for it) run a cursor over to deliver the results to the client. Bonus points if you can nest them all in a mass cpu-eating orgy of wanton resource waste.
Admin
<FONT face=Tahoma>i agree, i think SELECT statements were the first lessons, followed by INSERT, UPDATE, DELETE, then those DDL statements. That way you would probably learn to filter than create a separate table for each record or wreck havoc on production tables.
but like what some said, i think it's some sort of optimization (if no infoset aggregation is required) or customization (if each infoset has its own implementation for each client)
whatever the *real* reason behind this design, i'll probably stick with xml tables :P
</FONT>