I want a data encyclopedia



  • Here's my first WTF submission. I've been meaning to upload this for a while now.

    I briefly worked at a healthcare company. Apparently their idea of HIPPA compliance was making their database so impenetrable that no one - not even their own employees - could gain access to the data to misuse it.

    The above screenshot shows a few of the hundreds of tables in the largest of their multiple central databases. EVERY table, column, view, and stored procedure was named this way, or some other equally-esoteric pattern. This wasn't even caused by a total misunderstanding of the relational model, like we've seen here in a couple featured articles ("What's the maximum number of columns allowed?"). This is just how they decided to name things.

    They also had a couple data dictionary documents - distributed, of course, by one all-knowing employee at a different office - describing what each table was for in less detail than the one-sentence summaries at the beginning of a Javadoc page. Judging by the other submissions to this site, I'm guessing that "job security" is the first thing one learns as a DBA?

    BONUS WTF! The password for this database - which, somewhere deep within its bowels, contained all of their patient information - was six characters long. And contained three consecutive identical characters.

    (That's the database's bowels, not the password's bowels.)



  • You need to spend longer in the bowels of large databases before you are allowed to surface for air and a little light entertainment reading WTFs.

    ANY DATABASE with a large job to do (accounts, patient records, maybe both at once) will end up with a large number of tables.  So many infact that it is impossible to give them readable names like "invoices" and "patients".  You simply run out of english words to describe different tables in a 1-2 word name.  You can start using nonsense names like "fred" and "barney" but the usual solution is to adopt a naming scheme.  eg. "all financial tables start with a '9'".  then the system grows and adapts over decades and the naming scheme is broken down because it didn't anticipate some important usage which requires more than 100 tables within the "9xx" series.

    Along the path to the 100,000 table database (yes there are real, money-making databases that are this large) you will strike a few funny items in the naming convention, such as the "O0" table (letter-O, digit-zero, and all the documentation uses a font that doesn't distinguish these two characters) but large numbers of confusingly-named tables is a requirement, not a WTF.



  • I'm a n00b. I apologize for my limited experience with working with databases of that size

    I sort of expected that they would make some attempt at assigning the tables names with some semblance of usefulness, like "ab3nc_42_items". (I do see one "hcfa" there, so they tried at least once.)



  • @Qwerty said:

    ...but large numbers of confusingly-named tables is a requirement, not a WTF.

    What's interesting is that you've completely failed to prove this assertion.  I think if you have 100,000 tables in a single database, you are doing something seriously wrong.  What's more, there's nothing wrong with using a naming scheme, but it should still produce semi-legible, useful names.



  • So wait, you're telling me that what I posted was reasonable and what Qwerty posted was TRWTF?

    My soul is saved! I'm allowed to post things on the Internet again! Callooh, callay!



  • @morbiuswilters said:

    @Qwerty said:

    ...but large numbers of confusingly-named tables is a requirement, not a WTF.

    What's interesting is that you've completely failed to prove this assertion.  I think if you have 100,000 tables in a single database, you are doing something seriously wrong.  What's more, there's nothing wrong with using a naming scheme, but it should still produce semi-legible, useful names.


    I'm with you on that one.

    OTOH, it depends, of course, on the cabapilities of the database. If your database is an old version of DB2, where the tablenames could have a maximum of 8 characters, then you are seriously fucked. But decent modern databases don't restrict your tablenames that seriously, and with allowed table names of 16 characters or more, your naming scheme should be able to be rather helpful.


  • :belt_onion:

    @Qwerty said:

    You simply run out of english words to describe different tables in a 1-2 word name.  You can start using nonsense names like "fred" and "barney" but the usual solution is to adopt a naming scheme.  eg. "all financial tables start with a '9'".  then the system grows and adapts over decades and the naming scheme is broken down because it didn't anticipate some important usage which requires more than 100 tables within the "9xx" series.
    Or you could do what every one else does: put the financial records in the "Financial" database, the HR records in the "HumanResources" database and so on.

    I even remember seeing something like the equivalance of namespaces in SQL Server. I think in the AdventureWorks example database, you actually have tables named "Person.Address". So you could name your tables "Financial.GeneralLedger"



  • @bjolling said:

    I even remember seeing something like the equivalance of namespaces in SQL Server. I think in the AdventureWorks example database, you actually have tables named "Person.Address". So you could name your tables "Financial.GeneralLedger"

    It's called a schema in dba speak. Use schemas to your advantage. Every table in a SQL database is contained in a schema. The default schema name (if you don't specify the schema in your SQL statement) is the currently logged-in user's name.



  • I have a watered-down version of this at my work. I've been asked to "upgrade" a web-based time-management + a few other bits and pieces system. It's php/mysql, and thankfully, I was given carte blanche to rewrite it from scratch. The original had a grand total of 82 tables, including roughly 10 views (I believe they were included as an attempt to improve performance. Or something). Several tables consist of a foreign key as an id, and *one* other column - say, 'deleted', or 'active'.  My replacement db has 15 tables. This includes a few extra features, and I still feel like it's a lot for what it actually does (bad design decisions based on incomplete/misunderstood/mis-explained requirements. Yeah, communication here is another wtf).



  • @arotenbe said:

    The above screenshot shows a few of the hundreds of tables in the largest of their multiple central databases

    Every try working with Microsoft Dynamics GP? It's a sea of seemingly randomly named tables - don't even get me started on the stored procedures! It takes a good minute to load the sproc definition data when I click on the expand button in SQL Management Studio. I'd estimate that there are 10,000 sprocs...

    But it works...(mostly!)



  • How about a compromise?




    cl_mds2_c_accounts


    cl_mds2_d_employees


    cl_mds2_c_addresses




    This way, you can have 100,000 tables and still name the important ones. Of course, you still won't be able to filter/sort by the english names, but it's better than nothing.



  • The solution to this is simple.  Just use a XML file that maps the cryptic table name to a friendly English name and load it into Apache Kloperpink, which is a JSR62187-compatible database viewer.  With thousands of tables, writing that XML file could be daunting, so that's why you should use Ghiz to create splotfiles so Splot can write it for you.



  • One of the databases I regularly work with is on an AS/400.  It's only sort of a database as it's really a bunch of VSAM files, but the operating system exposes them using the DB2 database engine.  The AS/400 application that access these files is written in RPG-III, which, I'm told, has two major restrictions on field naming: maximum of 6 characters and no program can access two fields with the same name, even if they are in different files.  So, we use the first two characters for a file prefix and the next four to create a unique field name.  One of the "tables" has 113 fields.



  • @morbiuswilters said:

    @Qwerty said:

    ...but large numbers of confusingly-named tables is a requirement, not a WTF.

    What's interesting is that you've completely failed to prove this assertion.  I think if you have 100,000 tables in a single database, you are doing something seriously wrong.  What's more, there's nothing wrong with using a naming scheme, but it should still produce semi-legible, useful names.

    Morbs is right on this one.  I work for a sub company of one of the major Healthcare companies in the US, and while we do have a few large scale databases, none are that large and everything is broken down logically with sensible table and column names. 

     In Regards to hippa, we take extreme care to ensure the confidentiality with auditing, protocol, Group policy, restricted access, etc.  Surprisingly we developers, although I also double as a sql server DBA, get along great with the oracle team.  We do a lot of helping each other out in an effort to make sure that our standards for naming conventions, etc stay relevant and intact.

     In other words. The place you worked was terrible and you should be glad you left.



  •  I had to join to comment on a Microsoft Dynamics NAV system I've seen before, the database contains 91390 tables. Epic.



  • @beady said:

    I had to join to comment on a Microsoft Dynamics NAV system I've seen before, the database contains 91390 tables. Epic.
    Sounds like our Navision database - ~98.000 tables, except that they're split between 5 databases for some reason (with the largest one having 38293 tables).



  • @beady said:

    Epic.
     

    If you want to see epic, view source on this page, enable word wrap, and hold down PageDown.



  • @dhromed said:

    @beady said:
    Epic.
    If you want to see epic, view source on this page, enable word wrap, and hold down PageDown.
    WTF WHY IS IT MAKING ME DOWNLOAD >300 KB OF HIDDEN TAGS ON EVERY POST VIEW



  • @Xyro said:

    @dhromed said:

    @beady said:
    Epic.
    If you want to see epic, view source on this page, enable word wrap, and hold down PageDown.
    WTF WHY IS IT MAKING ME DOWNLOAD >300 KB OF HIDDEN TAGS ON EVERY POST VIEW

    Because you touch yourself at night.

    Morb may remember better than I, but I think at one point we pushed it close to a meg.



  • @bstorer said:

    @Xyro said:

    @dhromed said:

    @beady said:
    Epic.
    If you want to see epic, view source on this page, enable word wrap, and hold down PageDown.
    WTF WHY IS IT MAKING ME DOWNLOAD >300 KB OF HIDDEN TAGS ON EVERY POST VIEW

    Because you touch yourself at night.

    Morb may remember better than I, but I think at one point we pushed it close to a meg.

    Yes.  IIRC, it actually embeds all of the tags twice; once escaped, once not.



  • @morbiuswilters said:

    @bstorer said:
    @Xyro said:
    @dhromed said:
    @beady said:
    Epic.
    If you want to see epic, view source on this page, enable word wrap, and hold down PageDown.
    WTF WHY IS IT MAKING ME DOWNLOAD >300 KB OF HIDDEN TAGS ON EVERY POST VIEW
    Because you touch yourself at night.

    Morb may remember better than I, but I think at one point we pushed it close to a meg.

    Yes.  IIRC, it actually embeds all of the tags twice; once escaped, once not.
    It does indeed embed them all twice, although there doesn't appear to be any escaping differences.

    Also, I got a literal lol out of all the "FUCKING HELL" tags.  Fortunately I was able to control the iced tea I was swallowing at the time.



  • @morbiuswilters said:

    @Qwerty said:

    ...but large numbers of confusingly-named tables is a requirement, not a WTF.

    What's interesting is that you've completely failed to prove this assertion.  I think if you have 100,000 tables in a single database, you are doing something seriously wrong.  What's more, there's nothing wrong with using a naming scheme, but it should still produce semi-legible, useful names.

    QFT. 100,000 tables in a single base is generally a bad sign.



  • @Xyro said:

    @morbiuswilters said:
    Yes.  IIRC, it actually embeds all of the tags twice; once escaped, once not.
    It does indeed embed them all twice, although there doesn't appear to be any escaping differences.

    You are wrong, morbs is right. Proof:

    %22TRWTF+is+the+WTF+Meters+by+themselves.%22&%26quot%3bTRWTF+is+the+WTF+Meters+by+themselves.%26quot%3b&

    It's something as wonderful as urlencode(tag.'&'.htmlspecialchars(tag)).'&'.

    Sure, most of the times, tag == htmlspecialchars(tag)...



  • @derula said:

    @Xyro said:
    @morbiuswilters said:
    Yes.  IIRC, it actually embeds all of the tags twice; once escaped, once not.
    It does indeed embed them all twice, although there doesn't appear to be any escaping differences.
    You are wrong, morbs is right. Proof:

    %22TRWTF+is+the+WTF+Meters+by+themselves.%22&%26quot%3bTRWTF+is+the+WTF+Meters+by+themselves.%26quot%3b&

    It's something as wonderful as urlencode(tag.'&'.htmlspecialchars(tag)).'&'.

    Sure, most of the times, tag == htmlspecialchars(tag)...

    Ah, I missed that!  Subtle and fascinating!  The Community Server is a well of unending WTFs.  I wonder how many tables it uses.  Perhaps two for every tag.

    EDIT: haha, it also truncates tags even in the middle of an element escape.
    EDIT2: oh geez:@view source said:
    &%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%22%26qu&%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26quot%3b%26qu&



  • @Xyro said:

    EDIT: haha, it also truncates tags even in the middle of an element escape.

    Wow, that's awesome! Wonder if I can do that too.



    Also: The tags are only loaded on post page or on thread view after the first post containing tags. If there's no post using tags, tags aren't loaded (maybe different if you can edit). Smart, isn't it?



  • Only here does every thread degenerate into a discussion of how horrible the forum software is.



  • @arotenbe said:

    So wait, you're telling me that what I posted was reasonable and what Qwerty posted was TRWTF?

    My soul is saved! I'm allowed to post things on the Internet again! Callooh, callay!

    Just what we needed.  Another person who believes things that morb says.



  • @Xyro said:

    Ghiz
    teehee@Xyro said:
    Splot
    GUFFAW



  • At some point, someone will write a novel out of all these tags.



  • @belgariontheking said:

    @Xyro said:

    Ghiz
    teehee@Xyro said:
    Splot
    GUFFAW

    You must have missed this.



  • @Xyro said:

    @morbiuswilters said:

    @bstorer said:
    @Xyro said:
    @dhromed said:
    @beady said:
    Epic.
    If you want to see epic, view source on this page, enable word wrap, and hold down PageDown.
    WTF WHY IS IT MAKING ME DOWNLOAD >300 KB OF HIDDEN TAGS ON EVERY POST VIEW
    Because you touch yourself at night.

    Morb may remember better than I, but I think at one point we pushed it close to a meg.

    Yes.  IIRC, it actually embeds all of the tags twice; once escaped, once not.
    It does indeed embed them all twice, although there doesn't appear to be any escaping differences.

    Also, I got a literal lol out of all the "FUCKING HELL" tags.  Fortunately I was able to control the iced tea dick I was swallowing at the time.

    dTFY


  • @bstorer said:

    @Xyro said:
    Also, I got a literal lol out of all the "FUCKING HELL" tags.  Fortunately I was able to control the iced tea dick I was swallowing at the time.
    dTFY
    I always control the "iced tea".  I control it.



  • @arotenbe said:

    I'm a n00b. I apologize for my limited experience with working with databases of that size

    I sort of expected that they would make some attempt at assigning the tables names with some semblance of usefulness, like "ab3nc_42_items". (I do see one "hcfa" there, so they tried at least once.)

     

    Psst... I think qwerty is pulling your leg.



  • @arotenbe said:

    BONUS WTF! The password for this database - which, somewhere deep within its bowels, contained all of their patient information - was six characters long. And contained three consecutive identical characters.

    Don't you see? This is three-factor authentication!

    1. Know the password.
    2. Know how to navigate the database.
    3. Be crazy enough to try.


  • @SuperAnalyst said:

    How about a compromise?

    cl_mds2_c_accounts
    cl_mds2_d_employees
    cl_mds2_c_addresses

    This way, you can have 100,000 tables and still name the important ones. Of course, you still won't be able to filter/sort by the english names, but it's better than nothing.

    Worst of both worlds.  The architect setting up the initial system (20 years ago) can't accurately predict which tables are "important" today.  The users of these large systems quickly get to know that "IH01" is the important table for their particular task and they don't need to know that this was originally a vestigal bump on the side of a feature specified as a temporary fix for one customer but eventually became one of the significant features of the database.

     OK, 100k tables is an exaggeration.  There probably aren't too many of those databases in the world but I bet that the most WTF-encrusted one is still making money for its owner despite being too large for any one person to comprehend the entire thing at the table level.



  • I work with a document management system that creates tables as part of normal operation.  If you go into the config application, define a new document type, and add a keyword to it, several tables will be created.  For example, if you added keyword id 199 to the document type 150, you'd have new tables called keytable150, keyxitem150, and keyitem199.  Our very simple implementation has hundreds of these numbered tables.  The added bonus of this architecture is that the application has to use dynamic SQL for everything since the table names cannot be predicted when the code is written.  Even column names and data types are generated dynamically.



  • @derula said:

    You are wrong, morbs is right.

     

    Sounds like a bumper sticker. or the motto of a neo-fascist cult revolving around him as their leader, slaughtering everybody who says otherwise, and subjecting others to cavity searches using Dhromed's special tool to search for clues, forcing dead people to come back to life and shoving premature babies back into their mothers wombs. The very kind of people I hope to play shuffleboard with when I'm old and gray. (assuming I live through the cavity searches) .



  • @BC_Programmer said:

    or the motto of a neo-fascist cult revolving around him as their leader, slaughtering everybody who says otherwise, and subjecting others to cavity searches using Dhromed's special tool to search for clues, forcing dead people to come back to life and shoving premature babies back into their mothers wombs.

    No, no; our motto is "Sic Semper Hippieannis".



  •  *looks down*

    Yes, it is very special, isn't it.



  • Hippie Anuses, finally, we talk about something I have experience with.



  • @arotenbe said:

    Only here does every thread degenerate into a discussion of how
    horrible the forum software is.

    Because here is the only place where this ancient version of this proprietary forum software is still used.



  • @derula said:

    @arotenbe said:
    Only here does every thread degenerate into a discussion of how
    horrible the forum software is.
    Because here is the only place where this ancient version of this proprietary forum software is still used.

    From my studies so far, it seems that all threads degenerate into one of three topics: 1) Homoerotic imagery; 2) How much America is greater than the rest of the world; 3) How much this forum software sucks.
    Most threads of length involve all three, but tend to stabilize on one. A special exception is made for SpectateSwampRandomRandom.



  • @Xyro said:

    @derula said:
    @arotenbe said:
    Only here does every thread degenerate into a discussion of how
    horrible the forum software is.
    Because here is the only place where this ancient version of this proprietary forum software is still used.

    From my studies so far, it seems that all threads degenerate into one of three topics: 1) Homoerotic imagery; 2) How much America is greater than the rest of the world; 3) How much this forum software sucks.

    What about those turning into a competition of who can come up with the most cryptic Fallout 3 in-jokes?



  • @derula said:

    1) Homoerotic imagery; 2) How much America is greater than the rest of the world; 3) How much this forum software sucks
    1) dhromed; 2) morbiuswilters; 3) Everyone



  • @derula said:

    @Xyro said:
    @derula said:
    @arotenbe said:
    Only here does every thread degenerate into a discussion of how horrible the forum software is.
    Because here is the only place where this ancient version of this proprietary forum software is still used.
    From my studies so far, it seems that all threads degenerate into one of three topics: 1) Homoerotic imagery; 2) How much America is greater than the rest of the world; 3) How much this forum software sucks.

    What about those turning into a competition of who can come up with the most cryptic Fallout 3 in-jokes?

    I think that falls into category #2, because Fallout shows an America which has been devastated to the point where it's like the rest of the world.



  • @bstorer said:

    @derula said:
    @Xyro said:
    @derula said:
    @arotenbe said:
    Only here does every thread degenerate into a discussion of how
    horrible the forum software is.
    Because here is the only place where this ancient version of this proprietary forum software is still used.

    From my studies so far, it seems that all threads degenerate into one of three topics: 1) Homoerotic imagery; 2) How much America is greater than the rest of the world; 3) How much this forum software sucks.
    What about those turning into a competition of who can come up with the most cryptic Fallout 3 in-jokes?
    I think that falls into category #2, because Fallout shows an America which has been devastated to the point where it's like the rest of the world.

    Who would have guessed that some mere nuclear holocaust could boost a country's competitiveness like that!



  • @derula said:

    @bstorer said:
    @derula said:
    @Xyro said:
    @derula said:
    @arotenbe said:
    Only here does every thread degenerate into a discussion of how horrible the forum software is.
    Because here is the only place where this ancient version of this proprietary forum software is still used.
    From my studies so far, it seems that all threads degenerate into one of three topics: 1) Homoerotic imagery; 2) How much America is greater than the rest of the world; 3) How much this forum software sucks.
    What about those turning into a competition of who can come up with the most cryptic Fallout 3 in-jokes?
    I think that falls into category #2, because Fallout shows an America which has been devastated to the point where it's like the rest of the world.

    Who would have guessed that some mere nuclear holocaust could boost a country's competitiveness like that!

    Well, the non-nuclear kind did wonders for Germany and Israel.



  • @morbiuswilters said:

    @derula said:
    @bstorer said:
    @derula said:
    @Xyro said:
    @derula said:
    @arotenbe said:
    Only here does every thread degenerate into a discussion of how
    horrible the forum software is.
    Because here is the only place where this ancient version of this proprietary forum software is still used.

    From my studies so far, it seems that all threads degenerate into one of three topics: 1) Homoerotic imagery; 2) How much America is greater than the rest of the world; 3) How much this forum software sucks.
    What about those turning into a competition of who can come up with the most cryptic Fallout 3 in-jokes?
    I think that falls into category #2, because Fallout shows an America which has been devastated to the point where it's like the rest of the world.

    Who would have guessed that some mere nuclear holocaust could boost a country's competitiveness like that!

    Well, the non-nuclear kind did wonders for Germany and Israel.

    Coming up next: Godwin's law!



  •  I'm not touching this thread with my ten-foot stick.


Log in to reply