Why use numbers for index?
I have limited database experience and am about to try a big project, my question being what's the advantage to using numbers for the index of records rather than just making user name the primary and unique so you can always find the record by the name?
Such as keep track of users:
Why not say select * where user='john' ?
What's the purpose to having
Or is there any advantage?
an id number can be auto-incremented, so it's useful for things that need to be kept in order.
Also, by doing so, you guarantee that you won't have two rows with the same id (e.g., you'll have #1 (john) and #2 (john) instead of john and john (which would cause problems).
If you set it to unique wouldn't that prevent this issue? If not, it would be easy to check if a row exists under john right?
EDIT: Also you'd want to prevent there from being 2 user names John anyways because of log in, there couldn't be 2 identical user names.
1. "UNIQUE" would prevent duplicate ids, but by throwing an error instead of solving/working around the problem.
2. Checking if a user id exists is easy, but if when your table gets bigger, it can become time/resource-consuming.
3. Facebook (for example) allows two people to have the same name, just like RL. IMO, using some alternate value as a username (id#, or email is a really good one) is better than telling someone "john's already taken, so you have to change your name." Unique usernames (not real names) is desirable in many cases, however, so it would depend on your application.
One other point to consider is that a number (integer) is a lot quicker to code for/ work with than a name (string).
The primary purposes of an index number are to provide a value useful for comparing magnitudes of aggregates of related variables to each other, and to measure the changes in these magnitudes over time. Consequently, many different index numbers have been developed for special use. There are a number of particularly well-known ones, some of which are announced on public media every day. Government agencies often report time series data in the form of index numbers. For example, the consumer price index is an important economic indicator. Therefore, it is useful to understand how index numbers are constructed and how to interpret them. These index numbers are developed usually starting with base 100 that indicates a change in magnitude relative to its value at a specified point in time.