A colleague asked me about nested sets as described in Managing Hierarchical Data in MySQL.
My initial reaction was that is an example of the explosion of parts problem in database theory. (Sorry, but I am unable to find any good online references. This problem is also known as bill of materials.)
In the Oracle RDBMS, there is the Hierarchical Queries which handles data of this structure.
Set Up in Oracle
For this exercise, I used Oracle Database XE (a free product).
Create the table.
The SQL I used in Oracle is as follows via the APEX interface of the XE database. Because Oracle SQL does not have the AUTO_INCREMENT feature, I let the APEX interface of the Oracle XE database create a trigger which creates a unique key every time a row is inserted. The functionality is the same but the Oracle solution involves more work.
CREATE TABLE "CATEGORY" ( "CATEGORY_ID" NUMBER NOT NULL ENABLE, "NAME" VARCHAR2(20) NOT NULL ENABLE, "PARENT" NUMBER, CONSTRAINT "CATEGORY_PK" PRIMARY KEY ("CATEGORY_ID") ENABLE, CONSTRAINT "CATEGORY_NAME_UK" UNIQUE ("NAME") ENABLE, CONSTRAINT "CATEGORY_PARENT_ID_FK" FOREIGN KEY ("PARENT") REFERENCES "CATEGORY" ("CATEGORY_ID") ENABLE ) / CREATE OR REPLACE TRIGGER "BI_CATEGORY" before insert on "CATEGORY" for each row begin select "CATEGORY_ID_SEQ".nextval into :NEW.CATEGORY_ID from dual; end; / ALTER TRIGGER "BI_CATEGORY" ENABLE /
Note: I have added two (2) extra constraints that were implied in the data model, but not enforced in the MySQL example:
CATEGORY_NAME_UKensures that the name of each category is unique.
CATEGORY_PARENT_ID_FKensures that the ID of the parent already exists in the table.