When declaring the primary key (PK) of a table (eg USER), there are several options:
An existing field (
username
) can be placed as a primary key (natural key), so the username will be validated so that there is no other equal. For example:CREATE TABLE usuarios( username VARCHAR(30) not null; password VARCHAR(30) not null; tipo_u VARCHAR(10) null; PRIMARY KEY(username) ) ENGINE INNODB
A new field (
iduser
) can be added as a primary key (surrogate key), perhaps auto-incremental if desired. For example:CREATE TABLE usuarios( iduser INT not null AUTO_INCREMENT; username VARCHAR(30) not null; password VARCHAR(30) not null; tipo_u VARCHAR(10) null; PRIMARY KEY(iduser) ) ENGINE INNODB
Is one of the two more recommended? Which of the two ways allows a better performance in a database? What advantages does each method bring?
Is it more secure using the iduser? I really don't see much use for it, but I have seen that many sites use it like this.
Actually the way you choose will depend on the design you want to do in your database.
The issue of adding an auto-incrementing numeric ID to tables (surrogate key) instead of using existing fields (natural key) is a much-discussed topic that has its supporters and detractors because both methods have their pros and cons. .
Let's see some advantages and disadvantages of the two cases:
Surrogate keys (add an autoincremental iduser)
Advantage:
Disadvantages:
int
it is 4 bytes)... although this is usually compensated by the space that is saved later (see above).SELECT
, now you will have to add more fields and joins with the child tables.Natural keys (use username as key)
Advantage:
SELECT
is readable as long as it is valid.Disadvantages:
As in everything, there will be people who decide for one or the other. Personally, I prefer surrogate keys because, from past experience, I find them more flexible and take up significantly less space as the database grows.
I am going to give you two examples that I have found in the past, applied to your case:
Subrogated keys occupying less space
In your case, userid would be a
int
4-byte character and username would be avarchar(30)
31-byte character (1 for size + 30 for characters).If you add userid, you would be adding 4 more bytes to the original table, but in each row that is used as a foreign key in another table you would be saving 27 bytes (31-4) in the worst case (and in computing you always have to take the worst case).
That means that as soon as you have cad userid as a foreign key in a table, you would have already saved the added weight in the initial table.
Surrogate key being more flexible
Imagine that you have a database with dozens of tables and thousands (or millions) of records, many of them related to users and with userid/username as the foreign key. And now imagine that you have the user decide that he wants to change his username (he passes). What happen?
ON UPDATE CASCADE
do the work for you... but that's not always the case.And a close example: Stack Overflow allows you to change your username once a month (I think). And they have a userid key system (yours is 34527) as the primary key and then the username as a separate field which is easier for them to change.
1) The username cannot be auto-incremental, taking into account that it is one
varchar
and that the username will be entered through the form.2) The username can be put as
PRIMARY KEY
or asUNIQUE
The advantage of using an id of type int is seen when creating relationships with other tables. It also makes it easy for you to search for multiple users.
For instance:
Something you can't do with user
varchar(30)
.It is recommended to use both. PRIMARY KEY for iduser and UNIQUE for username
Note that
username
it is set toUNIQUE
This prevents the username from being repeated. The advantage of UNIQUE is that you can have multiple fieldsUNIQUE
in the same table unlikePRIMARY KEY
.To tell the truth, there is no difference at the Database level with respect to performance in it in the two forms that you expose, since in SQL queries it prioritizes the columns that have indexes, even more so if it is a primary key index. Namely:
If the username column is the PK of the table, its where is prioritized, while userFecCreacion, since it is not a column with an index, does not.
What I can limit is that in the first case you present, username VARCHAR(30), it would not be optimal, since the type does not help, instead of Varchar (since the field is a variable) I would use Char. Cheers
You must use identity this will make it unique and auto increment
CREATE TABLE users( iduser INT identity; username VARCHAR(30) not null UNIQUE; password VARCHAR(30) not null; u_type VARCHAR(10) null;
PRIMARY KEY(iduser) )