0

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
2
  • FWIW, you might find it simpler to manage and just as effective to only store the parent_taxonomy_id with the taxonomy_id of each row, and use a recursive CTE to build the tree when needed.
    – J.D.
    Aug 7, 2023 at 12:37
  • 1
    Actually I think that makes more on my case, thanks for suggesting that! Aug 8, 2023 at 0:40

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.