Archit
Archit

Reputation: 182

Data schema to represent relationship of single column to multiple tables in PostgreSQL

I would like to design a database schema to have the following relationship between tables:

users and admins have id, email, creator, updater.

Relationship that needs to be addressed are:

How do I represent this in the PostgreSQL database?

Thanks in advance.

Upvotes: 0

Views: 49

Answers (1)

Belayer
Belayer

Reputation: 14932

A single table, users with a boolean value indicating whether the user is on is not an administrator. The creating user and updating user in the same table and as foreign keys to user_id.

create table users
     ( user_id    text
     , user_name  text
     , is_admin   boolean
     , created_by text
     , updated_by text
     , constraint users_pk primary key (user_id)
     , constraint created_fk foreign key (created_by)
                  references users(user_id)
     , constraint updated_fk foreign key (updated_by)
                  references users(user_id) 
     );     

Upvotes: 1

Related Questions