Question 227426 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres Database Parent/Children relationship

This is a senario:

I have got a object let’s say a cat. For example when I insert the cat 4 details to database, I can add children to cat 4, lets say cat 1, 2 and 3.

In web page, when I click on cat 4 link, I need to open new page with details of cat 4 plus cat 1, 2 and 3.

I am not wondering about the frontend implementation, I onloy want to know how can this be designed in database. I do not think this is related to inhertance since we are dealing with same type of object which is a cat.

I can make it simple by creating a array field in cat table that contains children’s ids, but I am not sure if this is the best solution. Or It could be better way to implement this in Postgres database.

Tags:

2

Answers


  1. 0

    Don’t use arrays for this, they can’t maintain data integrity.

    Something like this could work:

    CREATE TABLE animals(
    id_animal INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
    , id_animal_type INT -- foreign key to animal types, like cat, dog, etc
    , id_mom INT REFERENCES animals(id_animal)
    , id_dad INT REFERENCES animals(id_animal)
    , gender CHAR(1) CHECK ( gender IN('m','f') )
    , name TEXT
    -- etc
);
    Login or Signup to reply.
  2. 0

    you can try this

    -- Cat Table
CREATE TABLE Cat (
cat_id SERIAL PRIMARY KEY,
name VARCHAR(255),
-- Other cat details...
);

-- CatHierarchy Table
CREATE TABLE CatHierarchy (
parent_cat_id INT REFERENCES Cat(cat_id),
child_cat_id INT REFERENCES Cat(cat_id),
PRIMARY KEY (parent_cat_id, child_cat_id)
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search