Why the fuck do I have to follow DB lectures when I fucking know all the stuff already? Fuck you university and your policies about attendance!

How do you de-normalize a parent/child hierarchy into a ancestor/descendent/distance structure?

@JonnyCodewalker sorry, that tends to happen to me.

Say you have this table:

parentId childId
1 2
1 3
2 4
2 5
3 6

you can see that 1 is the ancestor of everyone. But how do you see that in SQL?

The normal way is to de-normalize the data structure into this:

ancestorId descendentId distance
1 2 1
1 3 1
1 4 2
1 5 2
1 6 2
2 4 1
2 5 1
3 6 1

And to do that, you need a recursive query, using common table expressions.

It's quite a common task, for anyone that designs the data structure like the first example, and wants to know the ancestry of a record.

@AndSoWeCode Sorry for the late reply. Anyway, we "studied" hierarchical queries last test, but I forgot everything because it's not something I use everyday.

The problem is that we are doing data normalisation for the 3rd time in this course, and they explain only until 3NF. Same thing for the different types of joins. Being it a University course, I'd like to study more complex topics.

@stefano well you should basically forget the normal forms. You should only know what the term feels like, because nowhere will you ever encounter it if it's a performance-optimized database.

Storage is cheap, IO is expensive. That's why data redundancy is not a by-product, but a freakin' holy grail that's everywhere.

Can we speed up the load by creating a whole new giant table with the same data but a slightly different structure that will be queried on specific scenarios? Then why the f**k didn't we do it yesterday? Let's have 2 tables, which are exactly the same, but slightly different, for performance reasons.

That's the general idea.