Storing read optimized Tree in a Database
Introduction Have you ever tried putting a Tree (a Parent-Child hierarchical structure) into a Database Table? What seems to be a trivial thing from an implementation point of view can sometimes turn into a real performance nightmare when a large number of requests continuously try to read different portions/branches of the Tree. Example Use Case Let's say we have an e-commerce solution with a multi-level Product Catalog. One of the most basic requirements would be that if a Customer browses one of the Categories, he should be presented with Products from that Category and from its Subcategories (regardless of their depth in the hierarchy). If I browse for Phones, I should be presented with all the Phones of all the brands. I can then decide to browse a Sub-category of Phones - for example iPhones or Samsungs. The same use case will apply to any Taxonomy based systems that need to fetch Items related to entire Branches of Trees, not only from single Nodes. It is ba...