Sunday, May 09, 2010

LINQ challenge - an answer

Someone asked for an answer to the LINQ challenge I posted a while back.  The following is an answer to the "find all descendants of a given Parent" part of the challenge.  It cheats a little, in that it makes use of a variable external to the method (stack), but it prints out the descendants of whatever parent you feed it, so that's good. 

This is not the only way to get an answer to this particular problem, and I certainly don't guarantee it's the best.

Incidentally, if anybody knows a utility for HTML-formatting code examples in a Blogger-friendly way, I'd like to hear about it.

Assume a class called ForeignKey that has two fields, both strings, called ParentTable and ChildTable.  Assume also a variable list
defined earlier in the routine, that is declared as List<ForeignKey>

Action<string, int> f = null;
Stack<string> stack = new Stack<string>();

f = (table, level) => 
    list.Where(fk => fk.ParentTable.Equals(table))
        .Where(fk => stack.Contains(fk.ChildTable) == false)
        .ForEach(fk=> {
                        Console.WriteLine("{0}: {1}", level, fk.ChildTable);
              f(fk.ChildTable, level + 1);

You might be curious about what the level and stack variables are doing here.  The level parameter is so we have some way to indicate how many levels deep we are:  otherwise, we'd just be printing table names and not know where in the structure we were.  But if you don't care, leave it out.

The stack variable is there to guard against "loops", where a "descendant" of one parent is ultimately itself an ancestor of that parent.  If your hierarchy is one that cannot have any loops (say, a family tree) then you don't need to guard against them.  But it's a decent idea to do so anyway, in case your data has been corrupted. 

Thursday, May 06, 2010

When you can do without foreign keys

I had a boss once who said, after I'd reported that a database design was done except for the foreign keys, "Don't do that -- just a bunch of overhead, and if you code it right you don't need them."

I didn't say anything, but I remember thinking, "I had a ten year career fixing bad data!  A database that can defend itself against corruption is a wonderful thing!"

Someone else recently said to me that they didn't use to believe in foreign keys for the same reason:  if you write your business layer right, you don't need them to protect your database.  So the following is my summary of when you can get away with not having foreign keys in your database:
  1. If you are the only programmer on the app;
  2. If you are the only programmer who ever has or ever will work on this app;
  3. If no apps not under your control can make any changes to your database;
  4. If no ad-hoc changes or fixes are ever made to the database;
  5. If you and your tester (who may also be you) are blessed with the following conditions:
    1. Lots of experience;
    2. Plenty of time;
    3. Great thoroughness;
    4. A good memory for detail;
    5. Perfect understanding of both the database and app design and of the business problem
... then you can do without foreign keys.

But -- seriously, I'm asking -- why would you want to?