handling not null for optional fields

Question

Lets say that the frontend has an input form for user registration. A user can signup by either full name or company name. What would be the best approach for handling null data type in this scenario? Should the data type be NOT NULL with a default value of something? It could be the case that not every user will use both the full name and company name together when registering.

CREATE TABLE customer(
    company_name VARCHAR(255),
    full_name VARCHAR(255)
)

Answer 1

null is usually the way to represent the absence of data; in the scenario you describe, it does not look like using a default value would help.

Then: columns are nullable by default. If you want to enforce a rule that at least one the two columns should be not null, you can use a check constraint. Adding a serial primary key to the table would also be a good idea.

create table customer(
    customer_id serial primary key,
    company_name varchar(255),
    full_name varchar(255),
    check (company_name is not null or full_name is not null)
);