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.

• irtexas19: Reinderien can you explain why should I use null values instead of "undefined"? I've made some research, and I've found that using null values isn't advisable. What would happen if I don't use null values in this particular case?
• Reinderien: The use of nullable columns is not advisable if you know that your data for that column has an always-defined guarantee. If it's necessary to represent an "undefined" value, then using nullable columns is the way to go.