Defining Many To Many

There are a lot of simple tasks that we humans can do with little effort, but when put into logic, can often become quite difficult to define. A great example of this is Many To Many Table Associations.

So, how do you programatically take a table and tell if it is a many to many association? My train of thought started off simple: the table has two foreign key columns. Then the flaws started rolling in...

1. Regular tables could have two for foreign keys.
2. It can't just be two columns, there could be time stamps or version numbers.
3. It can't just be two key columns, because there could be composite keys.
4. It may or may not have a primary key.
5. The primary key could be a composite key on the foreign keys.

...it seems that I had taken my human brain (specifically it's mad pattern recognition skillz) for granted! :) So after a brief discussion in the office (and on our forums), we came up with the following logic:

1. Table must have Two ForeignKeys.
2. All columns must be either...
   1. Member of the Primary Key.
   2. Member of a Foreign Key.
   3. A DateTime stamp (CreateDate, EditDate, etc).
   4. Match our Row Version regular expression.

Of course, there could always be other things out there that we didn't think of. In this world there are many technologies, with many conventions, used by many programmers, all unique in there own way. So, unfortunately, there is no truly simple answer, nor is there a perfect solution...however, that is why we here at CodeSmith always try to be as generic and flexible as possible in our designs! Also, it's why we love to use things like Extended Properties, and how we get our last criteria:

0) Bypass logic if table contains Extended Property for ManyToMany

So, finally, here is what the code might look like...

Note: This is (a slightly modified version of) what is in our NHibernate templates.

public static bool IsManyToMany(TableSchema table)
{
    // 0) Bypass logic if table contains Extended Property for ManyToMany
    if (table.ExtendedProperties.Contains("CS_ManyToMany"))
        return true;

    // 1) Table must have Two ForeignKeys.
    // 2) All columns must be either...
    //    a) Member of the Primary Key.
    //    b) Member of a Foreign Key.
    //    c) A DateTime stamp (CreateDate, EditDate, etc).
    //    d) Name matches Version Regex.

    if(table.ForeignKeys.Count != 2)
        return false;

    bool result = true;
    versionRegex = new Regex("(V|v)ersion");

    foreach (ColumnSchema column in table.Columns)
    {
        if (!( column.IsForeignKeyMember
            || column.IsPrimaryKeyMember
            || column.SystemType.Equals(typeof(DateTime))
            || versionRegex.IsMatch(column.Name)))
        {
            result = false;
            break;
        }
    }

    return result;
}