Re: How to do "categories" in SQL?

On 02.10.2019 19:16, groovee_at_cyberdude.com wrote:

> Ebay has, for example Electronics->Computers->Dell->XPS , with reviews for XPS models, > maybe multiple ones. How does one model this kind of thing, that is to say
CATEGORIES,> in a SQL database?

Hierarchies or trees are historically modeled by storing the parent node for each node in the tree. The root node gets NULL as parent. The table would look like that:

CREATE TABLE categories (
  category_id int unsigned,
  parent_id int unsigned,
  category_name varchar(50),
  ...
  primary key (category_id)
)

This is lean, straight forward and fairly easy to use. Each category can have an arbitrary number of children, but only a single parent. If you add a proper FOREIGN KEY constraint, MySQL can even check the integrity of the structure (i.e. forbidding you to create orphans).

However there is no easy way in MySQL (nor MariaDB) to traverse such a tree in a single SQL statement, because both DBMS miss the CONNECT BY feature.

There is another, more complicated way to store such a structure in a SQL table that is knows as "Nested Set Model". RTFM for details:

https://en.wikipedia.org/wiki/Nested_set_model

> And, how would one do the *multiple reviews for each XPS computer" bit?

Well, you would simply allow multiple rows in the `reviews` table to refer to the same category_id. Received on Wed Oct 02 2019 - 23:50:16 CEST