Message-ID: <bbd73b96fbce29e0c1941253590f39ced1e79d4d.camel@janc.be>
Subject: Re: Schema design: user account deletion vs. keeping family tree
 data
From: Jan Claeys <lists@janc.be>
To: pgsql-general@lists.postgresql.org
Date: Tue, 25 Nov 2025 22:08:40 +0100
In-Reply-To: <CAEzggP_UOGwe5BA9s3iLY3R8LEi4QztA=2ka+vkRp-Wy64vXdQ@mail.gmail.com>
References: 
	<CAEzggP_UOGwe5BA9s3iLY3R8LEi4QztA=2ka+vkRp-Wy64vXdQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.3-0ubuntu1 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/bbd73b96fbce29e0c1941253590f39ced1e79d4d.camel%40janc.be>
Precedence: bulk

On Mon, 2025-11-24 at 12:27 +0100, Christoph Pieper wrote:
> I=E2=80=99m designing a schema for a family=E2=80=91tree web app on Postg=
reSQL. 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).
> Because 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.

Be careful. Storing and especially sharing/publishing any personal data
of, or closely related to, a living person (including the relations of
such person) would likely be a problem without permission from that
person. You probably want to contact a lawyer who=E2=80=99s familiar with t=
he
GDPR & other privacy laws=E2=80=A6

Personally, I would always keep tree data from different users apart,
give them detailed per-record control over what data can be published
and/or shared, and mark any records of living people as hidden/private
by default. And I would delete all records a user created when they
delete their account, or at the very least all those belonging to
living people.

----

About the schema design:
 * both your options assume a person has exactly 1 father and 1 mother
   (probably better just call them "parents" nowadays), and has only 1
   pair of parents (what with people who were adopted, etc.?)
 * "first name" & "last name" are assumptions that only make sense in
   some countries (even when your users are only German, their
   ancestors might not all be), and of course a person might have
   different legal names over their life
 * birth dates in genealogy are often not precise, especially if you go
   further in time, and the Postgres date type can=E2=80=99t =C2=A0express =
things
   like "November 1810", "about 1534", "1913 or 1918" or "between 1610
   and 1615", so might need a custom date type (and you later probably
   also want to be able to store/link many other dates?)

Genealogy is messy, and you will have to be able to store all sorts of
data you didn=E2=80=99t expect at first thought (see also the website about
names Rob Sargent linked to).
You also seem to make assumptions about relations being 1:1 or 1:N when
in reality they are very often 1:N or N:N instead.


--=20
Jan Claeys (please don't CC me when replying to the list)