Your Daily Cup of WTF - tblStaffDirectory

I suspect this one may be a bit less common than tblState ...

Used on an in-production, web-based staff directory of 600 or so, the idea behind tblStaffDirectory is expandability. By putting everything in one table, we don't need to worry about ever having to modify the database structure. Data in this table is stored in the following manner:

ID intID strTable strField strValue
1 1 Staff StaffID 1
2 1 Staff First Name John
3 1 Staff Location ID 4
4 4 Locations Location ID 4
5 4 Locations City Anytown

Creative, eh? While queries to retrieve the data in a useful manner may be a bit complicated (120 lines and 38 JOINS to get the equivilant of “SELECT * FROM Staff“), it's a heck of a lot better than the alternative (changing the table structures). Besides, I don't think users mind waiting 5-10 seconds for the page to load. I challenge anyone to come up with a better implementation than this.

Published Thursday, May 20, 2004 2:23 PM by Alex Papadimoulis
Filed under:

Comments

Thursday, May 20, 2004 2:34 PM by jakeypoo

# re: Your Daily Cup of WTF - tblStaffDirectory

what's wrong with

ID
Values

id, of course, being an autonumbered id, then values would be a comma separated list like "Username, Chris29, Password, pw1234, Date, 5/1/04"

well, i suppose the fields should be strUsername, strPassword, datDate, etc.
Thursday, May 20, 2004 2:47 PM by Jim Bolla

# re: Your Daily Cup of WTF - tblStaffDirectory

I fear the awesome performance of storing every value as a text field. This application must be pretty awesome to be able to handle a virtual database schema that can change w/o application modifications to accomodate the changes.
Thursday, May 20, 2004 3:29 PM by Phil Scott

# re: Your Daily Cup of WTF - tblStaffDirectory

Man, these posts crack me up. I had someone ask in class a couple weeks ago the maximum amount of data you can store in a row in SQL Server. I told them about 8k. Then they asked "no, i mean if you make all the fields text."

err, ok. my estimate was about 1,000,000,000,000 bytes of data per row btw.
Thursday, May 20, 2004 3:52 PM by Jerry Pisk

# re: Your Daily Cup of WTF - tblStaffDirectory

If you make all fields text (or image) then the maximum number of characters (bytes) you can store in a signle table row is (2^31 - 1) * 1024 which equals to 2,199,023,254,528 which is 2 ^ 41 - 1024. You'll get a little less characters if you're using double byte character set.
Thursday, May 20, 2004 4:10 PM by Phil Scott

# re: Your Daily Cup of WTF - tblStaffDirectory

Crap, you are right 1024 text fields per row, not 512. Doh! If this comes back to haunt me, I'll just say I was talking unicode data :)
Thursday, May 20, 2004 4:32 PM by Jerry Pisk

# re: Your Daily Cup of WTF - tblStaffDirectory

Heh, even if you say it was Unicode you'd still be wrong, because you said bytes, not characters ;) Otoh people who ask you such a question are not the ones to realize that.
Thursday, May 20, 2004 4:54 PM by Wim

# re: Your Daily Cup of WTF - tblStaffDirectory

You can't be serious! This sure is one way to kill performance...

Indexing down the drain, execution plan can't be optimized, bastardized datatypes, unmaintainable (speak of maintenance) overly complex queries, I could go on.
Thursday, May 20, 2004 4:54 PM by brady gaster

# re: Your Daily Cup of WTF - tblStaffDirectory

i'm so so so so so all about this thread. i think you should change the entire focus of your blog and just do things like this.

heck, why not buy "dailydoseofwtf.com" and we'll ALL contribute!

YEAH!
Thursday, May 20, 2004 6:19 PM by denny

# re: Your Daily Cup of WTF - tblStaffDirectory

I have done this kinf of thing for a set of "User Defined" items in an app but as was posted... indexing and sql server optimizing just got dumped to heck....

5 - 10 seconds you say?? you don't think the users mind?? what happens when you get say 50 hits on that in a 10 second time ?? bet it chokes and some users get errors....

where a normal db will be able to list that same data for say 1,000 users in say 1-2 seconds every time... no errors no lag.

sorry this is a good idea used for the wrong thing...
Friday, May 21, 2004 7:42 AM by ruurd

# re: Your Daily Cup of WTF - tblStaffDirectory

maybe denny should submit his own table scheme's to this blog?

sorry man, read between the lines
Friday, May 21, 2004 12:25 PM by Alex Papadimoulis

# re: Your Daily Cup of WTF - tblStaffDirectory

*sigh*

Sarcasm is dead.
Friday, May 21, 2004 2:51 PM by AndrewSeven

# re: Your Daily Cup of WTF - tblStaffDirectory

I once had an architect and a DBA who came up with an equivalent suxtem, but the had an IsElement IsAttribute and Names.

We didn't have to write the queries and all the data was provided FOR XML AUTO which we were able to convert into recordsets or into deep and complex object heiarchies in VB6.


Sarcasm dead? Tha sounds like a really brilliant idea. I can recommend an arch to help with that.
Saturday, June 5, 2004 1:28 AM by Sjoerd Verweij

# re: Your Daily Cup of WTF - tblStaffDirectory

Awesome. A full 8K page for every non-null property of this entity...

WOW.