Best Practice Performance and Structure (1 Viewer)

[Thinh]

What is the best performance for storing information in a table number or text.
On my forms i have alot of combo boxes so i wonder which way will give more performance. The data in the table will be use to graph aswell.

Pros
1.number is easier to sort, count pretty much any arithmatic operatation
2. Data security, People who look at the table wont understand much because most of the data is represented by numbers.

Cons
1.takes up an extra field in the table for the number field due to combobox
2.hard to understand the data in the table since most of the data is represented by number(troubleshooting)
3. For display or for graphing you have to use a lookup to see the actual value

What is the best approach based on performance and ease of use?currently all my table field are text besides the date fields. Currently i utilized the concept "what you see is what you get". What they see in the comboboxes will also be physically shown in the table and not a lookup field.

My question is it worth using the number concept to later have to use a lookup to determine the value for display or graphing purpose or just use the text concept and only worried about that it takes longer to go throught a text string then integer value. Which of the approach will give me the best performance and stabiility.

Thanks in advance Thinh

 

[boblarson]

Storing numbers as foreign keys are optimal as indexing is faster and the descriptions behind them can be changed in one place but the number stays the same, so less database maintenance (as well as being normalized).

1.takes up an extra field in the table for the number field due to combobox

This is not really true. The number used should just be an autonumber ID and the table has the description.

2.hard to understand the data in the table since most of the data is represented by number(troubleshooting)

Not really as you can pull together a quick query to look at everything

3. For display or for graphing you have to use a lookup to see the actual value

not true, you just have to use queries (which is what you should be doing anyway, and you just link the appropriate tables together and then you can place the fields you want in the query and you don't have to use the number but can use the text at that point.

And when you use forms to pull all the information together, the user never even knows about how you are storing the data. See an attached simplistic sample.