Denormalizing data: store foreign key or not?

When you denormalize, do you store the foreign keys?

The situation is as follows. I have a normalized database that stores a form template that people fill out to report their experience in different skills. The template form will change over time. Phrasing will change, and the number of skills will change. Once submitted, their data will not change.  Users should be able to recreate the form that was originally submitted no matter how much the template may have since changed. Therefore, the system should capture the state of the templates when people submit their reports.

In these sorts of situations, I denormalize the data, storing the template data as well as the responses.

I could leave it normalized and enforce logic that deactivates a template (in whole or part) when it changes, but that is, in my opinion, generally overkill.

My question is, should I store the foreign keys? When I used integer keys (all row ids are surrogate keys), I would have stored them just because integers are cheap and having them might help troubleshoot problems. Now that I’m using GUIDs, I’m less certain the potential benefit is worth the cost.

It’s unlikely the use of the denormalized data in this specific instance will be so intensive that performance is likely to be a problem, but it will grow, it is storing mostly string data (mostly nvarchar). I’m also curious what people think about this.

I’m not completely close-minded, but it’s unlikely I’ll change my mind about using GUIDs and denormalizing. Given that, what’s your opinion on storing foreign keys in denormalized data?



Refactoring: VFP form calls external PRG, uses Publics for shared data

I’ve inherited another legacy VFP application and am updating a routine to use a new format required by my client’s customer. There is a VFP form that eventually calls a program to gather, format, and export relevant data. One of the first things I need to do is change the form and program so their code can be independently tested. Once that’s accomplished, I’ll change the routines to generate the new format.

The question is, as always with this common situation is whether to pull the code into a “blob” method in the form and change all variables to properties, or call the program from the form with parameters. I realized my usual inclination is to use the former because eventually I would be binding the form properties to properties on future business classes that I’d create down the road. It’s odd how I’ll suddenly think of a usual problem in a different light. This is a routine that’s run four times a year, the new format is needed soon, and the client wants to begin planning for a new application to replace this one. Visual FoxPro will probably not be the tool of choice, so there is no reason to build the Queen Mary when a dinghy will get us across the narrow inlet just fine.

Can you tell I’m prone to make everything into A Work Of Art? Maybe I’m changing after all.

Between a rock and a hard place: Visual Studio 2010 service pack and LightSwitch

I wrote the following last week, but had to postpone publishing to work on something else. I’ll leave it intact, but conclude with an update that makes the rant only partially relevant.

Trying to use Visual Studio 2010 for developing three new projects over the past 6 months has felt like clawing my way out of a peat bog while wearing concrete overshoes: every step is excruciating and unproductive. I expected it to be a steep learning curve since I was jumping straight from VS.NET 1.1 to Visual Studio 2010. I did not expect to have so much pain just sussing out which combination of techniques and frameworks would be stable and productive. A steep learning curve is one thing: finding brick walls suddenly springing up at every third step is another. Continue reading

Climbing out of the morass; chipping away at obstacles

My current chores-and-knitting audio book is Blind Descent by Nevada Barr, which is set in Lechuguilla. The story’s murder could be a metaphor for my experience working with the current Visual Studio technology. The non-billable time may well kill my business and livelihood, but I’m not ready to give up. So, today, I am thinking of the expedition as the better, somewhat less bleak, metaphor. Simply trying to wrap my head around add-ons, toolkits,  products, domains, and appropriate uses to assess the viability of any tool has been very much like journey to the center of the earth.

  • Squeezing through spaces so narrow I can’t look ahead to where I’m going? Check.
  • Navigating huge spaces filled with refrigerator sized obstacles? Check.
  • Rappelling down narrow shafts with knife sharp protuberances? Check.
  • Climbing past fragile features? Check.
  • Doing all this in utter darkness except for the questionable beam of my own intellect? Check.
  • All sense of direction lost? Oh, my, yes.

