When designing a brand new table inside a database, what's the need for using id. For the reasons, we're using unique username and email in every area to complement the data with this unique username or email. What exactly is using id?

Also, what's the length/value area for? A new comer to this.

thanks a lot!

The id area is one particular surrogate key. It may be beneficial to utilize a surrogate key like a primary type in a database since it is totally unrelated to and for that reason untouched by exterior occasions within the real life.

Utilizing a natural key like the current email address might cause problems if a person changes their current email address your key will need to change. This could create difficulties because it will break foreign key contraints. It will likewise make querying for occasions relevant to some specific user with time harder as you've no guaranteed single key that's consistent for your user's entire history.

For those who have several database inside your company that requires the secrets, or else you export data out of your database with other programs or systems when you alter a type in your database you may even have to alter the secrets in individuals systems too, a thing that can't be done instantly by utilizing ON CASCADE UPDATE.

As others have stated, you will find two kinds of secrets for records: natural secrets and surrogate (artificial) secrets. The 2 major questions, then, are: must you make use of a surrogate key, and when so, what should that surrogate key be?

Regarding the first question: You only want to use a surrogate key for those who have no valid natural key to be used like a primary key up for grabs. All sane database systems offer the 'ON UPDATE CASCADE' clause, meaning if you work with an all natural key which transpires with change, the modification is going to be propagated to everything that is declared to reference it. Obviously, in case your database system doesn't support foreign keys, your best choice is by using a surrogate key, if perhaps to operate around the possible lack of functionality within the database system (and surrogate secrets can make your database simpler to consistency sign in light of this fact). Nevertheless, if you're creating a credit card applicatoin which has needs for top uptime and robustness, choose a database implementation that will get foreign secrets correct, or else you will probably discover that data integrity bugs is going to be found late in development (or perhaps maintenance) and you'll have to create utilities which will look at your data for consistency in a variety of modes of failure.

For that second question: If you are using a surrogate key, particularly if you will work around an insufficiency of the database system, you need to always address it as though it were immutable and globally unique. ALWAYS. This can help with many situations afterwards: companies can merge (and split), databases could be merged (and split), contributing to millions of other situations sometimes happens that are not anticipated once the database was created that can handle leading to problems when the surrogate secrets aren't globally unique. Since surrogate secrets aren't whatsoever associated with the information they hold (they've no regards to another fields within the table apart from the artificial one you have presented upon it) it is simply better if way. Therefore, after i must make use of a surrogate key, I personally use a UUID (that is basically a 128-bit integer, although not incremental). Now it's not necessary to be worried about renumbering record amounts and references when unpredicted occasions occur. (Yes, it will slow things lower, especially if your server is running on the 32-bit platform. But when you have to handle more load, distribute the burden better---don't sacrifice integrity for speed, ever, when you are dealing with important data!)

Relations between tables.

Is uneffective have regards to username or email address since this is a string and evaluating this values takes for a longer period, and indexes are bigger, optimal option would be add ID just like a primary key for relations with other tables as userid.