Database Best Practices for index values

65 pts.
Tags:
ASP.NET
SQL Server
SQL Server 2008
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:
  • SYSTEM_FAILURE
  • UNAUTHORIZED_REQUEST
  • UNEXPECTED_DATA_VALUE
  • UNRECOGNIZED_KEY
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... Dave

Software/Hardware used:
ASP.NET & SQL Server 2008

Answer Wiki

Thanks. We'll let you know when a new response is added.

Listen to the ‘database side of you’. That approach has many benefits, including normalization and data integrity.

—————–
Tables are very useful for parsing, particularly where the values are static.

Another benefit –
Parsing text down to keys or ‘tokens’ and referencing via the table can greatly reduce overhead.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Kccrosser
    Concur with Carlosdl - your tables will be much smaller (integer values instead of strings) and searching for matching integers is WAY faster than searching for matching strings.
    3,830 pointsBadges:
    report
  • Rick Martinez
    Carlos is right. What you can also do is create a table with an unique id column and a group column. That way you know what values belong to what group and you will always have an id for each value.
    585 pointsBadges:
    report
  • Overtech
    Thanks guys, guess that just confirms what I already knew I should do. It would help if the .NET guy and the database guy got along better, I get tired of listening to them argue all the time... ;) I'm not sure I follow the part about the group column though..?
    65 pointsBadges:
    report
  • Kccrosser
    I think what RickMartinez is suggesting is putting all the coded values into a single table, with a "group name" column that contains the "type" of the pick list items, a column containing the full text of the item, and a numeric index for the record. The alternative is to have a separate table for each "group" of pick list items. For this application, his suggestion may be the best. You can set up your "lookup" table like: table MyPickLists ( RefID int not null, GroupName varchar(32) not null, ItemText varchar(255) not null, constraint pkMyPickLists primary key RefID); Then, your SQL joins would always be against this single table, rather than having a separate table for each "group" of pick list items. I.e., your joins to retrieve the displayable text of an item are all: ... join MyPickLists mpl on mpl.RefID = <datatable>.<picklistidcolumn> With multiple tables, your code would have to explicitly reference the specific code table. Not a big deal, just an alternative. Note that if the text of two or more of the pick list items could be the same across multiple groups, then you may need to initially resolve the reference id by a combination of the group name and the item text.
    3,830 pointsBadges:
    report
  • Overtech
    Now you see, that just shows how little I know about maintaining a database. I've never thought of anything like that but it sure seems like a brilliantly simple method..! Now I just wish I had waited a bit longer before I dove into it because I already went through the tedious task of creating a table for each group of result values. Then again, I haven't really started on the source code for the web app to retrieve the information, so it might be worth going back and doing a redesign on the tables anyway. It sure would make the SQL statements that I will need much simpler and straight forward... Thanks again for the great suggestion..!
    65 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following