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.

13 Comments

  • 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.

  • 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.

  • 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.

  • 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.

  • 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 :)

  • 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.

  • 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.

  • 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!

  • 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...

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



    sorry man, read between the lines

  • *sigh*



    Sarcasm is dead.

  • 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.

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



    WOW.

Comments have been disabled for this content.