Is it recommended to have redundant foreign key columns?





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}







2















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?










share|improve this question































    2















    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?










    share|improve this question



























      2












      2








      2


      1






      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?










      share|improve this question
















      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?







      foreign-key






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Jun 24 '14 at 18:18

























      asked Jun 24 '14 at 18:08







      user41575





























          2 Answers
          2






          active

          oldest

          votes


















          2














          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.






          share|improve this answer































            0














            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;





            share|improve this answer








            New contributor




            xialvjun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
            Check out our Code of Conduct.





















              Your Answer








              StackExchange.ready(function() {
              var channelOptions = {
              tags: "".split(" "),
              id: "182"
              };
              initTagRenderer("".split(" "), "".split(" "), channelOptions);

              StackExchange.using("externalEditor", function() {
              // Have to fire editor after snippets, if snippets enabled
              if (StackExchange.settings.snippets.snippetsEnabled) {
              StackExchange.using("snippets", function() {
              createEditor();
              });
              }
              else {
              createEditor();
              }
              });

              function createEditor() {
              StackExchange.prepareEditor({
              heartbeatType: 'answer',
              autoActivateHeartbeat: false,
              convertImagesToLinks: false,
              noModals: true,
              showLowRepImageUploadWarning: true,
              reputationToPostImages: null,
              bindNavPrevention: true,
              postfix: "",
              imageUploader: {
              brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
              contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
              allowUrls: true
              },
              onDemand: true,
              discardSelector: ".discard-answer"
              ,immediatelyShowMarkdownHelp:true
              });


              }
              });














              draft saved

              draft discarded


















              StackExchange.ready(
              function () {
              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f68951%2fis-it-recommended-to-have-redundant-foreign-key-columns%23new-answer', 'question_page');
              }
              );

              Post as a guest















              Required, but never shown
























              2 Answers
              2






              active

              oldest

              votes








              2 Answers
              2






              active

              oldest

              votes









              active

              oldest

              votes






              active

              oldest

              votes









              2














              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.






              share|improve this answer




























                2














                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.






                share|improve this answer


























                  2












                  2








                  2







                  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.






                  share|improve this answer













                  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.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Apr 18 '17 at 21:01









                  Max VernonMax Vernon

                  52.4k13115232




                  52.4k13115232

























                      0














                      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;





                      share|improve this answer








                      New contributor




                      xialvjun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                      Check out our Code of Conduct.

























                        0














                        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;





                        share|improve this answer








                        New contributor




                        xialvjun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                        Check out our Code of Conduct.























                          0












                          0








                          0







                          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;





                          share|improve this answer








                          New contributor




                          xialvjun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                          Check out our Code of Conduct.










                          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;






                          share|improve this answer








                          New contributor




                          xialvjun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                          Check out our Code of Conduct.









                          share|improve this answer



                          share|improve this answer






                          New contributor




                          xialvjun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                          Check out our Code of Conduct.









                          answered 12 mins ago









                          xialvjunxialvjun

                          101




                          101




                          New contributor




                          xialvjun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                          Check out our Code of Conduct.





                          New contributor





                          xialvjun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                          Check out our Code of Conduct.






                          xialvjun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                          Check out our Code of Conduct.






























                              draft saved

                              draft discarded




















































                              Thanks for contributing an answer to Database Administrators Stack Exchange!


                              • Please be sure to answer the question. Provide details and share your research!

                              But avoid



                              • Asking for help, clarification, or responding to other answers.

                              • Making statements based on opinion; back them up with references or personal experience.


                              To learn more, see our tips on writing great answers.




                              draft saved


                              draft discarded














                              StackExchange.ready(
                              function () {
                              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f68951%2fis-it-recommended-to-have-redundant-foreign-key-columns%23new-answer', 'question_page');
                              }
                              );

                              Post as a guest















                              Required, but never shown





















































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown

































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown







                              Popular posts from this blog

                              Liste der Baudenkmale in Friedland (Mecklenburg)

                              Single-Malt-Whisky

                              Czorneboh