Possible Duplicate:
Why use an int as a lookup table's primary key?Why use an int as a lookup table's primary key?
So far, I'm accustomed to creating an ID column for every table and it is practical in a way that it makes me not think about decision making about primary key theories.
The professor in my university suggested the class to make primary keys from one or more fields which make up to one unique info about each column. And yes, I want to have a habit of applying natural keys instead of surrogate keys. On Wikipedia, the advantages and disadvantages of surrogate keys are listed, I strictly recommend This Article
I have seen people use integer ID fields for everything and nobody judges this method because
- it "looks" efficient
- a number field is used and it looks cooler because of its size per row in memory
I am beginning to think an extra ID field is just creating redundant data with no actual benefit. So why should I create an ID column when I can use other columns as key fields?
- If your ID field is 32 bits, it's equivalent to 4 ASCII characters already.
- If your Id field is 64 bit integer, it's 8 characters string so it doesn't actually save that much of memory (what implied here is the memory used on comparison. extra id column is already adding to the memory used (both HDD and RAM) )
- An extra ID field doubles your indexing cost because you will also index a unique field you can use as a primary key.
- You make extra joins if you need the data you could have used as a key field, for example, if you stored a unique user ID in one blog post, to show the name of the author, you make a join query, if your key field was the author's name, you don't need to join because you store the relevant data in the blog post table. a foreign key field with meaningful data reduces the need for subquery or join
- Creating an extra id field "adds" to the memory load, it's not a replacement of a unique string field, you are not replacing a char-varchar field with an integer, you are adding an extra column and it creates extra data flow. so any comparison of data store should be done between "string" and "int+string". adding an integer id field does not save space.
on the other hand
- assigning a primary key data which gets value from user input, can be problematic because people can enter, for say, their social security number wrong and the actual person who wants to register won't be able to register because of the unique policy. This can be circumvented by adding extra digit or digits to the original number.
Extra resources:
My conclusion from reading articles is that I should use natural keys whenever possible instead of skipping thinking about natural keys and using surrogate keys each time, as if it is a standard.
