The answer to your question is logical, not physical - the value you look up might change for business reasons. For example, if you index your customers by email address, what happens when an email address changes? Obviously this won't apply to all your lookup tables, but the benefits of doing it the same way across the entire application is that it makes your code simpler. If everything is integer → integer relations internally, you're covered.
Just read your comment to Sandy - perhaps in this case what you really want is a Check Constraint, not a foreign key/lookup table, e.g.:
create table icecream (flavour varchar(10))
go
alter table icecream add constraint ck_flavour check (flavour in ('Orange', 'Pista', 'Mango'))
go
insert into icecream (flavour) values ('Orange')
go
insert into icecream (flavour) values ('Vanilla')
go
Run this and you get:
(1 row(s) affected)
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "ck_flavour". The conflict occurred in database "GAIUSDB", table "dbo.icecream", column 'flavour'.
The statement has been terminated.
This is an efficient, high-performance method, but the disadvantage of course is that adding a new flavour means a code change. I would advise against doing it in the application - because then you need to do it in every app that connects to this DB, this is the cleanest possible design because there is only a single code path for doing validation.
Or is a tier of tables, or splitting to different tables based on dates, or per amount of users, or something else?
You may want to look into the concept of 'partitioning' in your database. Most RDBMSes have some support for them (eg, mysql, oracle, sql server, postgresql). Basically, you let the RDBMS handle the process of creating/managing the fact that each month/year/whatever is stored in a separate table, while the code accessing it treats it as one large table.
You could partition it by user name, date, or whatever's going to be used most frequently to access the data. (there are advantages / disadvantages of making it user-centric vs. date-centrid ... but I don't know if you want me going into all that)
Best Answer
All a primary key is is a value that we have determined is the value that is of utmost importance in a record. Whether that key is a signed int, an unsigned int, a string, a blob (actually, there are limits) or a UUID (or whatever name it takes today), the fact still stands that it is a key, and that it is the thing of utmost importance.
Since we're not constrained to use only positive oriented numbers for our keys, it makes sense to consider that a signed int will only go to ~2 billion, whereas an unsigned int will go to ~4 billion. But there's nothing wrong with using a signed int, setting the initial value to ~ -2 billion and setting an increment of one. After ~2 billion records you'll hit "zero" and then you'll continue to ~2 billion.
As to why it would be helpful to have "negative keys" in a table, that's the same question as "why is it helpful to have keys in a table". The "value" of a key has no impact on its status as a key. A key is a key is a key.
What is important is if the key is valid.
As to why it would be useful to allow keys that were negative, I can suggest some reasons:
What if you wanted to indicate returns in a sales system as negative sales order numbers, that matched the positive sales order number, thus making correlation easy (this is naive, and poorly designed, but it would work in a "spreadsheet" sense).
What if you wanted to have a users table, and indicate that the ones with negative numbers were system controlled (SO does this very thing, for chat feed users).
I could go on, but really the only reason why the number being negative is of importance is if you or I assign importance to it. Aside from that, there is no great reason for the value of a key to have any bearing on the key itself.