Parent Child Relations in a Database Table

Parent - Child relationships ( hierarchical relationships ) are very common & often crop often during database designs.

You can expect to find Parent -Child relationships while modeling employee-manager, office-outlets & other similar concepts. I recently encountered the need for a parent - child relationship in a need to model a product-sub product concept.

You can find a lot of literature that talks about this topic in detail. I just want to add my 2 cents..

The hierarchical relationships are best explained by examples. Here, I consider a popular fast food chain of outlets ( "Yummies" ) that has outlets across the city.
Al the outlets in a particular part of the city "report" to a single outlet - the "parent" outlet.

All these "parent" outlets in turn "report" to a single "parent" outlet - something like a "head office".

The "report"s could be anything - daily sales figures, inventory status, customer feedback, staff shortages, etc. However, at this point of time, the main focus is to model the relationship of a parent-child outlet chain.

I am using an Oracle XE Database & Oracle SQL Developer to model the tables, fashion out the queries, etc. You could do the same with any other Database & your favourite tools.

First, I'll model the database table. I have made a couple of assumptions to arrive at this model :-

Assumption #1 : Each child outlet has a single parent outlet.
Assumption #2 : Each outlet has a parent outlet, except the "root" outlet ( head office ) .

Here's the table structure that I have designed :-

CREATE TABLE OUTLET
(
OUTLET_ID NUMBER PRIMARY KEY,
OUTLET_NAME VARCHAR2(30) NOT NULL,
OUTLET_PARENT NUMBER
);

The OUTLET_ID of the "parent" outlet is stored in the "child" outlet's OUTLET_PARENT.

Here's the data I stuffed into the table :-

INSERT INTO OUTLET VALUES(1,'Yummies M G Rd',null);
INSERT INTO OUTLET VALUES(2,'Yummies BSK ',1);
INSERT INTO OUTLET VALUES(3,'Yummies BSK I Stage ',2);
INSERT INTO OUTLET VALUES(4,'Yummies BSK II Stage ',2);
INSERT INTO OUTLET VALUES(5,'Yummies BSK III Stage ',2);
INSERT INTO OUTLET VALUES(6,'Yummies Food World ',3);

COMMIT;

Now, to dish out the queries...

Which outlet is the "mother of all outlets" ? ( root outlet / head office )

The outlet that has no parent ( OUTLET_PARENT is null ) is the "mother of all outlets".

SELECT * FROM OUTLET WHERE OUTLET_PARENT IS NULL;

OUTLET_ID OUTLET_NAME OUTLET_PARENT
--------- ------------------ ---------------
1 Yummies M G Rd

1 rows selected

Which are the outlets under the root outlet ?

SELECT
a.outlet_id,
a.outlet_name,
a.outlet_parent,
b.outlet_parent
FROM outlet a,
outlet b
WHERE b.outlet_id = a.outlet_parent
AND b.outlet_parent IS NULL;

OUTLET_ID OUTLET_NAME OUTLET_PARENT OUTLET_PARENT_1
--------- ------------------ --------------- ---------------
2 Yummies BSK 1

I can now dish out various queries that involve parent-child relationships on the chain of outlets.

The model described above works best only if the two critical assumptions are satisfied. If not, you may need to explore alternate ways to achieve this relationship - e.g.: moving the relationship data ( OUTLET_ID, OUTLET_PARENT) to a separate table, etc.