Today's Database Design Principle is based around data movement within the database, specifically when it doesn't need to be done.
The basic problem I came across here was that two identical tables (A and B) were being used, along with a shared key, to manage the status of information inside the database. The application had a web form that could be filled out, saved, and later recalled to be submitted. In order to differentiate between the saved and the submitted data, the saved form fields were stored in table A, with an assumed status of 'ENTRY', while the submitted form fields were being stored in table B, with an assumed status of 'SUBMITTED'. When a saved form was then submitted, the data from table A would be copied to table B, and then the record in table A would be deleted.
While this design was functional, there were some definite drawbacks to the approach:
Table Maintenance – If a new field were added, or a field deleted from the form, then the same change had to be made in two places in the database.
Doing With Two Transactions What Could Be Done With One – With this design, the submission of data then requires two transactions: one insert to populate table B, and a delete to remove the save record from table A. If a single table had been used with a status field, then this could have been accomplished with a single update transaction.
Complicating Queries and Views – With the two-table approach, I now have to union together the two tables and calculate a status field to get the full set of data. This just adds a layer of complexity to the database, and it increases exponentially with each time this structure is used. Imagine if you have information in three or more different forms that are stored like this that are linked together and all the extra code that is required to keep things straight.
In this case, the easiest solution, as I implied above, would be to maintain a single table with a status column to no longer make the status implied, but explicit. This not only makes any coding linked to the table much easier, but if you are going to do any reporting or allowing non-developer business analysts access to the tables, it will be much easier for them to read the data.
The lesson here is to keep your tables simple and logically laid out. If you can avoid the hassle of moving your data all over (especially just to maintain implied statuses), you should. It will decrease the amount of work that needs to be done, and will be easier to read for anyone else who joins takes over your project.
Over the past few years, I've done a significant amount of Database Administrator (DBA) work in Oracle, Microsoft SQL and MySQL, and I've seen a lot of different approaches to databases. Needless to say, some have been more successful than others, and some have downright confounded me. While everyone has their own style and approaches, there are just some that are bad and should be avoided.
Hence, I've decided to create a topic on my blog here for Database Design Principles (DDP for short) where I can share the general mistakes I've seen, along with better suggested approaches to the problem. Some of these will be specific to certain database applications, but I plan on keeping things pretty general in order to help as many people as possible.
So keep an eye out for this new intermittent feature here on my blog!
Anyone who is interested in the current economic situation of the United States should take a few minutest to read Too Big to Fail? by Peter Goodman in The New York Times. It's a very terse look at the current American situation, and some of the questionable things that have been done in an attempt to prop up the declining economy. While the government is again jumping to bail out poorly run businesses, the article points out that is probably not the best option:
For one thing, this argument goes, taxpayers – who now confront plunging house prices, a drop on Wall Street and soaring costs for food and fuel – will ultimately pay the costs. To finance a bailout, the government can either pull more money from citizens directly, or the Fed can print more money – a step that encourages further inflation.
“They are going to raise the cost of living for every American,” said Peter Schiff, president of Euro Pacific Capital Inc., a Connecticut-based brokerage house that focuses on international investments. “The government is debasing the value of our money. Freddie and Fannie need to fail. They are too big to save.”
I know I was upset to hear that the government was going to bail out another poorly-run entity. The biggest problem is that these companies are being saved at the cost of the citizens of this country. Shouldn't things be happening the other way around?
Well, this week has been a fairly quiet one, as the only semblance of baseball that was played took place an Yankee Stadium. The media is still going on and on about the experience, presumably being paid by the Steinbrenners because I haven't heard a word from anyone else about it. It'll have to do as a send-off though, because it doesn't look like there's going to be much of interest going on at the stadium as the pinstripers are falling further and further behind the Red Sox and Rays in the division.
But now we get back to this crazy and wonderful season where nothing is what you'd expect. Few of the usual candidates are in line for any kind of awards this year, and a number of expected contenders and also-rans have swapped places. With two more weeks until the trade deadline, it looks like things could get a lot more interesting as well.
Most importantly, I'll have to get back to updating my fantasy rosters daily, which is a fun habit to be in!
Nearly a decade ago, I spent several weeks playing around with a music making package during my freshman year in college. This was before I knew how to play guitar, but really wanted to try and express my creative side. As a result of that experimentation, I created an “album” of techno and dance style music.
For many years, that music laid buried amongst my MP3 collection and a few white elephant gifts that I had given to unsuspecting souls over the years. But no longer…
Now, I have uploaded that album to share with the world on Last.fm. Introducing, DJ GuruGreg's first public album, Primero. Not looking to do much other than put it out there, and maybe cause some discussion. Just try not to be too harsh…remember, I was just experimenting.
Plus, if you've got some music you've recorded and you'd like to share with the world, Last.fm has introduced the Music Manager which will allow you to do just that. That's what I did. After all, what good are our creations if we just keep them to ourselves. Share them with the world, and you never know what might happen!
Were you one of the hundreds, if not thousands, camped out at various AT&T stores today to get your hands on a new iPhone? Ya, know…the one with the modestly enhanced features and overall price tag that justifies none of them.
Well, if you were, I feel sorry for you.
I'm so sick about hearing of iAnything that I'd ditch my four year old iPod Mini in favor of the vastly better designed Zune if I could justify the cost (since the Mini still works, even with zero battery life, it gets me by for now). Heck, I've already abandoned iTunes in favor of the revitalized Winamp player, and don't plan on ever looking back.
Of course, many would rather just believe the Apple hype than view things objectively.
I'm sure most of you know that over the past year and a half, I've been losing weight. Not drastically, but little by little by eating better and exercising more. And in about 15 months, I had managed to lose and keep off 30 pounds, going from a bloated 267 down to 237, and feeling much better about myself (even though the doctors say that may have contributed to my gallstone/gallbladder problems).
Well, in the two week span around my surgery, I dropped another 16 pounds, checking in last weekend at a svelte 221. I'm sure it's mostly due to the fact that there were about seven days in the hospital when I wasn't allowed to have solid food.
I guess it made such a difference that a number of my co-workers mentioned that I looked a lot slimmer. It's not how I'd normally like to lose weight (yes, the big jumps are nice, but are much harder to maintain), but I'll take it this time. After all, I'm not trying to devour everything that comes near me (though I have had cravings for food I hadn't had for a while). In fact, my appetite has been down so far, and we'll see if it stays that way as I get healed up.
Anyway, now that my clothes are getting baggy, I'm feeling even better about the way I look, and I'm going to keep on working to keep the weight off. At least within the limits of my healing process. That'll just mean more smart eating choices and plenty of walking for now.
Well, today Marci and celebrate three years of marriage, just a few months after celebrating the fifth anniversary of our first date. I know we both feel extremely blessed to have one another, and can't imagine life without one another. We've been through a lot together already in our marriage, but we always seem to be growing closer together.
Even though this year's anniversary snuck up on me (yes, major surgery can do that to you), I know we're both firmly looking towards the future together. We're in the process of buying a house for our family, which is already starting. That's right, while I was in the hospital, Marci found out that she was pregnant. I'm very excited to be a dad, but I've got to make sure I've got myself all healed up before then. So, we've just been grouping all these things together at once, it seems.
Of course, that's just how life goes though. You can plan all you want, but He may have something else in store. So we just continue to move forward in life, and grow in our love, knowing everything else will come together. That's how we've made it through the last three years, and will likely continue for many more.
Summerland by Everclear