Last post Apr 29, 2011 12:30 PM by atconway
Apr 28, 2011 03:15 PM|panchoLopez|LINK
I'm building an educational site that will store a fixed list of about 5000 unique vocabulary words and associated data in a table. Users need to be able to save the words they're studying from this list and the the business code needs to update a status
integer as the user does various things with each word. I'm considering two different designs:
1. Give each word an id (a basic integer 1-5000) and when a user saves a word the word id, user id, and status are saved to a table
2. Instead of using an id for each word, just use the word itself as the primary key as each word is unique.
With 1 you have the extra step of looking up the word with the word id everytime you need to save or retrieve a word. With 2 you add size to your UserWord table (well, the handful of characters difference between "25" and "necessary" for example) but multiplied
by a large number of users storing 1000s of words each it would add up.
Or are these differences too small to really matter?
Any other suggestions or am I on the right track?
Apr 28, 2011 03:52 PM|chintanpshah|LINK
I would go with the first option. Comparing of Intergers are far faster than strings. Size also matters.
Apr 28, 2011 04:23 PM|abhisheks|LINK
I will go with first option as well. It's going to be faster than going the other route. Better to have int as primary key than a string, string comparison is always slow.
Apr 29, 2011 12:30 PM|atconway|LINK
I would make a Primary Key 'int' type field and call it a day. Allows for scalability if the need ever comes to have the same value twice (for some unforeseen reason or enhancement)and there are some performance factors to consider if the database got bigger.
Also when you tie it back to ASP.NET and databinding, I like the key,value pair relationship. Below is a link you might find interesting:
Primary Keys - int's vs. varchar: