Simple database design and LINQ
- by Anders Svensson
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
