1

is it possible to design a database with a classic tree structure (no nested-set), forcing to have just 2 levels?

the table I thought should be:

id title parent_id
1 row NULL
2 child 1
3 grand_child ERROR 2

The third row shouldn't be possible.
Can I get that without trigger or without any programming language?

2 Answers 2

3

Simplest solution is making the parent node into separate table with the same set of fields*. Then no nested references would be possible.

For example, you could have following entities (SQL-like pseudocode)

CREATE TABLE RootNode
(
    Id INT,
    ...other fields as needed...
);


CREATE TABLE ChildNode
(
    Id INT,
    Parent_FK INT REFERENCES RootNode (Id), 
    ...other fields as needed (same as in RootNode)...
);

*I actually suspect the parent table and child table might get different fields over time, but that depends on what you are actually trying to model.

2
  • Thanks for the response. Can you be more specific please? With an example?
    – Giacomo M
    Dec 7, 2023 at 15:07
  • What about the columns are exactly the same between parent and child?
    – Giacomo M
    Dec 7, 2023 at 15:54
1

Building on Yano_of_queenscastles just to provide a sample:

INSERT INTO RootNode (Id, OtherField1, OtherField2)
VALUES
    (1, 'Root 1 Data', 'More data for Root 1'),
    (2, 'Root 2 Data', 'More data for Root 2');

-- Sample data for ChildNode table
INSERT INTO ChildNode (Id, Parent_FK, ChildField1, ChildField2)
VALUES
    (101, 1, 'Child 1 Data', 'More data for Child 1'),
    (102, 1, 'Child 2 Data', 'More data for Child 2'),
    (103, 2, 'Child 3 Data', 'More data for Child 3');

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.