Simple website sql database

Question

I'm getting into backend with postgresql and I would like to know how much of my example would fit for a real website database, just for storing and then displaying it on website.

create table clients (
    id BIGSERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    age INT CHECK (age >= 18) NOT NULL,
    email VARCHAR(70) UNIQUE NOT NULL,
    password VARCHAR(100) NOT NULL,
    card VARCHAR(70) DEFAULT ('undefined') UNIQUE NOT NULL,
    joined TIMESTAMP NOT NULL,
    country VARCHAR(50) DEFAULT ('undefined') NOT NULL,
    language VARCHAR(50) DEFAULT ('undefined') NOT NULL
);

insert into clients (first_name, last_name, age, email, password, joined, language) values ('Rustie', 'Matchell', 18, '[email protected]', 'OSauq0z2suY', '2021-04-18 05:26:40', 'Kurdish');
insert into clients (first_name, last_name, age, email, password, card, joined, country, language) values ('Ulric', 'Hoggins', 20, '[email protected]', 'M4hnFLJ5XeP', '30243414381012', '2021-02-20 08:07:13', 'China', 'Mongolian');
insert into clients (first_name, last_name, age, email, password, card, joined, country, language) values ('Sephira', 'Bayly', 26, '[email protected]', 'INL57w6gXe', '5100138794351466', '2021-04-25 06:17:26', 'North Korea', 'Gujarati');
insert into clients (first_name, last_name, age, email, password, card, joined, country, language) values ('Hermine', 'Fassman', 29, '[email protected]', '1UX4TApQMEuV', '3552094428434244', '2021-06-18 06:48:54', 'Indonesia', 'Albanian');

RESULT:

 id | first_name | last_name | age |            email             |   password   |        card        |       joined        |        country        |  language
----+------------+-----------+-----+------------------------------+--------------+--------------------+---------------------+-----------------------+------------
  1 | Rustie     | Matchell  |  18 | [email protected]    | OSauq0z2suY  | undefined          | 2021-04-18 05:26:40 | undefined             | Kurdish
  2 | Ulric      | Hoggins   |  20 | [email protected]             | M4hnFLJ5XeP  | 30243414381012     | 2021-02-20 08:07:13 | China                 | Mongolian
  3 | Sephira    | Bayly     |  26 | [email protected]           | INL57w6gXe   | 5100138794351466   | 2021-04-25 06:17:26 | North Korea           | Gujarati
  4 | Hermine    | Fassman   |  29 | [email protected]         | 1UX4TApQMEuV | 3552094428434244   | 2021-06-18 06:48:54 | Indonesia             | Albanian

Answer 1

serial / bigserial are deprecated and you should instead use a generated always as identity clause on an integer-type column.

It looks like you're storing a plaintext password. No, please, no. You need to read about password hashing and salting in general, and then the PostgreSQL crypto support routines.

The string undefined is not a good way to represent a field being missing; this is an in-band value when you need an out-of-band value. The more reasonable thing to do for your card, country and language columns is to allow them to be nullable and to give them a default of null.

Consider combining your inserts into one insert statement with multiple rows in your values expression.