MySQL e genealogia

[MarcoGrazia]

Salve ragazzi, dovrei creare un programma di genealogia, quindi un database che tenga conto degli ascendenti e dei discendenti di una famiglia, compresi i rami collaterali.
Ora il mio tallone d'Achille è proprio che io con gli alberi in informatica non ci ragiono.
Sto iniziando a creare il programma d'interfaccia e mi serve una base da cui partire col database.
Ho pensato ad una tabella "Persona" strutturata come di seguito:

CREATE TABLE `persona` (
  `id_persona` int(10) UNSIGNED NOT NULL,
  `id_account` int(10) UNSIGNED NOT NULL,
  `nome` varchar(256) NOT NULL,
  `cognome` varchar(256) NOT NULL,
  `sesso` tinyint(3) UNSIGNED NOT NULL COMMENT '0=sconosciuto, 1=maschio, 2=femmina',
  `data_nascita` date DEFAULT NULL,
  `data_morte` date DEFAULT NULL,
  `data_matrimonio` date DEFAULT NULL,
  `luogo_nascita` varchar(1024) DEFAULT NULL COMMENT 'Città, nazione',
  `luogo_morte` varchar(1024) DEFAULT NULL COMMENT 'Città, nazione',
  `luogo_matrimonio` varchar(1024) DEFAULT NULL COMMENT 'Città,nazione',
  `note` varchar(4096) DEFAULT NULL,
  `id_madre` int(10) UNSIGNED DEFAULT NULL,
  `id_padre` int(10) UNSIGNED DEFAULT NULL,
  `id_figlio` int(10) UNSIGNED DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COMMENT='Tabella contenente i dati della persona';

Dove molti dati sono dati per null, dato che possono essere mancanti, ma non è questo il punto.

• id_persona è l'identificativo di record di questa tabella ( indicizzata e autoincrementante )
• id_account proviene dalla scheda personale, cioè chi compila l'albero, per identificarne un prorpietario;
• gli altri dati sono identificata la persona a cui il record è riferito;
• id_padre, id_madre e id_figlio, sono riferiti ai genitori e ai figli; ovviamente a null perché potrei non avere i dati e non avere discendenza.

Dato che come dicevo di alberi non ne capisco molto, inserendo un dato, per esempio pippo de pippis, con figlio 1 ( riferimento al record del figlio ma non ancora inserito di fatto nel database ) come funge?
Ovvero, quando vado a inserire la tabella per il figlio, metto come riferimento al padre l'id_persona.
Insomma una procedura che non è possibile realizzare a mano, credo servano le foreign key.
Mi date una mano?

PS: Forse andava in database->MySQL

 

[criric]

Ciao, tempo fa ho fatto un lavoro per un sito dove ogni utente poteva creare altri utenti, si è creato cosi un albero genealogico.
Ho creato semplicemente una tabella con due campi ( idUtente e idParent ) dove chi creava l'utente era il parent.
Con delle funzioni ricorsive salivo e scendevo dall'albero. Per recuperare il capostipite mi sono fatto aiutare dal forum in questa discussione.
Quando veniva creato un utente avevo in sessione l'id parent e l'id del nuovo utente lo recuperavo con insert_id di mysqli, tutto in php.
Il tuo lavoro è un pò più complesso ma penso che ragionandoci un pò potresti usare la stessa logica.

 

[MarcoGrazia]

Credo che mi ristudierò gli alberi binari.

 

[MarcoGrazia]

Sto studiando sia chiaro
Allora, sto studiando gli alberi a insiemi annidati, forse la forma più nota di struttura ad albero, in pratica una tabella potremme avere la seguente struttura:

CREATE TABLE `Note` (
  `id_nota` int(10) UNSIGNED NOT NULL,
  `nsleft` int(10) UNSIGNED NOT NULL,
  `nsright` int(10) UNSIGNED NOT NULL,
  `id_account` int(10) UNSIGNED NOT NULL,
  `nota` varchar(1000) NOT NULL,
  FOREIGN KEY ('id_account') REFERENCES 'Accounts' ('id_account')
);

Cioè il sistema classico dei commenti di un sistema a thread come questo forum, dove id_account ( registrato nella tabella Accounts ) scrive una nota.
Poi un altro id_account scrive un commento, e così ancora.
Ci possono essere dei forks ai commenti, per questo ci sono i campi nsleft ed nsright, a secondo di dove va il fork, a sinistra o a destra.
Ogni record rappresenta un nodo.
Diventa facile anche percorrere i nodi di un ramo, ad esempio con la seguente query:

SELECT c2.* FROM Note AS c1 JOIN Note AS c2 ON c2.nsleft BETWEEN c1.nsleft AND c1.nsright WHERE c1.id_nota = 4;

Dove 4 è un riferimento alla nota 4 ovviamente; la SELECT mi ritorna tutti i nodi compresi tra la 4 e l'ultimo discendente.
Anzi con poche query dovrebbe essere facile, dato un nodo, salire o scendere per leggere genitori e figli.
Inoltre, leggendo varie pubblicazioni, cartacee, comprendo pure che eliminando un nodo arbitario, non c'è poi bisogno di ricompattare il database, perché non c'è gerarchia tra i nodi, ma solo riferimenti ai nodi stessi, se uno manca viene saltato. Mi fido ma ancora non ho realizzato prove, vedremo come funziona più avanti...
Invece pare che i problemi, ma relativi alla complessità delle query, sussistano solo quando si vuole inserire un nodo in mezzo ad altri nodi, perché qui bisogna ricalcolare tutti i valori nsleft ed nsright.
Studio ancora.