Fortunately a long-time friend of mine is in town for the annual Microsoft Most Valuable Professionals Summit. Reluctantly, I asked Kathleen Dollard to review my project plans for two customers who’ve been waiting patiently for three projects. Lately, I’ve missed my own time as an MVP, not only for the SWAG and camaraderie, but for the ability to plug into the internal state of MS development teams.  She can’t divulge NDA information, of course, but she has an extensive breadth of experience. Research is part of her job, so she tries, as much as is possible today, to keep a broad overall view of the programming landscape. Like me, she’s practical, believes technology serves customers (not the other way-round), and is willing to look outside Microsoft for solutions and examples of good practice. I was reluctant to ask for her help because she makes her living mentoring and consulting. Luckily, she was willing to work for breakfast, dinner, beer, a room, free wi-fi, and all the cat hair she wants.

She helped me tremendously, as have a few other references that are starting to come together as the most recent revs age. In short, she reassured me that, no, I am not yet senile. .Net programming is hard. Really hard. In addition, the programming landscape is in such a state of flux and tools so nascent that choosing an approach today that will be current in 2015 (one requirement) is a tough call. We went through various options, ranted about the general state of programming, drank wine, beer and ate soft tacos from Taqueria Guaymas, ranted some more, and wrote some code. Kathleen is, as we say, good people.

I’m reawakening this slumbering blog for two reasons: 1) I still believe writing helps me think better, and I’m finally at the point that I can fashion sentences that are, at least, coherent; and 2) to fulfill my promise to Kathleen to share my experience. Part of the joy of being a Visual FoxPro developer was the tightly knit, experienced community. Collectively, we knew just about everything there was to know about the product, how to use it well, what to avoid, who to listen to, who to ignore. I still believe the best advisers are programmers who write live, production software. As smart as the folks are at Microsoft, and they are,  field experience will always be more valuable for defining best practices. I say that because I believe any best practice has to take into account the entire scope of a project with heavy emphasis on deploying deliverables and measuring how well applications work for the customer.

There is a wealth, or maybe welter would be a better word, of advisory flotsam on the Internet seas, and I don’t want to contribute to the noise. I want to document what I’ve found to be helpful, obsolete, or gratuitous. If anyone believes this blog is too much noise and too little signal, let me know in comments, please.

Meanwhile, as I sort out my thoughts, concerns, trials, and decisions, please feel free to note your own thoughts. I told Kathleen at one point that I don’t see myself as particularly special. If I’m struggling with something, have a question, or want a feature I know there’s someone else who feels the same. It doesn’t mean that my point-of-view is viable or relevant, but it does mean it might need addressing. Because I’m also not a complete idiot. That’s a mantra I’ve been repeating to myself lately. I am not an idiot; this stuff is hard. I’ll cover the following broad areas: vocabulary, technologies, helpful links, wishes, and ruminations on the state of independent programming.

I like feedback. Don’t be shy.

Get Stuff(ed): formatted Datetime string

ETA: 2009.12.02: This code is in version 8. As the inimitable Doug Hennig (fangirl squeal) points out in comments, VFP 9 TTOC() has a new flag for returning an XML DateTime format. I would still use one of the options below because they are version-neutral, and because Set Date Short | Long affects the return value (the setting from Windows Control Panel is used).

Well, that and because sometimes one simply must write code that makes one giggle.

Silly, but this code amuses me.

? Stuff( ;
  Stuff( ;
  Stuff( ;
  Stuff( ;
  Stuff( Ttoc( Datetime(), 1 ), 13, 0, ':'), ;
  11, 0, ':'), ;
  9, 0, ' '), ;
  7, 0, '.'), ;
  5, 0, '.')

ETA (2009.12.02): Another way to do the same thing as above is the following:

?Transform( Year( Date() ) ) + "." + ;
  Padl( Transform( Month( Date() ) ), 2, "0" ) + "." + ;
  Padl(Transform( Day( Date() ) ), 2, "0" ) + " " + ;
  Padl(Transform( Hour( Datetime() ) ), 2, "0" ) + "." + ;
  Padl(Transform( Minute( Datetime() ) ), 2, "0" )+ "." + ;
  Padl(Transform( Sec( Datetime() ) ), 2, "0" )