Simple database design and LINQ

Question

I have very little experience designing databases, and now I want to create a very simple database that does the same thing I have previously had in xml. Here's the xml:

<services>
  <service type="writing">
    <small>125</small>
    <medium>100</medium>
    <large>60</large>
    <xlarge>30</xlarge>
  </service>
  <service type="analysis">
    <small>56</small>
    <medium>104</medium>
    <large>200</large>
    <xlarge>250</xlarge>
  </service>
</services>

Now, I wanted to create the same thing in a SQL database, and started doing this ( hope this formats ok, but you'll get the gist, four columns and two rows):

> ServiceType Small Medium Large
> 
> Writing       125    100    60
> 
> Analysis       56    104   200

This didn't work too well, since I then wanted to use LINQ to select, say, the Large value for Writing (60). But I couldn't use LINQ for this (as far as I know) and use a variable for the size (see parameters in the method below). I could only do that if I had a column like "Size" where Small, Medium, and Large would be the values. But that doesn't feel right either, because then I would get several rows with ServiceType = Writing (3 in this case, one for each size), and the same for Analysis. And if I were to add more servicetypes I would have to do the same. Simply repetitive... Is there any smart way to do this using relationships or something?

Using the second design above (although not good), I could use the following LINQ to select a value with parameters sent to the method:

protected int GetHourRateDB(string serviceType, Size size)
{
  CalculatorLinqDataContext context = new CalculatorLinqDataContext();
  var data = (from calculatorData in context.CalculatorDatas
        where calculatorData.Service == serviceType && calculatorData.Size == size.ToString()
        select calculatorData).Single();
  return data.Hours;
}

But if there is another better design, could you please also describe how to do the same selection using LINQ with that design?

Please keep in mind that I am a rookie at database design, so please be as explicit and pedagogical as possible :-)

Thanks!

Anders

Answer 1

You can use your first method and do something like this:

protected int GetHourRateDB(string serviceType, Size size)
{
    using (CalculatorLinqDataContext context = new CalculatorLinqDataContext())
    {
        var data =
            (from calculatorData in context.CalculatorDatas
            where calculatorData.Service == serviceType
            select calculatorData).Single();

        switch (size) {
            case Small:
                return data.Small;
            case Medium:
                return data.Medium;
            case Large:
                return data.Large;
            default:
                // Error handling
         }
     }
}

Your second method would also work, but if you know that there will always be exactly three sizes for all items, and that they will always be called Small, Medium and Large, then by using the first methods you can hardcode that into the schema and get the database to enforce it for you.

You might also want to consider if it would be better to use an auto-incrementing primary key instead of using the service name as the primary key. Names have an unfortunate problem that they often change for reasons outside of your control (fix typos, marketing, translations, etc.). You might want to avoid having to recompile your program every time a name changes.

Answer 2

If you want to use an enumeration to select you desired field, go for Mark Byers answer. I just wanted to show up another way to do what you want: just use a lambda for selecting the field, like you are doing in many other Linq methods:

protected int GetHourRateDB(string serviceType, 
                            Func<CalculatorData, int> fieldSelector)
{
    using(CalculatorLinqDataContext context = new CalculatorLinqDataContext())
    {
        return fieldSelector(
                  (from calculatorData in context.CalculatorDatas
                   where calculatorData.Service == serviceType
                   select calculatorData).Single()
               );
    }
}

You would use it like:

int someSmallValue = GetHourRateDB("Writing", x => x.Small);
int someMediumValue = GetHourRateDB("Writing", x => x.Medium);

Be aware that Single() throws an InvalidOperationException if there is no element in the database with the given serviceType. You have to handle that some way.