I've got a user table where you will find 100s of countless rows and there is a area username(varchar), must i turn it into a primary key rather than a special index? do you know the advantages or disadvantages of adding an additional area user_id(int) making that the primary key rather? I do not see where I'd use user_id except say on join condition in which a join on int could be faster than join on varchar? or perhaps is it?(since both fields are indexed)
update: assume altering username isn't a choice.
The main reason I favor a number PK is in order to easily permit the username to become transformed.
When the username can also be the main key, this means that records that report to that particular user should also be transformed once the username is transformed.
Observe that your database can create the proper ID for that number PK through numerous means. On MySQL it's adding an "auto_increment" attribute towards the area, on Postgres and Oracle it's through sequences.
For those who have 100s of countless rows though, you're factual that you might be better while using username. I avoid getting variant PK's going swimming between tables, it simply helps make the whole factor harder to keep for individuals who follow me in to the code unless of course it's essential.
I'd prefer adding an additional area because the primary key.
The primary reason, is the fact that -imho- primary secrets shouldn't have any 'business' value. A principal secret is basically an adminstrative item, that is only vital that you the database to ensure that integrity could be garantueed.
As John already mentionned, with the addition of a surrogate primary key, you are able to -inside your situation- allow that the user changes his username without problems.
The need for a principal key will not be transformed: otherwise an update may become very costly, if you have plenty of foreign secrets. All individuals changes ought to be cascaded towards the related tables.
Alongside that, an integer is for example 4 bytes, while your usename column is a lot bigger.
This doesn't only mean that you will be trying out a lot more space in related tables, it implies that your index will grow bigger.
The containers that comprise your index will contain less 'record pointers', meaning you will have more containers, so your index is going to be reduced.
To begin with, I second Frederik's comment: I'm a firm believer in not ascribing any company or functional value towards the primary key of the table. There might not be a choice of altering the username now, but possibly you will see later. Even when not, it's better to get involved with the habit of smoking and become consistent wonderful your tables instead of mixing paradigms.
Another reason behind utilizing a number (or consecutive in some manner) primary secret is insertion increase speed. While this is often transformed, automatically the main key on the table can also be the clustered index. The clustered index determines the physical order from the rows within the table, so placing something from order causes the database engine to change all the rows after it lower to ensure that it may place it within the proper location. Having a table with countless rows, that is one non-trivial place or update operation.