Postgres DB design

Question

I need to develop a database to handle muting people in an online community. The DB needs to keep track of the currently muted people. When somebody gets unmuted, that entry gets moved to an archive table. People can only be muted by specific staff members. This is the code I came up with:

CREATE table sh_members ( id bigint primary key, name varchar(32) not null, discriminator smallint not null, display_name varchar(32) not null, joined_at timestamp not null ); create table sh_staff_members ( id bigint, constraint link foreign key (id) references sh_members (id), primary key (id), first_promotion timestamp not null, last_promotion timestamp check (sh_staff_members.last_promotion > first_promotion) ); create type mute_type as enum ('command', 'reaction'); create table current_mutes ( member_id bigint not null, constraint member foreign key (member_id) references sh_members (id) on delete set null, muter_id bigint not null, constraint muter foreign key (muter_id) references sh_staff_members (id) on delete set null, primary key (member_id, muter_id), mute_type mute_type not null, mute_reason text not null, message_link text, duration interval, muted_at timestamp not null ); create table mutes_archive ( id serial primary key, member_id bigint not null, constraint member foreign key (member_id) references sh_members (id) on delete set null, muter_id bigint not null, constraint muter foreign key (muter_id) references sh_staff_members (id) on delete set null, unmuter_id bigint not null, constraint unmuter foreign key (unmuter_id) references sh_staff_members (id) on delete set null, mute_type mute_type not null, mute_reason text not null, message_link text, muted_at timestamp not null, unmuted_at timestamp not null, duration interval generated always as (unmuted_at - muted_at) stored, unmute_reason text not null ); 

What probably stands out most is the foreign key in sh_staff_members which also functions as a PK. While inheritance seems like a better tool to represent the is-a relationship between sh_staff_member and sh_member, using it is out of my skill level. Hence I decided to go for a 1:0..1 relationship between those tables. When it comes to current_mutes, there can only be one entry per member_id. When that member gets unmuted, the entry gets moved into muted_archive. Note, it's intentionally possible for a staff member to mute themselves.

This is the schema I'm trying to go for:

So, I have a few questions:

1. Is this database properly normalized?
2. How do I write Postgres checks to ensure that sh_staff_members.id ∈ sh_members.id?
3. Other suggestions and improvements are certainly welcome!

Answer 1

What probably stands out most is the foreign key in sh_staff_members which also functions as a PK. While inheritance seems like a better tool to represent the is-a relationship between sh_staff_member and sh_member, using it is out of my skill level. Hence I decided to go for a 1:0..1 relationship between those tables.

IMHO, this is the most natural way to express is-a in an RDBMS and I see no issue with it.

Otherwise:

In some places you (properly, I think) include the primary key constraint as an unnamed, inline column constraint. In others, you have it as a table constraint, i.e. primary key (id). I prefer the first style, but you should be consistent.

Sometimes you name your constraints and sometimes you don't. I typically don't, but whatever you do you should be consistent.

joined_at and muted_at could receive a convenience default current_timestamp.

serial is a non-standard extension and you should instead prefer the generated always syntax, which is standard and you're already using elsewhere.