Table with FK(s) to other columns within the table

Question

Table with FK(s) to other columns within the table

BOR15K

SSCertifiable

Points: 5894
More actions
April 23, 2024 at 8:14 am

#4414414
Hello All,
I have recently joined a project, where main, core tables with millions of records have FKs to the columns within the table itself like in the example below:
```
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table (
table_id INT NOT NULL,
parent_id INT NOT NULL,
top_parent_id INT NOT NULL);

ALTER TABLE test_table
ADD CONSTRAINT test_table_PK PRIMARY KEY (table_id);

ALTER TABLE test_table
ADD CONSTRAINT test_table_R01 FOREIGN KEY (parent_id)
REFERENCES test_table (table_id);

ALTER TABLE test_table
ADD CONSTRAINT test_table_R02 FOREIGN KEY (top_parent_id)
REFERENCES test_table (table_id);
```
I never saw this approach before and would like to know if it is reasonable, if there is any performance issues one would expect, please? Any pros and cons will be truly appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Obviously a hierarchy of some sort. What sort of data do the rows in the table represent?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

BOR15K SSCertifiable Points: 5894 More actions · Answer 2

List of various Ids: document ids, booking id etc. Those in turn have their respective tables.

ratbak Ten Centuries Points: 1358 More actions · Answer 3

Take a look at Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets & Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations from Jeff Moden.

Or Joe Celko's Trees and Hierarchies in SQL for Smarties.

BOR15K SSCertifiable Points: 5894 More actions · Answer 4

ratbak wrote:

Take a look at Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets & Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations from Jeff Moden.
Or Joe Celko's Trees and Hierarchies in SQL for Smarties.

Completely forgot about those "Steroids". Thank you for reminding.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

ratbak wrote:

Take a look at Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets & Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations from Jeff Moden.
Or Joe Celko's Trees and Hierarchies in SQL for Smarties.

Thanks for the referral, Ratbak.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MMartin1 One Orange Chip Points: 27795 More actions · Answer 6

ALTER TABLE test_table
ADD CONSTRAINT test_table_R01 FOREIGN KEY (parent_id)
REFERENCES test_table (table_id);

Seems to me that the first row can only reference itself, so would have to watch the order in which you do your inserts.

----------------------------------------------------