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.

I was seeking an online BTC investment plan when I got scammed for 95,000 Euros in Bitcoin. I was so down and didn't know what to do. I regretted ever taking this decision and kept asking myself so many questions. It took me almost 2 months to try and forget what had happened to me but it was impossible to do that. While at work, I read an article on Quora regarding a recovery expert and genius, Dante Gideon Recovery. I reached out to him. This Agent was fast and very reliable. I was relieved that even though not all my cash came back, at least he was able to recover 95,000 Euros in Bitcoin. It was such a huge relief. I learnt my lesson the hard way though investing and getting returns was all I ever wanted and I did not know this could land me in trouble. DanteGideon, I WILL ALWAYS BE GRATEFUL. His personal email: DanteGideon @ Consultant com} WHATSAPP +1 (603) 702 ( 4335 ) Great experience.