Comment On Not So Simple

Ah, the a-database-needs-only-one-table philosophy. Good times.

I bet redesigning the entire database would take less time then trying to figure out how to query that table and group the data into something reasonable.

Do you KNOW how much it costs to create a table in SQL Server? The "polymorphic-relational" pattern is an effective way to mitigate the need to create expensive new tables, and has proven itself over the years. This is not by any means a WTF.

However, I think it is now generally acknowledged that storing all data within an XML fragment in a single text field is a superior approach, as it also minimizes the unnecessary creation of columns, and is highly extensible. Often one only needs a single table with a single column for even the most complex of applications! I like to call this the "flexml" pattern, as it's just so gosh-darn flexible.

"that's right, you haven't seed that module yet; you better sit down for this one."

Ed: Fixed! Need to stop writing these up during Lost...

Un-bLevelable

Man, reminds me of a post last week on databases that were not normalized. This is even worse. In both cases, using a database system did hardly anything for them. Sounds like most of the development team there is the problem, a little organization and structure would simplify things, make them easier to understand, and almost eliminate those "you'll want to sit down for this" discussions. If you structure things well, you make it easier for everyone to understand better.

I think that using a BLOB to store the XML is still far superior to the single text field.

Let me guess, all
l were ints
all b were varchar(5) (for "true" or "false")

and the rest were text?

Enterprisey Architect:

I think that using a BLOB to store the XML is still far superior to the single text field.

You do mean using a single record with one BLOB column in which a zip-file is stored containing the XMLs, of course.

Then there's one column, one row: can't beat that, right?

Disburbing?

Enterprisey Architect:

I think that using a BLOB to store the XML is still far superior to the single text field.

A BLOB?! Excuse my language, but what the French toast?

I know I mentioned a text field in my description of the flexml pattern, which is the safest approach for beginners. However, once you are familiar with the needs of your application, you'll generally find that you can optimize this to the faster char data type - just make sure your users don't type any long sentences, although they will learn quickly. Lol!

Incidentally, although people might suggest that you use one of those unisex data types beginning with n (ntext, nvarchar, ninteger and so on), do you know any Chinese people that will use your application? And could you even read what they typed anyway? Thought not. Lol!

To those that suggest that the need for an XML schema will reduce flexibility, I don't think you quite understand how XML should be used. Here's an example demonstrating the ability to hold a complex one-to-many relationship in an extremely gosh-darn flexible manner.

<flexml>

     <key>Person</key>

     <value>Dr DBA</value>

     <key>PersonHasShippingAddress</key>

     <value>35,138</value>

     <key>ShippingAddress35</key>

     <value>1 Main St, Anytown</value>

     <key>ShippingAddress138</key>

     <value>35 36th St, New York, NY</value>

</flexml>

I really miss that girl who was capable of performing Ireland.

Don't be so harsh, guys, the original developer deserves bonus points for spelling "defenition" right.

tego:

Let me guess, all
l were ints
all b were varchar(5) (for "true" or "false")

and the rest were text?

You forgot FILE_NOT_FOUND and NotQuiteTrue
it at least need to be VARCHAR(20)

Dr DBA:

I know I mentioned a text field in my description of the flexml pattern, which is the safest approach for beginners. However, once you are familiar with the needs of your application, you'll generally find that you can optimize this to the faster char data type - just make sure your users don't type any long sentences, although they will learn quickly. Lol!

Incidentally, although people might suggest that you use one of those unisex data types beginning with n (ntext, nvarchar, ninteger and so on), do you know any Chinese people that will use your application? And could you even read what they typed anyway? Thought not. Lol!

I thought it was funny that you were laughing at your own jokes. Not that they aren't funny, but you're probably laughing harder than us :)

I come to this site for the wonderful programming ideas and techniques. I'm not familiar with using xml so instead, I will store an MS-Access database in an Oracle blob in a one column one row table. For efficiency, I will keep it zipped.

psssht, obviously he just needs to join on dtUserDefined3

This isn't hard. While you couldn't search do a function search by Name, you could use the Great Circle function to find objects near a location using a range. Join against a static hash table of Location Name, Lat, Long.

Zipcode databases are at most $100. Free if you can rip them off from the Census.

I imagine it became a WTF because probably the agents have service locations. For example a traveling service guy could work one day in NY, the next in Chicago, the next in LA. The database may have been designed appropriately for that scenario.

Enterprisey Architect:

I think that using a BLOB to store the XML is still far superior to the single text field.

No way, microsoft is waaaay ahead of you. Use the xml data type in sql server 2005.

So did this dude really quit once he saw the table schema? I once waded through a multi-hundred line function which repeatedly reused local variables ("for efficiency") and it took me a good 8 months to leave. You people are just too wussy!

KenW:

My favorites are the columns obviously reserved for the designer of this table:

lUserDefined1
lUserDefined2
lUserDefined3

That's so hilarious. I've never seen someone make that joke.

Wait... there's already a Location class? Well, then...

LocationEntity location = new LocationEntity(targetLocation);

Voila! Problem solved.

"David always dreading having to ask his coworkers about the system. No matter what the question was, no matter how easy it seemed, they always responded in the same manner: smacking their hand to their forehead, sighing, and then saying in an apologetic tone: "that's right, you haven't seen that module yet; you better sit down for this one." Rarely did their explanation disappoint."


That sounds like my job. You'd think getting some simple information out of the database would be easy but every time I have to ask, I get that response and the explanation usually takes at least 2 hours.

oh.

my.

goodness.

I have tears welling up in my eyes right now. And I haven't even been maced.

Holy denormalized table, Batman!

Ah--Pretty Simple after all--all you have to know is which lCapabilityTypeID values are locations, then search for matches on sName to GetLocationByName, and also knowing what values for bActive and bSelectable to use to help filter the search correctly, and how to make use of (or even if it's necessary to use) the fields lParentID, lOldParentID, lTreeID, lRightID, lOldTreeID, lSelectableTreeParentID, lOldSelectableTreeParentID, lIndentLevel, bLevel1, bLevel2 to make sure you're getting at the right data and not some incorrect branch or version of the data, and of course you have to make sure to filter on all the other fields appropriately to get down to the correct information.

Or maybe it's as simple as knowing who the president of the country is as filtering where sCustodianName is the president of the country you want?

is that Onyx?

WHAT THE *#$%!!! SERIOUSLY!!! WTF???!!!
Is this some sadistic joke??!!!

Dr DBA:

Enterprisey Architect:

I think that using a BLOB to store the XML is still far superior to the single text field.

A BLOB?! Excuse my language, but what the French toast?

I know I mentioned a text field in my description of the flexml pattern, which is the safest approach for beginners. However, once you are familiar with the needs of your application, you'll generally find that you can optimize this to the faster char data type - just make sure your users don't type any long sentences, although they will learn quickly. Lol!

Incidentally, although people might suggest that you use one of those unisex data types beginning with n (ntext, nvarchar, ninteger and so on), do you know any Chinese people that will use your application? And could you even read what they typed anyway? Thought not. Lol!

To those that suggest that the need for an XML schema will reduce flexibility, I don't think you quite understand how XML should be used. Here's an example demonstrating the ability to hold a complex one-to-many relationship in an extremely gosh-darn flexible manner.

<flexml>

     <key>Person</key>

     <value>Dr DBA</value>

     <key>PersonHasShippingAddress</key>

     <value>35,138</value>

     <key>ShippingAddress35</key>

     <value>1 Main St, Anytown</value>

     <key>ShippingAddress138</key>

     <value>35 36th St, New York, NY</value>

</flexml>

You guys still are not getting it.

You want to store these things as pictures of xml fragments taken on a wooden table, UUencoded and placed into memo fields in an MSAccess database, zipped, tarred, and stored in a single column of a single table in an oracle database.

-LEGO

Dr DBA:

Do you KNOW how much it costs to create a table in SQL Server?

No, and for that I thank my lucky stars.

dkf:

Dr DBA:

Do you KNOW how much it costs to create a table in SQL Server?

No, and for that I thank my lucky stars.

We're paid to do work, not buy work. Let the other people worry about the costs.

something like this should be even funner to do in SQL Server 2008 when they add the spatial data type

pitchingchris:

Dr DBA:

I know I mentioned a text field in my description of the flexml pattern, which is the safest approach for beginners. However, once you are familiar with the needs of your application, you'll generally find that you can optimize this to the faster char data type - just make sure your users don't type any long sentences, although they will learn quickly. Lol!

Incidentally, although people might suggest that you use one of those unisex data types beginning with n (ntext, nvarchar, ninteger and so on), do you know any Chinese people that will use your application? And could you even read what they typed anyway? Thought not. Lol!

I thought it was funny that you were laughing at your own jokes. Not that they aren't funny, but you're probably laughing harder than us :)

"Lol!"

one table to rule them all...

It's a Feature:

Ah--Pretty Simple after all--all you have to know is which lCapabilityTypeID values are locations, then search for matches on sName to GetLocationByName, and also knowing what values for bActive and bSelectable to use to help filter the search correctly, and how to make use of (or even if it's necessary to use) the fields lParentID, lOldParentID, lTreeID, lRightID, lOldTreeID, lSelectableTreeParentID, lOldSelectableTreeParentID, lIndentLevel, bLevel1, bLevel2 to make sure you're getting at the right data and not some incorrect branch or version of the data, and of course you have to make sure to filter on all the other fields appropriately to get down to the correct information.

You should be fine as long as you include lRightID and not lWrongID.

LEGO:

Dr DBA:

Enterprisey Architect:

I think that using a BLOB to store the XML is still far superior to the single text field.

A BLOB?! Excuse my language, but what the French toast?

I know I mentioned a text field in my description of the flexml pattern, which is the safest approach for beginners. However, once you are familiar with the needs of your application, you'll generally find that you can optimize this to the faster char data type - just make sure your users don't type any long sentences, although they will learn quickly. Lol!

Incidentally, although people might suggest that you use one of those unisex data types beginning with n (ntext, nvarchar, ninteger and so on), do you know any Chinese people that will use your application? And could you even read what they typed anyway? Thought not. Lol!

To those that suggest that the need for an XML schema will reduce flexibility, I don't think you quite understand how XML should be used. Here's an example demonstrating the ability to hold a complex one-to-many relationship in an extremely gosh-darn flexible manner.

<flexml>

     <key>Person</key>

     <value>Dr DBA</value>

     <key>PersonHasShippingAddress</key>

     <value>35,138</value>

     <key>ShippingAddress35</key>

     <value>1 Main St, Anytown</value>

     <key>ShippingAddress138</key>

     <value>35 36th St, New York, NY</value>

</flexml>

You guys still are not getting it.

You want to store these things as pictures of xml fragments taken on a wooden table, UUencoded and placed into memo fields in an MSAccess database, zipped, tarred, and stored in a single column of a single table in an oracle database.

-LEGO

Don't forget to store it on a zip-drive for maximum moving efficiency.

I should be a BA.

silent d:

one table to rule them all...

One sproc to find them.

Pope:

silent d:

one table to rule them all...

One sproc to find them.

One trigger to bring them all..

Man, you should have seen the six increasing levels of horror on my face as I scrolled down eight lines at a time at the end of that article!

Oh crap that means there's about 50 columns! ... Happy place, happy place ...

This is nothing! I've tamed tables with 144 columns and sprocs that took a corresponding 144 arguments. You guys are complete wimps! True WTF code I see it everyday. :-(

cod3_complete:

True WTF code I see it everyday. :-(

Good Job ! I just hope your eyes make it.

Glanced ahead and instinctively interpreted lLongitude as ILongitude, i.e.:

public interface class ILongitude

{

    virtual property float Longitude

    {

       float get(); 

       void set(float value);

    }

}

Still haven't decided if that would've been worse than the actual wtf presented or not.

Obviously, the table was created in a fixed width terminal. The fields were never meant to be called lSomething but ISomething.

pitchingchris:

Pope:

silent d:

one table to rule them all...

One sproc to find them.

One trigger to bring them all..

And in the object bind them.

RayMarron:

pitchingchris:

Pope:

silent d:

one table to rule them all...

One sproc to find them.

One trigger to bring them all..

And in the object bind them.

In the SQL Server, where the filegroups lie.

RayMarron:

pitchingchris:

Pope:

silent d:

one table to rule them all...

One sproc to find them.

One trigger to bring them all..

And in the object bind them.

Datagrid, son. In the datagrid bind them.

FredSaw:

RayMarron:

pitchingchris:

Pope:

silent d:

one table to rule them all...

One sproc to find them.

One trigger to bring them all..

And in the object bind them.

Datagrid, son. In the datagrid bind them.

Someone, somewhere hasn't been taking their 'I Hate Oracle' medication.

You have all forgotten.... cursors.

I think those are actually in use, but the content depends on
- the content of the first field
- the id is odd
- the actual season is spring
[more, even more stupid conditions]

KenW:

My favorites are the columns obviously reserved for the designer of this table:

lUserDefined1
lUserDefined2
lUserDefined3

Anonymous Coward:

Someone, somewhere hasn't been taking their 'I Hate Oracle' medication.

You have all forgotten.... cursors.

Cursors! Foiled again!

This is something that I saw every day. Not this specific table, but the design. It's just absurd how many enterprise databases can be so heavily used, yet be so mismanaged and so poorly conceived that it's sick. I've seen tables that have had literally dozens of "UserDefinedX" columns and the like that I honestly thought that it was some sort of joke -- until I saw how many records was in that table and how heavily used it was.

It may be a WTF in the sense that it is poorly designed and makes no sense, but it is not a WTF in the sense that this hasn't been seen or isn't being done in multiple enterprise environments every day.