A self referencing table is a table which has a field which is defined as foreign key to the same table's primary key.
CREATE TABLE DBREGISTRY
(
ID INTEGER,
PARENT INTEGER, // PARENT references ID of table DBREGISTRY
DESCRIPTION CHAR(30),
INTVALUE INTEGER,
CONSTRAINT PK_DBREGISTRY PRIMARY KEY (ID),
CONSTRAINT FK_DBREGISTRY_PARENT FOREIGN KEY (PARENT) REFERENCES DBREGISTRY (ID)
)
This kind of table does allow to save data rows in a hierarchical way.
ID PARENET DESCRIPTION
1 1 "ROOT"
2 1 "FIRST LEVEL ITEM A"
3 2 "ITEM 1 OF FIRST LEVEL ITEM A"
4 1 "FIRST LEVEL ITEM B"
5 4 "ITEM 1 OF FIRST LEVEL ITEM B"
For imaging the hirarchy:
"ROOT"
"FIRST LEVEL ITEM A"
"ITEM 1 OF FIRST LEVEL ITEM A"
"FIRST LEVEL ITEM B"
"ITEM 1 OF FIRST LEVEL ITEM B"
There are several things (some of them are pitfalls) to pay attention in praxis:
a.) For foreign key parent it makes sense to define also "ON DELETE CASCADE"
b.) Importing such a table is usually not an easy thing, this because items and there ID can be arise completely random.
E.g. : Windows registry seems to be made with a similar structure.