Store representations in SQL rather than whole strings?

I want to mark my users with one out of four static strings: indestructible, unstoppable, superefficient and megafantastic.

Would it be faster and more efficient to store a one char representation of the string rather than the whole string? Sort of like a key<->value mapping. The DB would on开发者_C百科ly need to match one letter instead of a whole string when I do a search.

In the DB these words would only be represented as i,u,s and m. On the users profile I print indesctuctible if I get an i, unstoppable if I get a u etc.

Do I actually speed up anything by doing like this or is it just unnecessary work?

What if I have 10 more of these representations?

---

If you are talking about database design, you should have a normalized design.

Create a lookup table id/name, store the id value in your user table (use something like a tinyint).

ID  Name
1   indestructible
2   unstoppable
3   superefficient
4   megafantastic

This way you can query the database directly for the string.

If you cache this table, you will not be hitting the database for these values, and you will only be storing a small amount of data per user (not to mention transfer less data over the wire) - these all translate to better performance on your DB.

---

Sure, you might see a little performance boost. That said, it may not be worth it if you compromise your database design. This is a normalization question. Without knowing how you're planning on using the data it is difficult to say exactly how you should structure your data.

---

I think it is a waste of time. Beware of micro-optimizations. For such a reduction you would loose all meaning in the database for that column, loose the flexibility to have values that start with same letter, have to modify your DAL to pass in just the first letter when given a whole word.

I would normally index the column and leave it as full strings. If that was the bottleneck in your system then your writing the fastest, most efficient programs in the world and you don't need my humble advice anyway ;-).

---

Yes this should make your code faster, using a code 1,2,3,4 would be even fastest for 2 reasons

1) It takes less time to compare an int(that can be compared in 1 CPU cycle) than comparing two strings. Even 1 char may take more cycles because it needs to work if 'A' equals 'a' etc.

2) it takes up less space in the table, in a DB the smaller your rows are the faster your DB will be becaus SQL will be able to fit more rows onto a page.

You may need to have a serious number of users to notice the difference, but it would defenitley be good practice to store codes instead of values.

Regards GJ

---

You could create a lookup table and which has a fk into the main table

Character
------------
CharacterID
Name
ProfileID int (FK)

Profile
--------
ProfileID
ProfileDesc

where profile table is

ProfileID, ProfileDesc
----------------------
1        , indestructable
2        , ...

This would allow you to add a smaller more efficient index onto the ProfileID in the Character table

---

If you're looking to maximize your performance and don't care about normalizing this data (which is almost always the best option), you could store those 4 attributes as bits:

CREATE TABLE <name> (
    <other fields>
    isIndestructible bit, 
    isUnstoppable bit
    isSuperefficient bit
    isMegafantastic bit
)

It's bad practice in that you'll be SOL if you decide to add "isMagnificent" or change "isIndestructable" to "isResilient", but if you've really just got 4 attributes you're tracking, 4 bits are hard to beat.