# Is a Self-defining Table of Domains Normalized?

I don't really know the official name of what I've made but it can't be the first time this has been made, so apologizes up front for not knowing every term.

My question, is a table as outlined below normalized?

``````pk    fk    description
1     1     domains
2     1     people
3     1     cars
4     2     tom
5     2     smith
6     3     vmw
7     2     betty
8     3     ford
``````

The main 'rule' this table confuses me about is `A table should store only data for a single type of entity`. It stores domain information, but it stores multiple domains' information.

A example use for this table, fetch the domain of people.

``````select * from domains where fk = 2
``````

which returns

``````pk    fk    description
4     2     tom
5     2     smith
7     2     betty
``````

The fk is the 'domain' the item is in, which is the pk of the domain. This is what to me is self-defining because this table defines which domains it contains.

This is a called a "self-referencing table", or "single-table hierarchy". You normally query them with a recursive CTE because you want the children, not just on a single conditional.

Note, normally you place the fk of the root as `null`

``````CREATE TABLE foo(pk,fk,description)
AS VALUES
( 1 , null , 'domains' ),
( 2 , 1 , 'people' ),
( 3 , 1 , 'cars' ),
( 4 , 2 , 'tom' ),
( 5 , 2 , 'smith' ),
( 6 , 3 , 'vmw' ),
( 7 , 2 , 'betty' ),
( 8 , 3 , 'ford' );

WITH RECURSIVE t(pk, fk, description, level) AS (
SELECT pk, fk, ARRAY[description], 0
FROM foo
WHERE fk IS NULL
UNION ALL
SELECT foo.pk, foo.fk, t.description || foo.description, t.level+1
FROM t
JOIN foo ON (foo.fk = t.pk)
)
SELECT *
FROM t;

pk | fk |      description       | level
----+----+------------------------+-------
1 |    | {domains}              |     0
2 |  1 | {domains,people}       |     1
3 |  1 | {domains,cars}         |     1
4 |  2 | {domains,people,tom}   |     2
5 |  2 | {domains,people,smith} |     2
6 |  3 | {domains,cars,vmw}     |     2
7 |  2 | {domains,people,betty} |     2
8 |  3 | {domains,cars,ford}    |     2
(8 rows)
``````

Now you can find all `{domains,cars}` like this.

``````WITH RECURSIVE t(pk, fk, description, level) AS (
SELECT pk, fk, ARRAY[description], 0
FROM foo
WHERE fk IS NULL
UNION ALL
SELECT foo.pk, foo.fk, t.description || foo.description, t.level+1
FROM t
JOIN foo ON (foo.fk = t.pk)
)
SELECT *
FROM t
WHERE description @> ARRAY['domains','cars'];
pk | fk |     description     | level
----+----+---------------------+-------
3 |  1 | {domains,cars}      |     1
6 |  3 | {domains,cars,vmw}  |     2
8 |  3 | {domains,cars,ford} |     2
(3 rows)
``````

But the real advantage comes with just a bit more complexity.

``````INSERT INTO foo(pk,fk,description) VALUES
( 9, 8, 'Mustang' );
``````

Now rerunning that same query you ge t

`````` pk | fk |         description         | level
----+----+-----------------------------+-------
3 |  1 | {domains,cars}              |     1
6 |  3 | {domains,cars,vmw}          |     2
8 |  3 | {domains,cars,ford}         |     2
9 |  8 | {domains,cars,ford,Mustang} |     3
(4 rows)
``````

`Mustang` is under `ford`, and it's all in the `domain` of `cars`.

You may also want just the last item of the array in the result set, or a trail of pkids. All of those are minor variations of this same pattern utilizing Recursive CTEs.

All examples tested with PostgreSQL