How to store fungible data?

Question

I am writing a .net application that is taking form information to do some car loan prequalification calculations and will store this in a database. The trouble is every field should allow the user to type any kind of string data into it and preserve it into the database.

Most of the fields need to be converted to decimal or integer values to do any calculation so is it best to just ignore the data type and use strings in the database?
I haven't tested this approach but I would expect it to be difficult to find and sort the data correctly with this approach since every thing would be a string and need to be converted to the real data type to do a query.

One other alternative I have considered is to have two columns for every field one to hold the string value and one to hold the actual parsed value type. This sounds some what difficult to maintain.

lastly I have thought about storing one extra column or table that will just be a name value pair to put all the unparsable data for that record. I think this may be very difficult to create business objects against.

How do you tend to to deal with this type of issue?

Answer 1

I think your suggestion of using two columns for every field is the best approach. After all this is semantically what is happening.

When the user enters an amount, you want to store what the user typed and the actual amount so you'll need two fields.

When you create your application, make sure you group all data access together. This way you can make this solution maintainable.

Answer 2

Hi

I guess that you need to store what the user types in for a proper record of, well, what the user typed. So, that's one string per field called 'what_the_user_typed'. This might translate to a lot of columns in your table, but what the heck. Next, what useful data can you (or do you want to) parse from that string ? I'd guess about one value per string. If, for example, you have a field labelled 'Age of Car' then you might want to parse from the string the 'age_of_car' or, even, 'year_of_first_registration'; so that's another column for the table. And so forth.

Regards

Mark

Answer 3

The only sensible way to scrub data is when it is entered, when the person with the knowledge to correct it is actually present.

So, in your web forms:

1. Use javascript to prevent entry of invalid characters.
2. Use javascript to validate fields before submitting them to the server, then display notes about what fields need correction and how.
3. And on the server, check all fields for validity and redisplay the web form with notes about what fields need correction and how.

Number 3 is important because if javascript is disabled (or someone tries to hack the form submission), the form and validation will still work properly.

I guarantee that you and your system will suffer down the road if you let people enter bad data and try to parse it in the back end. (Note this isn't the same as incorrect. Bad means it doesn't even conform to the basic requirements for the field such as text in a numeric field, or negative income, or a name consisting entirely of spaces.)