Same details to be stored for different roles in database but with different required fields(not null fields)

Question

Consider I have two users with two different roles -- user1: role1, user2: role2

Many columns are same for both role types, for example consider data of birth. But for role1 saving data of birth is required(not null) whereas for role 2 it is optional(nullable). So is it better to maintain two different tables for two roles or keep same table and maintain logic in backend.

Akina · Answer 1 · 2022-02-28T12:36:18.943

But for role1 saving data of birth is required(not null) whereas for role 2 it is optional(nullable).

Use table-level CHECK constraint:

CREATE TABLE user_role (
    user_name VARCHAR(255) NOT NULL,           -- common user info
    role_id INT NOT NULL,                      -- reference to role table 
    birth DATE,                                -- day of birth
    CHECK (role_id <> 1 OR birth IS NOT NULL)  -- if role=1 then check that birth date is set
);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a1ae67dd4dc31d446b65f41e54f59431

Same details to be stored for different roles in database but with different required fields(not null fields)

1 Answers1