2

For each row entered, the user will have one piece of data in either grams, kilograms or pounds. I supply 3 fields and the user must enter in one. The app then calculates the other two values off the entered value.

I am wondering if it is best practice to store all 3 values in the database, or just store the single value entered and calculate the other 2 as needed for display and reporting purposes.

7

For data this simple to calculate, it does not make sense to store denormalized data (all three).

Store only the grams and user in the database, then load the user and grams properties of this object with the stored data.

public class Weight
{
     public string User {get;set;}
     public double Grams {get;set;}
     public double KG 
     {
         get { return Grams * .001; }
         set { Grams = value * 1000.0;}
     }
     public double Pounds
     { 
         get { return Grams * 0.00220462; }
         set { Grams = value * 453.592; }
     }
}

and for storage, we can see the benefits of the repository pattern for cases like this where our optimal storage solution does not resemble our business use case:

public class WeightRepository
{
      public void Insert(Weight weight)
      {
          sqlConnection.Execute("insert into Weight (User, Grams) values (@user, @grams)", weight.User, weight.Grams);
      }

      //Get, update, ect
}

With this approach, our business logic (Weight class) is isolated, convenient to use, and easily unit tested.

We can also get blazing fast reads from our DB since we have minimized our number of columns.

Usually, good architecture increases performance, not hinders it :)

8

It depends!

If possible, I'd always try and store everything in the database in the same unit. Makes comparisons, searching and lots of other things much easier ("Find me all the widgets over 1kg").

However, I've also been in situations where it was important that the user always got back exactly the same data as they entered - if they put in 1.00000437546535 lb, they want back 1.00000437546535 lb, not 1.00000437546536 lb as might happen if you convert to kg and back. In that case, you have to store the original form in the database and convert as needed.

1

Depends

If your table is normalized (specifically if it is meant to be in third normal form, BCNF, or higher), then every column must be an attribute of the key, not an attribute of something else.

Let's say the table is meant to represent a set of measurements, where each measurement is one row. The value entered by the user is an attribute of the measurement. Values computed from that value are not; they are attributes of the value, which in turn is an attribute of the measurement. Therefore, storage of those values would violate 3NF.

If you are using a denormalized table you might consider pre-computing those values and storing them in separate columns. Not only could this reduce computation time if you ever need those values again, but storing them would also allow you to index the values, e.g. if you needed to sort or search not by the data entered but by the result of the computation.

Computed columns. SQL Server also has a nifty feature called Computed Columns, and these can be indexed as well. So assuming you can do the calculation in T-SQL, you could add the values as computed columns, which will look like normal columns but take up no storage space.

In your case, I would not store the computed values. If I needed to be able to search the table by the result of computation, I might consider adding a computed column and an index on that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.