public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jan Claeys <[email protected]>
To: [email protected]
Subject: Re: Schema design: user account deletion vs. keeping family tree data
Date: Tue, 25 Nov 2025 22:08:40 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAEzggP_UOGwe5BA9s3iLY3R8LEi4QztA=2ka+vkRp-Wy64vXdQ@mail.gmail.com>
References: <CAEzggP_UOGwe5BA9s3iLY3R8LEi4QztA=2ka+vkRp-Wy64vXdQ@mail.gmail.com>
On Mon, 2025-11-24 at 12:27 +0100, Christoph Pieper wrote:
> I’m designing a schema for a family‑tree web app on PostgreSQL. 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‑grandparents).
> 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’s familiar with the
GDPR & other privacy laws…
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’t express 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’t 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.
--
Jan Claeys (please don't CC me when replying to the list)
view thread (7+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Schema design: user account deletion vs. keeping family tree data
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox