What would be the pros and cons of hierarchical data vs. related data, in performance (and categorization)?

Question

This is related to this question. It got me thinking that, for example, this book. Instead of it being a child of both [Agriculture] and [Other], in the trees [Books > Catalogs > Agriculture] and [Business & Industrial > Agriculture & Forestry > Other], respectively, it could just stand on its own have those levels as tags, instead.

In a website like eBay, would the performance costs of listing all records with the tags [Agriculture AND Catalog] heavily outweigth those of listing all records in the [Books > Catalogs > Agriculture] category, provided the data is properly normalized?

I realize this probably is a stupid question (and no, I don't think I'm programming the next eBay), but I can't shake this curiosity and, worse; the temptation; so I'd rather sound stupid now and get a good grasp of my misunderstandings than do something stupid or pointless later on.

Answer 1

When the relational model first emerged, the dominant database paradigm was hierarchical; and then (as is still the case) hierarchical is more efficient because, in essence, you can start with partially prejoined queries.

But only based on one static hierarchical structure. The great difficulty emerges when you want to flex the view of the data to come from another root dimension. (It was a strongly debated question whether relational databases could ever be efficient enough for practical usage; soon resolved by diligent application of Moore's Law.)

Think about Customers who have Orders which comprise Products; a hierarchy. Inevitably you'll want to also be able to think about Products which are to be found in Orders from Customers; an entire other hierarchy. And Salespeople in Regions who generate Orders from Customers; or have Customers that place Orders.

Relational databases afford you the ability to reconstruct your hierarchies at will with roughly equal efficiency; a benefit only available in other database formats at the cost of embedding multiple orthogonal hierarchies; in which case the cost of altering, adding, or deleting data becomes astronomical.

Answer 2

The main purpose of the relational model is to prevent duplication. Hierarchical data, while easier to grok manually, suffers from duplication issues.

Use the model that fits your data best instead of doing premature optimization.

I tend to build presentational hierarchical trees out of relational data.