4
\$\begingroup\$

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: enter image description here

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!
\$\endgroup\$
2
  • \$\begingroup\$ I'm surprised that PG allows this. Constraint names have no implicit prefixes when you choose one on your own, and you have multiple constraints named member. \$\endgroup\$
    – Reinderien
    Oct 1, 2022 at 14:07
  • \$\begingroup\$ Apparently the namespaces are distinct, but I don't know how I feel about this naming scheme. \$\endgroup\$
    – Reinderien
    Oct 1, 2022 at 14:10

1 Answer 1

4
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.