Sfrgttk

Imagine I have the models A, B, C e D, where A have many Bs, B have many Cs and C have manyDs:

Without redundance

A

|  id | ... |

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

| ... | ... |



B

|  id | a_id | ... |

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

| ... |  ... | ... |



C

|  id | b_id | ... |

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

| ... |  ... | ... |



D

|  id | c_id | ... |

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

| ... |  ... | ... |

Would be recommended to have more columns in C e D with the reference to A and B?

With redundance

C

|  id | a_id | b_id | ... |

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

| ... |  ... |  ... | ... |



D

|  id | a_id | b_id | c_id | ... |

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

| ... |  ... |  ... |  ... | ... |

It's a redundance, but I usually do this to make simpler queries, I can make less JOINs. I think it probably have better performance too.

Is it recommended? (At least when the columns are immutables) Is there a better solution for this?

I can find no fault with adding columns to tables that meet the requirements of your system.

It may in fact make some queries much faster to have the "redundant" columns present. Take this example, which is pretty contrived, where you want to get a list of parents, and the names of the schools their children attend. The first example, where parents are not linked directly to schools:

SELECT p.ParentName

    , s.SchoolName

FROM dbo.Schools s

    INNER JOIN dbo.Children c on s.School_ID = c.School_ID

    INNER JOIN dbo.Parents p ON c.Parent_ID = p.Parent_ID;

Versus the query where Parents are linked to schools, even though the link is "redundant" since each parents child already has the link:

SELECT p.ParentName

    , s.SchoolName

FROM dbo.Schools s

    INNER JOIN dbo.Parents ON s.School_ID = p.School_ID

Clearly, you'd want to do the second variant because (a) it's less complicated to understand the intent, and (b) SQL Server has to do less work, which is always a good thing.

https://stackoverflow.com/questions/40553231/storing-redundant-foreign-keys-to-avoid-joins

For @max-vernon 's answer, it's true that the first sql is more than the second, but not too much. I prefer the first one. So:

    // you have D, and want to get A, then:

    let d = D();

    let c = select C.* from C where C.id=d.c_id;

    let b = select B.* from B where B.id=c.b_id;

    let a = select A.* from A where A.id=b.a_id;

    // or

    let d = D();

    let a = select A.* from A

                right join B on A.id=B.a_id

                right join C on B.id=C.b_id

                right join D on C.id=D.c_id

                where D.id=d.id;



    // you have A, and want to get Ds, then:

    let a = A();

    let Bs = select B.* from B where B.a_id=a.id;

    let Cs = select C.* from C where C.b_id in Bs.map(b => b.id);

    let Ds = select D.* from D where D.c_id in Cs.map(c => c.id);

    // or

    let a = A();

    let Ds = select D.* from D

                left join C on C.id=D.c_id

                left join B on B.id=C.b_id

                left join A on A.id=B.a_id

                where A.id=a.id;