What's the best way to represent hierarchical data structure in a single database table?

[miar]

I’m working on a project where I need to store religious text data in a database. The structure goes like this: Testament → Book → Chapter → Verse. Each level connects to the next one down.

My first idea was to make separate tables for each level:

testaments(id int, testament_name varchar)
book_list(id int, book_title varchar)
chapter_data(id int, chapter_number varchar)
verse_info(id int, verse_text varchar)

But someone suggested using a single table with parent_id references instead. They said this creates a tree-like structure where each row points to its parent.

I’m confused about how this parent_id approach actually works. How do you navigate through the hierarchy when everything is in one table? Is this really better than having multiple tables? I want each verse to have a unique URL that connects to teaching materials later.

[SoaringEagle]

the parent_id approach is overkill here. religious texts have a fixed structure, so why overcomplicate it? just use foreign keys between separate tables - testament_id in your books table, book_id in chapters, and so on. you’ll get simpler queries and won’t need any recursive nonsense to find verses. single tables work great for dynamic hierarchies, but your structure’s basically set in stone.

[emmad]

I built something almost identical for a legal doc system and went with separate tables using composite keys. Your Testament → Book → Chapter → Verse setup is perfect for this. Skip the parent_id references and use compound primary keys instead - like (testament_id, book_id, chapter_id, verse_id). Queries run way faster and URL generation becomes dead simple. You get clean hierarchy without messy recursive queries or tree traversals. The performance boost is huge when pulling specific verses or whole chapters. For URLs, you can generate paths like /old-testament/genesis/1/3 straight from the keys. Parent_id works if your hierarchy changes constantly, but religious texts don’t change structure. Ever. Separate tables also let you add level-specific stuff later - testament intros, book authors, chapter summaries - without cramming everything into one table full of null columns.

[Finn_Mystery]

The single table with parent_id can work, but I’d go with a hybrid approach for your case. I built a similar documentation system and cramming everything into one table caused more problems than it solved. Keep your separate tables but add a path column to each that stores the full hierarchy like ‘/testament_1/book_5/chapter_3/verse_12’. This gives you clean URLs while keeping your data separated properly. You can throw in a materialized path or nested set model if you’re doing lots of tree traversals. Single table gets messy fast when hierarchy levels have different attributes. Verses have text content, chapters have summaries, books have authors - stuffing all that into one table with tons of null columns just feels wrong. For URLs, the path approach makes routing way cleaner than rebuilding the hierarchy from parent_id relationships every single time.