CODEWITHSUNDEEP
database
Muhammad Fahad Nadeem
Muhammad Fahad Nadeem

Reputation: 1142

Is it better to have Id attribute in model inspite of no need to use it to uniquely identify records rather we have another unique identifier?

Is it better to have Id attribute in the model as the primary key despite of no need to use it to uniquely identify records? I am using asp.net core and entity framework, this is my model

    public class PreRegistration
    {
        public int Id {get; set;}
        public string PhoneNumber {get; set;}
        public string VerificationCode {get; set;}
    }

Is it anyhow useful/better for my model to have an integer Id (auto-incremented) when I can easily use the phone-number for identification purposes? In ASP.NET Identity Core, the IdentityUser model has an Id attribute when we can easily use Email, UserName, etc attribute to uniquely identify the records. Why is it so?

Upvotes: 0

Views: 682

Answers (2)

Neville Kuyt
Neville Kuyt

Reputation: 29629

Your question boils down to "should I use a surrogate key or a natural key as my primary key?".

There are 3 requirements for a primary key.

Uniqueness - a primary key must be unique, even in the face of typos, or geography, or data changes. Names are definitely not unique. A phone number is unique within a geography, but you'll need to include international dialing code to guarantee uniqueness. Your data model include a verification code, but not a "status" - so a user entering the wrong phone number could create duplicates on phone number. The combination of phone number and verification code would be unique, but you wouldn't want to use it as a foreign key because you wouldn't know which record has been verified.

Not null - this depends mostly on your business logic, but if there's a state in which you don't yet know the phone number, but want to link the record to other tables, it wouldn't be suitable.

Invariant - you may think phone numbers don't change, but this isn't true. In London, UK, we've had several changes to phone numbers over the last 30 years.

So, the answer to your question is probably to use a surrogate key.

Upvotes: 0

Tad Harrison
Tad Harrison

Reputation: 1293

It is a best practice to have a surrogate key on a table for a few reasons:

  • Phone numbers, email addresses, and usernames change, even if you are promised that they never will. It might be as simple as the need to allow for fixing typos made by users during data entry.
  • Numeric keys are easier to reference in a data model. Each foreign key reference will need a copy of the key value.
  • Numeric keys are very easy to index and are supported by Identity column mechanisms.
  • Some frameworks do not work as well for updates and deletes without a simple numeric key.

There may be other good reasons, but the most compelling is the first point--the difficulty of changing data that has been used as a primary key and referenced as foreign keys.

Of course, the phone number is likely required to be unique if you were intending to use it as a primary key, so go ahead and add a unique constraint on phone number.

Upvotes: 0

Related Questions