Database design wtf



  • At my current job I do data conversions.  I get some very interestings systems that I have to figure out how they are storing everything. While working on my latest project and trying to figure out the sql database.  I executed a stored procedure and it took a minute and fourteen seconds to execute and returned 206589 rows.  "Where's the wtf?" you ask.  The proc was sp_tables



  • Heh.

    TIP: Finish typing your post before submitting.



  • Or finish it two sentences early.



  •  @dhromed said:

    Heh.

    TIP: Finish typing your post before submitting.

    agreed. it ends halfway through a sentence. 



  • @Steeldragon said:

     @dhromed said:

    Heh.

    TIP: Finish typing your post before submitting.

    agreed. it ends halfway through a sentence. 

     I was finished typing.  Sorry that I forgot the period at the end of the last sentence.

    The proc was sp_tables.



  • @campkev said:

    @Steeldragon said:

    @dhromed said:

    Heh.

    TIP: Finish typing your post before submitting.

    agreed. it ends halfway through a sentence. 

    I was finished typing.  Sorry that I forgot the period at the end of the last sentence.

    The proc was sp_tables.

    I didn't even notice the lack of a period -- it seemed complete to me.  I inferred that sp_tables was some built-in sproc (I've not heard of it) that lists the tables in the current DB.  If so, then having hundreds of thousands of tables is truly an epic WTF.  Am I correct? 



  • I knew I could count on you, Morbs.  Your inferrence is correct, sp_tables is exactly that.



  • 206589 tables would lead me to believe that this DB "design" must involve something like:

    orders_Jan2008

    orders_Feb2008

    orders_Mar2008

    ...

    Of course, there could also be "dead tables", temp tables no longer in use but still in the DB. One particular production DB has a temp_table that's actually in use; and other tables called like order_amounts_2_temp that are forgotten backups of another table. Still under 200 tables, though.



  • I have a lot of trouble imagining wtf is going on with that database design - one table per user for some sort of preferences? id/value nonsense pattern in each table?

    The wtf pervert in me wants to know.

    FYI, in MS-SQL, sp_tables is your tool for table "reflection" (don't you OOP-nitpickers start)... which apparently you will need heavily with this project.

    Oh why can't DBAs be given a license to kill...



  • @danixdefcon5 said:

    206589 tables would lead me to believe that this DB "design" must involve something like:

    orders_Jan2008

    orders_Feb2008

    orders_Mar2008

    ...

    It would?  Then it logically must also lead you to believe they are keeping track of over seventeen thousand year's-worth of orders.... 

    Even if it was one table per day, that would still be over five and a half centuries.  The only kind of explanation I can imagine that would get that number of tables was if they were using one table per *order*. 



  • @DaveK said:

    @danixdefcon5 said:

    206589 tables would lead me to believe that this DB "design" must involve something like:

    orders_Jan2008

    orders_Feb2008

    orders_Mar2008

    ...

    It would?  Then it logically must also lead you to believe they are keeping track of over seventeen thousand year's-worth of orders.... 

    Even if it was one table per day, that would still be over five and a half centuries.  The only kind of explanation I can imagine that would get that number of tables was if they were using one table per *order*. 

     

    my money is on one table per person/entity.  It is either one row for each order they placed or one row for each update to their profile/information.  perhaps both.

     any other guesses before we are enlightened as to the real cause?



  • @dphunct said:

    any other guesses before we are enlightened as to the real cause?

    My guess is temp tables that aren't really created as temp tables (just regular tables with a name like "tmp_" + time()) and that aren't being cleaned out.  The sheer number of tables indicates is some kind of automated table-creation process that is poorly-implemented and isn't properly cleaning up after itself.



  • @dphunct said:

    @DaveK said:

    @danixdefcon5 said:

    206589 tables would lead me to believe that this DB "design" must involve something like:

    orders_Jan2008

    orders_Feb2008

    orders_Mar2008

    ...

    It would?  Then it logically must also lead you to believe they are keeping track of over seventeen thousand year's-worth of orders.... 

    Even if it was one table per day, that would still be over five and a half centuries.  The only kind of explanation I can imagine that would get that number of tables was if they were using one table per *order*. 

     

    my money is on one table per person/entity.  It is either one row for each order they placed or one row for each update to their profile/information.  perhaps both.

     any other guesses before we are enlightened as to the real cause?

    All the tables are in fact completely empty, and just their names form the records in the real data set, in CSV format or similar?



  • @DaveK said:

    All the tables are in fact completely empty, and just their names form the records in the real data set, in CSV format or similar?

    "I'd like to report a bug in your DBMS software.  You arbitrarily limit table names to 255 characters.  This is no where near enough space, as I'm storing my data in table names."

    You mean something like that?



  • @campkev said:

    I knew I could count on you, Morbs.  Your inferrence is correct, sp_tables is exactly that.

     

    Not to pick on you specifically, but this is something that bugs me about the sidebar, and up until recently, CodeSOD.  People submit something quickly and don't bother explaining anything, under the assumption that everyone reading is familiar with every single language, database configuration, operating system and piece of software.  A lot of people neglect to explain why something they're submitting is such a WTF and always assume its obvious or infered.  This gets annoying because you have to read through a heap of replies and thread derailing comments in order to figure out what the issue was in the first place.  Its like delivering a punchline without setting up the joke.



  • @Soviut said:

    People submit something quickly and don't bother explaining anything, under the assumption that everyone reading is familiar with every single language, database configuration, operating system and piece of software.

    they usually don't even bother to specify what technology they're talking about, as if they not only assume everybody to be familiar with their technology of choice, but also that it's the only existing technology in its class. oh, sp_tables? right, that's implemented as a stored procedure and is called like that in every SQL implementation.



  • @Soviut said:

    under the assumption that everyone reading is familiar with every single language, database configuration, operating system and piece of software.

    I guess he figured that anyone who understood any sort of database design or even what 'Stored procedures' were would be able to infer 'sp_tables' is probably a stored procedures that lists tables, or at least be able to find it in a quick second without anyone holding their hand.



  • @tgape said:

    @DaveK said:
    All the tables are in fact completely empty, and just their names form the records in the real data set, in CSV format or similar?

    "I'd like to report a bug in your DBMS software.  You arbitrarily limit table names to 255 characters.  This is no where near enough space, as I'm storing my data in table names."

    You mean something like that?

    Yep.  But then I had an even more brillant idea.  The names of the tables could be in SQL syntax so that when concatenated from a list into a single statement and executed it would create a temp table containing the real data  :-) 

     



  • @Farmer Brown said:

    @Soviut said:
    under the assumption that everyone reading is familiar with every single language, database configuration, operating system and piece of software.
    I guess he figured that anyone who understood any sort of database design or even what 'Stored procedures' were would be able to infer 'sp_tables' is probably a stored procedures that lists tables, or at least be able to find it in a quick second without anyone holding their hand.
     

    As much as I hate to admit it, I have to agree with farmer brown on this one.  If you know so little about databases that you can't guess that sp_tables is a stored procedure that lists the tables then you probably don't know enough to think having a quarter million tables is a WTF.



  • @tster said:

    If you know so little about databases that you can't guess that sp_tables is a stored procedure that lists the tables then you probably don't know enough to think having a quarter million tables is a WTF.

    That's quite a stretch, don't you think?

    I happened to be unaware that MSSQL contained a set of very poorly named SPs for handiness & utility, and assumed, by virtue of the very poor name, that the sp belonged to one of the systems suggested by the OP, in which case all bets are off as to what it does.

     



  • @dhromed said:

    I happened to be unaware

    GIYF



  • @Soviut said:

    @campkev said:

    I knew I could count on you, Morbs.  Your inferrence is correct, sp_tables is exactly that.

     

    Not to pick on you specifically, but this is something that bugs me about the sidebar, and up until recently, CodeSOD.  People submit something quickly and don't bother explaining anything, under the assumption that everyone reading is familiar with every single language, database configuration, operating system and piece of software.  A lot of people neglect to explain why something they're submitting is such a WTF and always assume its obvious or infered.  This gets annoying because you have to read through a heap of replies and thread derailing comments in order to figure out what the issue was in the first place.  Its like delivering a punchline without setting up the joke.

    You're right, we can't all understand every language etc., so I take the attitude that if I don't "get it" first time, well then move on and wait for another. But I don't expect everything to be explained.
    To modify your joke analogy, it would be like delivering the punchline then saying "Do you see? The dog can't smell because it has no nose, but he misunderstood the question, so replied that the dog smells bad - it's kind of a pun, but also funny because of the misunderstanding." That would be both dull and patronising. So I go with the ones I get, but don't expect everyone to explain just for my benefit.
    Oh and this one made perfect sense to me first time round, but then I'm a database bore.


  • @campkev said:

    I executed a stored procedure and it ... returned 206589 rows.  ...  The proc was sp_tables.
    So tell us, what were all of those tables/views/nonscalar sprocs?


Log in to reply