Setting up a base table is a one-piece auto-increment of a standard primary key?

Question

I have a web application, I have a concept of users who are likely to go into the user table, for example:

table: user username (varchar 32) | email (varchar 64) | fav_color | ... 

I would like the username and email address to be unique, that is, I cannot allow users to have the same username or the same email address. I see that in examples of tables of this type, the primary key is always entered with auto-increment.

Not sure why this is done, does it somehow speed up queries using foreign keys? For example, let's say I have another table like:

 table: grades username (foreign key?) | grade 

Is it inefficient to use a username as a foreign key? I want to make queries like:

 SELECT FROM grades WHERE username = 'john' 

so I think it would be faster to do an integer search for the database ?:

 SELECT FROM grades WHERE fk_user_id = 20431 

thanks

Answer 1

What you are asking for is a somewhat design decision based on the judgment of an individual data designer. Personally, in this case, I would include a primary key with auto-increment. It is unusual to guarantee that the username (and even more so the email address) will be unchanged. However, you can create your software so that the same integer primary key always refers to the same user, regardless of what else may change in this user record.

To help users search performance, there will be a UNIQUE restriction on the username with an index that matches it. If you really want your email addresses to be unique (basically a solution for business requirements), you could also set a UNIQUE restriction on the email address. Foreign keys are ignored in the default database in MySQL (unfortunately), so I will not worry about the advantages there are in the perspective of data modeling.

Edit:

I think I will go in favor of foreign keys if they are put into effect now. Yes, there are provisions for updating all data that depend on a foreign key (for example, UPDATING CASCADE). However, they are often poorly understood and considered difficult to maintain. It is usually best to use a foreign key for something immutable, hence your solid primary key.

Answer 2

My advice, after many years of creating db

only use symbols as PCs when they do not represent anything in the real world.

The real world is a chaotic place, and as soon as you use PK from it, you are one slope.

Just trust me.

(and there is also a speed gain).

Regards, // t

Answer 3

This may not necessarily be the “standard” as such, but it is fast, easy, convenient and generally resistant to changes in the business key.

See also: Pros and cons of auto-increment keys on each table

Answer 4

Integers as the primary key will make your future life much easier as your application evolves. Use the index for username and / or email to optimize the query.

Answer 5

I like whole keys because:

• make connections faster
• smaller and faster indexes
• never need to change (you may need to change your username and email values)

Answer 6

Integer column indexes run faster than with large character values. A primary key in a narrow identification column is the best solution.

Answer 7

Using real data as foreign keys is very problematic and "inefficient" because it violates referential integrity. Do you think usernames and emails are unique and will never change? You are almost certainly mistaken. Read an earlier question about natural keys.

Primary keys with automatically adding integers will be faster, but that’s not why they are used. They are used because they work. Use them.