Currently designing a database to register animal species (Sharks and relatives)
1 table will hold species (name, attributes, and relative information)
I will have the taxonomy hierarchy for each specie, then I'm thinking in create a table to hold that, eg:
CREATE TABLE IF NOT EXISTS public.taxonomy
(
taxonomy_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
taxonomy_level_id smallint NOT NULL,
path ltree NOT NULL,
english_name varchar(50) COLLATE pg_catalog."default",
description text COLLATE pg_catalog."default",
CONSTRAINT taxonomy_pkey PRIMARY KEY (taxonomy_id),
)
My question is due the ltree and best use of it. In every sample I see people registering all nodes even they are not used, for example if they have A.B.C, they will insert as:
A
A.B
A.B.C
Is all that necessary or can we just define the ending required path? (1 row just with A.B.C and then latter we can also have A.B.A)
Let'me show my usage... The "full" path for a great white shark would be "Eukariota.Animalia.Metazoa.Chordata.Vertebrata.Gnathostomata.Fish.Chondrichthyes.Elasmobranchii.Selachimorpha.Lamniformes.Lamnidae.Carcharodon"
As shark and relatives start at "Chondrichthyes" I plan to drop all previous nodes which are static and fixed. So the root node would be Chondrichthyes and I can trim the path to: "Chondrichthyes.Elasmobranchii.Selachimorpha.Lamniformes.Lamnidae.Carcharodon"
Registering a row per every node is kinda painful when all I need is the finishing node for the specie. So, my question: Can I register only the required path or all nodes must register independently, even if they never be the target_id of any table? eg:
Chondrichthyes
Chondrichthyes.Elasmobranchii
Chondrichthyes.Elasmobranchii.Selachimorpha
Chondrichthyes.Elasmobranchii.Selachimorpha.Lamniformes
Chondrichthyes.Elasmobranchii.Selachimorpha.Lamniformes.Lamnidae
Chondrichthyes.Elasmobranchii.Selachimorpha.Lamniformes.Lamnidae.Carcharodon
Chondrichthyes.Elasmobranchii.Selachimorpha.Lamniformes.Lamnidae.Lamna
Chondrichthyes.Elasmobranchii.Selachimorpha.Lamniformes.Lamnidae.Isurus
VS
Chondrichthyes.Elasmobranchii.Selachimorpha.Lamniformes.Lamnidae.Carcharodon
Chondrichthyes.Elasmobranchii.Selachimorpha.Lamniformes.Lamnidae.Lamna
Chondrichthyes.Elasmobranchii.Selachimorpha.Lamniformes.Lamnidae.Isurus
parent_taxonomy_id
with thetaxonomy_id
of each row, and use a recursive CTE to build the tree when needed.