sql - PostgreSQL - How to identify three roles of users in database? -

i'm building database schema needs 3 kind of user roles: normal user, admin , super admin. first approach make 2 tables, 1 normal user , 1 both admin , super admin inherits users table. table definitions this:

create table users (   id bigserial not null primary key,   email text not null unique,   password text not null,   name text not null,   gender char not null,   created_at timestamp not null default current_timestamp );  create table admins (   phone bigint not null,   doc bigint not null,   super_admin boolean not null,   id bigint not null primary key references users(id) ); 

but i'm not sure how select user or admin when needed. i'm thinking if better leave 1 "users" table , define new table "roles" users matched, in order select them column "role" specifies if user normal, admin or super admin.

first, shouldn't storing telephone numbers integers. should using strings. there exist valid phone numbers in world can start 0, integers cannot handle.

this can complicated. first, note have 2 additional data fields admins , supers. if include data in users table, access users have information. normally, fine. however, imagine situations issue.

postgres directly supports table inheritance (see here), don't need explicit foreign key relationship. inheritance nice way handle derived user types. gives alternative tables admins , supers can reference in from clause (and these available users).

there 2 alternatives. 1 put additional columns (phone , doc) in users table, along indicator of type. single field ("normal", "admin", "super") or 3 flags ("isnormal", "isadmin", , "issuper"). alternative have separate userroles junction table.

each of these methods has merits. because have started down path of multiple tables , have enhanced data admins , supers, recommend inheritance specific case. note syntax might be:

create table admins (   phone bigint not null,   doc bigint not null ) inherits (users);  create table supers (   phone bigint not null,   doc bigint not null ) inherits (users);