Database Architecture Question

Question

I have a SQL Database that I am going to be interacting with via LINQ once built. It has a client who has (ignoring the others) two fkID fields to two tables.

Class
Country

What I am trying to do is store a "Rules" table where the following happens:

if the Class is 'A' and the Country is 'USA' then 
    create rows  'RowsA' in table todo

else if the class is 'A' and the country is not 'USA' then
    create rows 'RowsB'  in table todo

else
    create rows 'RowsC' in table todo

So, is the best way to do this to have in the rules table

RuleID Identity
ClassFK Int null
CountryFK Int null
order int not null

and have my C# code do the following:

var rules = from r in db.Rules
            orderby r.order
            select r;

foreach(rule in rules)
{
     if (((client.ClassFK == rule.ClassFK) || (rule.ClassFK == null)) &&             
           ((client.CountryFK== rule.CountryFK) || (rule.CountryFK== null)))
     {
          // Create The Rows
     }
}

This seems awfully flimsy to me

Answer 1

One way to make it a little cleaner looking I think would be to make a method on the Rule class that checks whether it applies to a client or not; That makes your code look something like:

foreach(rule in rules.Where(r => r.AppliesTo(client)))
{
    // Create the Rows
}

However, the part I see missing is knowing what rows to create based on a particular rule; This is not contained in your rules table.