And this is why you should listen to me



  •   A while back a colleague and I were tasked with building a custom CMS. My colleague worked on the administration part and as such designed the database. It was all good until I spotted a problem in the "pages" table that was supposed to keep track of the website pages. It was a "lang" column. On closer inspection the purpose of this became painfully obvious - it was supposed to keep the id of the page's language.

    *groan*

     I had a chat with the other dev and tried to explain to him why this is such a bad idea:

    "You can't put this here, we'll have multiple entries for each page in a multilanguage website"

    "But we need to store the language"

    "Yes, use another table and link it to this one."

    "Why? this way we can have different data for a page for each language."

    "Sooner or later you'll have data that stays the same for all languages. Then you'll have duplication"

     "But we don't have any data like that"

    ...and so on and so forth. Apparently we were supposed to use DISTINCT a lot. It's very difficult to explain to someone who has never taken a database design course why normalization is important. My colleague never actually went through a CompSci course; he just picked it up along the way.

    Eventually I gave up and let him have it his way. One crappy table couldn't hurt, right? I mean how many pages can a website have anyway?

    Unfortunately there was a complication. You see the admin panel could dynamically handle new tables, but with one small catch. The tables had to have a similar structure. Meaning that every single one now had a "lang" column. Eventually we had to add a basic eshop on this architecture and before you know it we had a products table that had a one row per product per language. The category table also had this structure. The product_categories table had the same structure. That's right, the table linking products and categories now had three columns: product id, category id and lang. You can imagine what the EAV tables looked like.

    But that's ok, that's fine. I mean how much data can you have anyway?

    Well, we recently got a request to build a site for a bookstore. Said bookstore gets its inventory from a provider that has supplied us with a feed where we can get the product information. Turns out there are about 65000 products. And about 4000 categories. These are given in two languages. Count how many entries that's in the product_categories table. Count how many entries that is if we had say... 5 languages.

    Not only that but we can't really calculate how many products there are. The question "how many products do we have in stock" is answered by "in what language?". Cause if you speak french instead of english there might be more items in stock.

    It's alright though. See we figured this one out. We now have one row per item. We did this by adding extra columns so now the table design is like so: "productName", "productNameEn" (english), "lang" and so on and so forth.

    I'll leave the SQL statements needed to control this monstrosity to the imagination.

     

    Exercise: 

    In 500 words or less, explain to management why despite the fact that it works and is full of shiny buttons the product is in fact a giant clusterfuck.



  •  I'll do it in less than 100:

    You see, Mr. President, because of the way the system was designed, the database will the three times the size it should be. While this may not seem like such a huge issue, we will need to pay for three times the infrastructure as we should, thus spending a ton of money on things we don't need. It would be far less expensive to perform a simple redesign of the database.

     

    (71 - damn I'm good)



  • @bezking said:

    You see, Mr. President, because of the way the system was designed, the database will the three times the size it should be. While this may not seem like such a huge issue, we will need to pay for three times the infrastructure as we should, thus spending a ton of money on things we don't need. It would be far less expensive to perform a simple redesign of the database.

    Way too clear and concise.  I think you just blew your shot at CEO



  • @bezking said:

     I'll do it in less than 100:

    You see, Mr. President, because of the way the system was designed, the database will be three times the size it should be. While this may not seem like such a huge issue, we will need to pay for three times the infrastructure as we should, thus spending a ton of money on things we don't need. It would be far less expensive to perform a simple redesign of the database.

     

    (71 - damn I'm good)

    FTFY



  •  Isn't that like normal company policy? Along with the dumb SQL clients that pull down thousands of rows to the client and then show to the user a select few of them. Yes, they do the sorting and searching on the client. Then they wonder why when the databases increases in size, that it still takes an age to receive a few records, despite having gigabit ethernet. They normally take the solution of increasing the size of the backbone so they have more dedicated gigabit connections.



  • @bezking said:

    It would be far less expensive to perform a simple redesign of the database.
    If only. By now there are countless classes that are based on this architecture. God forbid we separate model from controllers. That's for wimpy programmers that can't handle hardcore code. Real men have SQL everywhere.

    We'd have to rewrite half the code to accomodate such a change. Try justifying that to management.



  •  "Mr president, a full redesign will eliminate approximately X man-hours in maintenance per month and save $X on server costs, while simultaniously boosting performance. In the first X months the development effort will pay for itself."



  • @astonerbum said:

     "Mr president, a full redesign will eliminate approximately X man-hours in maintenance per month and save $X on server costs, while simultaniously boosting performance. In the first X months the development effort will pay for itself."

    Hmm.  First guy had 71, you have (according to 'wc') 36.  Ok, I'll beat that

     - and I'll do it in haiku:

    Hey there, C.E.O!

    Wanna save X bucks per month?

    More hookers and coke! 

    :-) *bows* 



  • @DaveK said:

    Hmm.  First guy had 71, you have (according to 'wc') 36.  Ok, I'll beat that

     - and I'll do it in haiku:

    I can still do better! I need 0 words. However, I do need a cattle prod, an airtight room, and a few pints of ale. And a PFY to pay for the ale.



  • @derula said:

    @DaveK said:

    Hmm.  First guy had 71, you have (according to 'wc') 36.  Ok, I'll beat that

     - and I'll do it in haiku:

    I can still do better! I need 0 words. However, I do need a cattle prod, an airtight room, and a few pints of ale. And a PFY to pay for the ale.

    Tricky!  Remember, you still have to communicate to him precisely which of the many many good reasons for doing this to him is the one which you are currently doing it to him for.

    I suppose you could manage some sort of interpretive dance, with an operatic libretto done in semaphore using the cattle prods.

     



  • @DaveK said:

    @derula said:
    I do need a cattle prod, an airtight room, and a few pints of ale. And a PFY to pay for the ale.
    Tricky!  Remember, you still have to communicate to him precisely which of the many many good reasons for doing this to him is the one which you are currently doing it to him for.

    Or, have the airtight room insulated from radio waves so he can't phone home, leave him in there for a few hours / days, and call it a "workplace accident". Simon Travaglia does it all the time.


  • Garbage Person

    @derula said:

    @DaveK said:
    @derula said:
    I do need a cattle prod, an airtight room, and a few pints of ale. And a PFY to pay for the ale.
    Tricky!  Remember, you still have to communicate to him precisely *which* of the many many good reasons for doing this to him is the one which you are *currently* doing it to him for.

    Or, have the airtight room insulated from radio waves so he can't phone home, leave him in there for a few hours / days, and call it a "workplace accident". Simon Travaglia does it all the time.

    Ye olde fireproof tape safe.

    Yes, we still need one. No, we don't have tapes anymore. WE JUST DO OKAY!? Surely you can put the IT offices in a now-defunct bank branch (there sure are a lot of defunct banks these days) so we can just repurpose the vault.



  • @DaveK said:

    I'll do it in haiku:

    Hey there, C.E.O!

    Wanna save X bucks per month?

    More hookers and coke! 

    :-) *bows*
    I can't believe I'm saying this, but you win, dave. 


Log in to reply