MIME-Version: 1.0
From: Christoph Pieper <christoph@fecra.de>
Date: Mon, 24 Nov 2025 12:27:56 +0100
Message-ID: <CAEzggP_UOGwe5BA9s3iLY3R8LEi4QztA=2ka+vkRp-Wy64vXdQ@mail.gmail.com>
Subject: Schema design: user account deletion vs. keeping family tree data
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000009887a2064455761f"
Archived-At: <https://www.postgresql.org/message-id/CAEzggP_UOGwe5BA9s3iLY3R8LEi4QztA%3D2ka%2BvkRp-Wy64vXdQ%40mail.gmail.com>
Precedence: bulk

--0000000000009887a2064455761f
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi,

I=E2=80=99m designing a schema for a family=E2=80=91tree web app on Postgre=
SQL. Users
register accounts and can create one or more family trees. Each tree
consists of persons (the user themself, relatives, ancestors). Many persons
in a tree will never have an account (e.g. great=E2=80=91grandparents). Bec=
ause of
GDPR, when a user deletes their account we must remove/anonymise their user
profile, but we want to keep the family tree data intact so that other
users can still reference those ancestors.

We expect hundreds of thousands to millions of persons and deep ancestry
queries (N generations, inbreeding/relationship calculations).
I=E2=80=99m hesitating between two schema designs:

*Option A =E2=80=93 Separate family_tree_node table*

create table app_user (
  id          uuid primary key,
  email       text unique not null,
  created_at  timestamptz not null default now()
);

create table person (
  id                 uuid primary key,
  created_by_user_id uuid references app_user(id) on delete set null,
  first_name         text,
  last_name          text,
  birth_date         date
  -- more non-account-specific attributes may be added her in future!
);

create table family_tree (
  id            uuid primary key,
  owner_user_id uuid not null references app_user(id) on delete cascade,
  created_at    timestamptz not null default now()
);

create table family_tree_node (
  id              uuid primary key,
  family_tree_id  uuid not null references family_tree(id) on delete
cascade,
  person_id       uuid references person(id) on delete set null,
  father_node_id  uuid references family_tree_node(id),
  mother_node_id  uuid references family_tree_node(id)
);

create index on family_tree_node (family_tree_id);
create index on family_tree_node (person_id);
create index on family_tree_node (father_node_id);
create index on family_tree_node (mother_node_id);

Here family_tree_node is the structural graph for a specific tree. A node
may point to a person, but can also exist without one (minimal data only).
If a user/account is deleted, we only drop/anonymise data in app_user (and
optionally created_by_user_id), while person and family_tree_node remain.

*Option B =E2=80=93 Use person directly as the graph node (soft delete)*

create table app_user (
  id          uuid primary key,
  email       text unique not null,
  created_at  timestamptz not null default now()
);

create table person (
  id                 uuid primary key,
  created_by_user_id uuid references app_user(id) on delete set null,
  first_name         text,
  last_name          text,
  birth_date         date,
  father_id          uuid references person(id),
  mother_id          uuid references person(id),
  deleted_at         timestamptz     -- soft delete flag
);

create index on person (father_id);
create index on person (mother_id);
create index on person (deleted_at);

In this model, the pedigree graph is just a person(father_id, mother_id).
When a user deletes their account we never hard=E2=80=91delete persons; ins=
tead we
set deleted_at and/or anonymise some fields. All queries must filter on
deleted_at is null to hide soft=E2=80=91deleted persons.

Question:
From a PostgreSQL point of view (database best practices, data integrity,
performance and long=E2=80=91term maintainability at millions of rows), whi=
ch
approach would you prefer, or is there a better pattern for this kind of
=E2=80=9Caccount can be deleted, but genealogy should remain=E2=80=9D use c=
ase?

Regards and many thanks!
Christoph


--=20
[image: fecra company logo]

*Christoph Pieper*

christoph@fecra.de

fecra GmbH, Strelitzer Str. 63 10115 Berlin, Deutschland

www.fecra.de  | HRB 268518 B

--0000000000009887a2064455761f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi,<br><br>I=E2=80=99m designing a schema for a famil=
y=E2=80=91tree web app on PostgreSQL. Users register accounts and can creat=
e one or more family trees. Each tree consists of persons (the user themsel=
f, relatives, ancestors). Many persons in a tree will never have an account=
 (e.g. great=E2=80=91grandparents). Because of GDPR, when a user deletes th=
eir account we must remove/anonymise their user profile, but we want to kee=
p the family tree data intact so that other users can still reference those=
 ancestors.<br><br>We expect hundreds of thousands to millions of persons a=
