The SQL code you provided creates several tables to implement a basic user, group, and permission system. Let's break down each table and its purpose:
-
public.user
table:- This table stores information about users.
- Columns:
id
: A serial primary key that uniquely identifies each user.name
: A varchar field to store the name of the user (up to 200 characters).created_at
: A timestamp indicating the creation date of the user, with a default value of the current timestamp when the record is created.updated_at
: A timestamp indicating the last update date of the user, with a default value of the current timestamp when the record is created or updated.is_active
: A boolean field that indicates whether the user is active (True
) or inactive (False
), with a default value ofTrue
.
-
public.group
table:- This table stores information about groups.
- Columns:
id
: A serial primary key that uniquely identifies each group.name
: A varchar field to store the name of the group (up to 200 characters).created_at
: A timestamp indicating the creation date of the group, with a default value of the current timestamp when the record is created.updated_at
: A timestamp indicating the last update date of the group, with a default value of the current timestamp when the record is created or updated.
-
public.permission
table:- This table stores information about permissions.
- Columns:
id
: A serial primary key that uniquely identifies each permission.name
: A varchar field to store the name of the permission (up to 200 characters).created_at
: A timestamp indicating the creation date of the permission, with a default value of the current timestamp when the record is created.updated_at
: A timestamp indicating the last update date of the permission, with a default value of the current timestamp when the record is created or updated.
-
public.usergroups
table:- This table represents the many-to-many relationship between users and groups, indicating which users belong to which groups.
- Columns:
user_id
: A foreign key referencing theid
column in thepublic.user
table, representing the user who is a member of the group.group_id
: A foreign key referencing theid
column in thepublic.group
table, representing the group to which the user belongs.created_at
: A timestamp indicating the creation date of the relationship record, with a default value of the current timestamp when the record is created.updated_at
: A timestamp indicating the last update date of the relationship record, with a default value of the current timestamp when the record is created or updated.- The primary key is a composite key formed by the
user_id
andgroup_id
columns, ensuring that a user can only belong to a group once.
-
public.userpermissions
table:- This table represents the many-to-many relationship between users and permissions, indicating which permissions are assigned to which users.
- Columns:
user_id
: A foreign key referencing theid
column in thepublic.user
table, representing the user to whom the permission is assigned.permission_id
: A foreign key referencing theid
column in thepublic.permission
table, representing the permission assigned to the user.created_at
: A timestamp indicating the creation date of the relationship record, with a default value of the current timestamp when the record is created.updated_at
: A timestamp indicating the last update date of the relationship record, with a default value of the current timestamp when the record is created or updated.- The primary key is a composite key formed by the
user_id
andpermission_id
columns, ensuring that a user can have a specific permission only once.
-
public.grouppermissions
table:- This table represents the many-to-many relationship between groups and permissions, indicating which permissions are assigned to which groups.
- Columns:
group_id
: A foreign key referencing theid
column in thepublic.group
table, representing the group to which the permission is assigned.permission_id
: A foreign key referencing theid
column in thepublic.permission
table, representing the permission assigned to the group.created_at
: A timestamp indicating the creation date of the relationship record, with a default value of the current timestamp when the record is created.updated_at
: A timestamp indicating the last update date of the relationship record, with a default value of the current timestamp when the record is created or updated.- The primary key is a composite key formed by the
group_id
andpermission_id
columns, ensuring that a group can have a specific permission only once.
With these tables, you can now create and manage users, groups, and permissions for your system effectively. The use of many-to-many relationship tables (public.usergroups
, public.userpermissions
, and public.grouppermissions
) allows you to assign multiple users to groups and multiple permissions to users and groups easily.
create table public.user(
id serial primary key not null,
name varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
is_active BOOLEAN NOT NULL default True
);
create table public.group(
id serial primary key not null,
name varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now()
);
create table public.permission(
id serial primary key not null,
name varchar(200),
created_at timestamp not null default now(),
updated_at timestamp not null default now()
);
create table public.usergroups(
user_id int references public.user(id),
group_id int references public.group(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(user_id, group_id)
);
create table public.userpermissions(
user_id int references public.user(id),
permission_id int references public.permission(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(user_id, permission_id)
);
create table public.grouppermissions(
group_id int references public.user(id),
permission_id int references public.permission(id),
created_at timestamp not null default now(),
updated_at timestamp not null default now(),
PRIMARY KEY(group_id, permission_id)
);