Rant, Technology

Programming Ignorance

Have you ever had a person who doesn’t know what they are doing make your life more difficult? The last couple of weeks I’ve spent HOURS fixing a couple of major design flaws in a piece of software because it has come to the point that these design flaws are seriously hindering the progress and performance of the software. The software works great for the hundreds of companies that use it, but it’s the improvements we are wanting to make that are being hindered.

One of the flaws I’ve been fixing is rewriting a bunch of functions to use the primary key for a database row instead of a text field that holds the item’s name. Everything within the inventory management part of the software has been based on the item name. So for example if you want all information for an item this is the query the original designer thought was appropriate  SELECT * FROM rentals WHERE rentalname=’80’x20′ Canopy Tent’; instead of SELECT * FROM rentals WHERE rentalid = 7; As you can imagine this causes major problems when somebody changes the name of a rental item. When these problems were brought to my attention, I had to write a feature that changed the name of every instance of an item in the reservation and reservation history tables when an item name was changed (there was not enough time to fix the design flaw). Like I said I’m rewriting it all to use the primary key instead of a text field when referencing the rentals and reservation tables. There was not an option to use the primary key on new code I wrote because of the next flaw I will discuss. I had to keep going with the bad design until I had time or was forced to fix it.

The other flaw when handling inventory is each row only accounts for one item. So if I had 5000 White Folding Chairs, I had 5000 rows in the main inventory table, each with their own unique primary key (because that is the nature of primary keys), but each row had a matching name. If I booked 250 of those chairs I had 250 rows in the reservation table and subsequently 250 rows added to the history table when the reservation passes. If an item’s information is updated it would have to update 5000 rows which is unnecessary. This method also adds a lot of unnecessary queries, aggregate functions, and group by clauses. If I wanted to count the chairs in inventory the software would use a separate query like SELECT count(*) as qty FROM rentals WHERE rentalname=’White Folding Chair’ GROUP BY rentalname; instead of SELECT qty FROM rentals WHERE rentalid=12; and since qty is a now a field in the database this query is totally unnecessary because the quantity in inventory is retrieved with any SELECT * query. To fix this problem I had to do the following:

  • Add a quantity field to all associated tables (master inventory, reservation, and reservation history tables)
  • Count the quantity of each item on each reservation
  • Update the rows associated with that item and reservation with the quantity. At this point if I have 25 of an item on a reservation I have 25 rows that have 25 in the quantity field. Unfortunately MySQL throws a fit about the query UPDATE rentals_reserv a SET qty = ( SELECT count(*) FROM rentals_reserv b WHERE a.reservid = b.reservid AND a.rentalname = b.rentalname GROUP BY b.rentalname ); There are some workarounds but it’s easier to do two separate queries which isn’t that big of a deal.
  • Delete all but one row for each item on each reservation. So if I had 25 of an item on a reservation I would delete 24 of those rows.
  • Remove all queries that counted items on a reservation (the count(*) queries mentioned above), and add the quantity field to SELECT queries. This removes a lot of extra queries and a lot of extra foreach and while loops also (updating rows)

    So that’s what I’ve been working on and will be working on  for at least a couple of more days.

    My original intention wasn’t to blog about all of that. This was going to be a much shorter entry and just discuss the following block of code I found in the software several years ago.

    $len_dis=strlen($discount_p);
    if ($len_dis==1) {
    $discount_p = “.0” . $discount_p;
    } else {
    $discount_p = “.” . $discount_p;
    }

    Do you know what that block of code does? It converts a number like 9 to a percent. This is used when you have a field text field like this:

    %

    And you want the whole number you received turned into a decimal. So instead of using $variable / 100 he used that hideous block of code. While that block of code had minimal impact on the program as a whole it is probably the single dumbest thing I’ve ever seen in programming. I’ve never claimed to be a great programmer, but how ignorant do you have to be about the basics of programming to do any of these three things.

    3 Comments

    1. Uh SELECT * is bad. forces a table scan. Performance would
      be hindered. Always do SELECT (col1, col2, col3….). If you want
      SELECT * then list out all the columns. 😉 Had to do some work like
      that going through a clients stored procedures trying to find out
      where there slow down was. Figure out what needed indexed and what
      didn’t. Worst part was it was like a short time frame so all we
      could do was find the problem and make recommendations. I think it
      was years worth of data and procedures.

      1. Are you sure SELECT * causes a table scan? I know if a table doesn’t have an index or you are using a WHERE clause that uses non-indexed columns it will do a table scan.

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    Anti-spam protection

    Prove that you are Human by typing the emphasized characters: