Entries in Database (2)

Wednesday
Sep042013

Surgery Tracker Database Integration

 

As I continue to work on and evolve this little project of mine, I am reminded of a bonus featurette on one of the Lord of the Rings DVDs.  They recounted how a very simple line in the script, something like "The fellowship ran down the stairs" turned into this 5 minute long special effects laden sequence, with crumbling stairs, and stunts, and added dialogue.
Now my version of this is, perhaps, not as visually stimulating as that.  Basically, I had planned this out in general terms.  The user would interact with a form, there would be some massaging of the form data, and that data would populate the database, etc.  I knew exactly what information they need to track, so making the database wasn't a problem.  I always question if there's a better way, but I don't think I can avoid that kind of self-doubt at this point.
What keeps happening, though, is I approach one piece of the project.  I will use creating the form as an example.  I know the broad strokes of what I want to accomplish, but then as the implementation happens, it ends up being so much more complex than I originally planned.  Now, I believe this is necessary complexity.  It would be very easy to do a simple form that handled a single entry at a time, but that would be a big hit to ease of use for the people I'm making this for.  So it ends up being more complicated than I originally thought.

Now I'm up to the point where it's time to stick these entries in the database, and again I'm surprised at how much more complicated the implementation is from my original expectations.  Part of it is PHP, I think.  Maybe it's just inexperience, but I don't find the way PHP interacts with databases very intuitive.  As far as I can tell, every query has to be extracted as a row in an array, even when you're performing some function and not retrieving data.  This came up because I was doing a count of records to figure out what the next record's ID number should be.
Another unexpected complexity was whether to use an insert or update query.  This one is my fault.  Early on, I had decided that every addition to the database would be a new entry, which would use an insert.  When I wanted to find the total number of a given surgery, for a given doctor, in a given month of the given year, I would pull all the records that met that criteria, and add them up.  This struck me as unnecessarily complicated, and potentially more time consuming.  Changing the 'Amount' field of a single record seemed a much better solution than maintaining several rows for each entry.  I know I give up some detail this way, but it's detail that they won't care about.

What I did not fully consider when making that change was that this didn't really eliminate complexity so much as shift it.  This change means that I can't simply use an insert for each addition.  I have to check whether the entry I'm trying to add it already there first.  So now I'm querying for the existence of the entry, then either inserting it or updating it depending on whether it exists.  So every entry now requires two queries.  This is what I would call sub-optimal behavior.  I could probably shave down on the number of repeats if I just have it try either inserting or updating, then doing the other one if that fails, but I don't like that solution.  
Right now I'm trying to weigh whether it would be better to go back and change the database again, making it behave more like how I originally planned, or just continue on and try to finish this.  It's not like this will be used 24/7 by a few hundred people at a time, so the inefficiency probably won't be noticeable, but the fact that it's there irks me.  Is it better to delay this thing even more to fix a problem no one will notice, or finish it sooner, get it to the people who want it, and feel like I actually accomplished something?  I've come close to giving up a few times already, and I'm concerned another setback will torpedo any remaining enthusiasm I have for this project.  I know what I should do, I'm just not sure if it's worth it at this point.  Stay tuned, I'm sure I'll be revisiting this soon.

 

 

Sunday
May232010

Database Projects

Now that I have a better idea of how databases work, I want to using them at work.  There are two things I do by hand that I think could be greatly enhanced by using a databases.  The first is tracking computer deployments. We have specialized software that is deployed on certain PCs, and right now I track all of them by hand using various spreadsheets. This works, but it isn't exactly ideal.

The other thing that would benefit from some databasin' is the employee directory. There are a bunch of things we track and put on the intranet about employees, and it's all done in separate documents by hand.  Email lists, a list of birthdays, a photo directory with phone numbers and a phone directory without pictures. It occurred to me during the database class I took that it's madness that I'm tracking this stuff by hand. This is, literally, what databases were designed for.

My biggest hurdle at the moment is mostly inexperience mixed with fighting the inertia of not doing it. I'm trying to be good and actually plan it out instead of just rushing in and making a bunch of mistakes. I've heard, though, that doing things like this have to be done 3 times to get it right. I'm wondering if maybe I should just plow ahead and screw it all up. Then again, the 3-time theory probably doesn't work that way. I don't want to be the guy that has to do it 4 times.

I'll try to catalogue my trials and tribulations implementing these systems at work. I'm not too clear on how specific I can (or should) get about things like that on here. I think it's best to play it on the safe side, at least for now.