I'm more of a .NET than a database guy, but sometimes we just do what we've got to do. Please bear with me if this is an elementary question.
One of the apps I'm working on now will have to parse data from another system and store the values in the database. The data coming from the other system is a set of about 37 values and most of those values can be one of several possibilities (rather than free text).
What would the best practice be in this situation? I can either just store the plain text value parsed directly from the email, or I can build a table for each value type of the distinct possibilities then just store the index value in the record. (I hope that made sense).
For example, one of the individual values in the set I would be expecting is called the "TCAP Error Code" and it will always be one of these strings:
It would be quick and easy from a programming perspective to just store the string value in the database. But my limited database experience tells me to build a table assigning a numerical value to each of these 4 possibilities and just store the numerical index value in my table when I parse the results.
Another value is called the "Position Source" and it will always be one of 13 possible string values. Some of the other values only have 2 or 3 possible string values.
14 of the 37 values fall into this same scenario, meaning I would need to build 14 different "index tables" and programmatically parse each value in order to store just the index value in my table.
The programmer side of me wants to just split the raw string values and let the database guy (me) worry about how to store them. The database side of me hates the idea of storing thousands of records each containing the same string values over and over again...
So, I figured I'd throw it out there and see what the experts would do...