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)