nd deep ancestry queries (N generations, inbreeding/relationship calculatio=
ns).</div><div>I=E2=80=99m hesitating between two schema designs:</div><div=
><br></div><div><b>Option A =E2=80=93 Separate family_tree_node table</b></=
div><div><br><font face=3D"monospace">create table app_user (<br>=C2=A0 id =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0uuid primary key,<br>=C2=A0 email =C2=A0 =
=C2=A0 =C2=A0 text unique not null,<br>=C2=A0 created_at =C2=A0timestamptz =
not null default now()<br>);<br><br>create table person (<br>=C2=A0 id =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 uuid primary key,<br>=
=C2=A0 created_by_user_id uuid references app_user(id) on delete set null,<=
br>=C2=A0 first_name =C2=A0 =C2=A0 =C2=A0 =C2=A0 text,<br>=C2=A0 last_name =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0text,<br>=C2=A0 birth_date =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 date<br>=C2=A0 -- more non-account-specific attributes may be=
 added her in future!<br>);<br><br>create table family_tree (<br>=C2=A0 id =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0uuid primary key,<br>=C2=A0 owner_=
user_id uuid not null references app_user(id) on delete cascade,<br>=C2=A0 =
created_at =C2=A0 =C2=A0timestamptz not null default now()<br>);<br><br>cre=
ate table family_tree_node (<br>=C2=A0 id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0uuid primary key,<br>=C2=A0 family_tree_id =C2=A0uuid not =
null references family_tree(id) on delete cascade,<br>=C2=A0 person_id =C2=
=A0 =C2=A0 =C2=A0 uuid references person(id) on delete set null,<br>=C2=A0 =
father_node_id =C2=A0uuid references family_tree_node(id),<br>=C2=A0 mother=
_node_id =C2=A0uuid references family_tree_node(id)<br>);<br><br>create ind=
ex on family_tree_node (family_tree_id);<br>create index on family_tree_nod=
e (person_id);<br>create index on family_tree_node (father_node_id);<br>cre=
ate index on family_tree_node (mother_node_id);</font><br><br>Here family_t=
ree_node is the structural graph for a specific tree. A node may point to a=
 person, but can also exist without one (minimal data only). If a user/acco=
unt is deleted, we only drop/anonymise data in app_user (and optionally cre=
ated_by_user_id), while person and family_tree_node remain.</div><div><br><=
/div><div><b>Option B =E2=80=93 Use person directly as the graph node (soft=
 delete)</b></div><div><br><font face=3D"monospace">create table app_user (=
<br>=C2=A0 id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0uuid primary key,<br>=C2=A0=
 email =C2=A0 =C2=A0 =C2=A0 text unique not null,<br>=C2=A0 created_at =C2=
=A0timestamptz not null default now()<br>);<br><br>create table person (<br=
>=C2=A0 id =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 uuid pri=
mary key,<br>=C2=A0 created_by_user_id uuid references app_user(id) on dele=
te set null,<br>=C2=A0 first_name =C2=A0 =C2=A0 =C2=A0 =C2=A0 text,<br>=C2=
=A0 last_name =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0text,<br>=C2=A0 birth_date =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 date,<br>=C2=A0 father_id =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0uuid references person(id),<br>=C2=A0 mother_id =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0uuid references person(id),<br>=C2=A0 deleted_at =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 timestamptz =C2=A0 =C2=A0 -- soft delete flag<br>);<b=
r><br>create index on person (father_id);<br>create index on person (mother=
_id);<br>create index on person (deleted_at);</font><br><br>In this model, =
the pedigree graph is just a person(father_id, mother_id). When a user dele=
tes their account we never hard=E2=80=91delete persons; instead we set dele=
ted_at and/or anonymise some fields. All queries must filter on deleted_at =
is null to hide soft=E2=80=91deleted persons.<br><br>Question:</div><div>Fr=
om a PostgreSQL point of view (database best practices, data integrity, per=
formance and long=E2=80=91term maintainability at millions of rows), which =
approach would you prefer, or is there a better pattern for this kind of =
=E2=80=9Caccount can be deleted, but genealogy should remain=E2=80=9D use c=
ase?</div><div><br></div><div>Regards and many thanks!</div><div>Christoph<=
/div><div><br></div><div><br></div><div><br></div><span class=3D"gmail_sign=
ature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature" data=
-smartmail=3D"gmail_signature"><div dir=3D"ltr"><table style=3D"font-family=
:Arial,sans-serif;font-size:14px;color:rgb(51,51,51)"><tbody><tr><td style=
=3D"vertical-align:top"><img src=3D"https://www.fecra.de/fecra_logo.png" al=
t=3D"fecra company logo" width=3D"60" height=3D"60"></td><td style=3D"paddi=
ng-left:15px"><p style=3D"margin:0px;padding-bottom:0.25rem"><strong>Christ=
oph Pieper</strong></p><p style=3D"margin:0px;padding-bottom:0.5rem"><a hre=
f=3D"mailto:christoph@fecra.de" target=3D"_blank"><font color=3D"#666666">c=
hristoph@fecra.de</font></a></p><p style=3D"margin:0px;color:rgb(90,90,90)"=
>fecra GmbH, Strelitzer Str. 63 10115 Berlin, Deutschland</p><p style=3D"ma=
rgin:0px"><a href=3D"https://www.fecra.de/" style=3D"color:rgb(90,90,90)" t=
arget=3D"_blank">www.fecra.de</a>=C2=A0 |=C2=A0<span style=3D"color:rgb(90,=
90,90)">HRB 268518 B</span></p></td></tr></tbody></table></div></div></div>

--0000000000009887a2064455761f--