How to store phone numbers in a database

Read the number once, when it comes in, and store what it is: its E.164 form, a plus and the digits, as text, with a constraint that refuses anything else. Keep the extension, the country and the line type in their own columns, and what the person typed beside them. Every other form is computed from E.164 when you need it.

The column

Text, sixteen characters. E.164 allows fifteen digits, and the plus makes sixteen. A phone number is an identifier, not a quantity: nothing is ever added to it.
Not an integer. An integer drops the plus and any leading zero, so 0044 7400 123456 and +44 7400 123456 end up as different values, a national number with its trunk prefix stops being one, and the boundary between country code and number is gone.
A constraint, in the database. The application normalises; the column refuses what was not normalised: a national form, spaces, a leading zero after the plus, more than fifteen digits. Two applications writing to one table then cannot store the same number two ways.
A unique index, if one number means one account. On the E.164 column, it is also the index every lookup by number uses. If several rows may share a switchboard number with different extensions, index the number and the extension together instead.
The extension apart. It is not part of E.164, and RFC 3966 writes it as a parameter (tel:+12015550123;ext=42).

The table

PostgreSQL

CREATE TABLE contact (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  -- E.164: a plus, then 2 to 15 digits, the first not 0
  phone       varchar(16) NOT NULL CHECK (phone ~ '^\+[1-9][0-9]{1,14}$'),
  phone_ext   varchar(10) CHECK (phone_ext ~ '^[0-9]{1,10}$'),
  phone_region char(2),   -- ISO 3166-1 alpha-2, from parse: 'GB'
  phone_type  varchar(20), -- the line type, from parse: 'mobile'
  phone_input text        -- as the person typed it
);
CREATE UNIQUE INDEX contact_phone_key ON contact (phone);

SQLite

CREATE TABLE contact (
  id          INTEGER PRIMARY KEY,
  -- E.164: a plus, then 2 to 15 digits, the first not 0 (SQLite has no regular
  -- expressions without an extension: GLOB and length do the same check)
  phone       TEXT NOT NULL CHECK (
                length(phone) BETWEEN 3 AND 16
                AND phone GLOB '+[1-9]*'
                AND substr(phone, 2) NOT GLOB '*[^0-9]*'),
  phone_ext   TEXT CHECK (phone_ext GLOB '[0-9]*' AND phone_ext NOT GLOB '*[^0-9]*' AND length(phone_ext) <= 10),
  phone_region TEXT CHECK (length(phone_region) = 2),
  phone_type  TEXT,
  phone_input TEXT
);
CREATE UNIQUE INDEX contact_phone_key ON contact (phone);

MySQL 8.0.16 and later enforce CHECK constraints too; there the condition is written with REGEXP_LIKE.

What it accepts

Inserted one after the other into an empty table, in both databases; each row is accepted or refused the same way in both.

phone	phone_ext	What it is	Insert
+447400123456	—	E.164	accepted
+12015550123	42	E.164 with an extension apart	accepted
+447400123456	—	the same number again	refused
07400123456	—	national form, no country code	refused
+44 7400 123456	—	spaces	refused
+04400123456	—	a country code cannot start with 0	refused
+4474001234567890	—	sixteen digits	refused
+1	—	one digit	refused
+12015550199	12a	an extension with a letter	refused

Writing a number

Normalise in the application, where you know the person's country: the guides' parse reads what they typed in the plans you serve and returns the E.164 form, the region and the line type, or nothing when no plan accepts the number — then there is nothing to store, and the form says so. Pass the values as parameters.

INSERT INTO contact (phone, phone_ext, phone_region, phone_type, phone_input)
VALUES ($1, $2, $3, $4, $5);
-- $1 '+447400123456'  e164 from parse
-- $2 NULL             the extension, if the form has a field for it
-- $3 'GB'             region from parse
-- $4 'mobile'         type from parse
-- $5 '07400 123456'   what the person typed

The region settles numbers that share a country code (+1 is 25 plans, +44 is 4) and lets you filter by country without parsing again.
The line type is the one the plan gives the range (line types at signup); store it with the date if you act on it, since plans change.
The input as typed answers the support question "I entered my number correctly" and lets you parse again after a plan update.
Display is computed: the national form for people in the same country, the international form for everyone else.

The SQL above is run before it is published: in SQLite and in PostgreSQL 16, each row inserted and its outcome checked against the table.