Loading ...
Sorry, an error occurred while loading the content.

4916Re: [SQLQueriesNoCode] Better approach to Queries and Relationships design Question

Expand Messages
  • Charles Carroll
    Jun 26, 2014
      Also I am thinking about performance in terms of querying the link table to get a key then using that key against the data.

      The Table I am dealing with has a few dozen fields and I would rather query the link table to  give me the Foreign key
      where CatfieldA=17 AND CatfieldB=987 AND CatfieldC=529 AND CatfieldD=NULL

      Then hit the data table that matches id=foreign key I got above
      Select fieldA, FieldB, FieldC, FieldD from blah where id=FKey I got above
      which has got to be faster than all the ANDs.

      This also allows a little more clarity to me that the LinkTable is easier to enforce Business rules in without getting distracted by the data. And allows the data to be related more than one way whereas the first approach one row of data can only be attached to one set of categories.

      The data to me is THE DATA, and all the ways it is attached to various other business groups, categories and facts are definitely a link table situation.

      But at least in the past I have found my nose was dead wrong, or there was another approach I was not thinking of. And that great minds had been working on this and developed some schools of thought and some litmus tests as to when it made sense, and when it did not.

      On Thu, Jun 26, 2014 at 10:32 AM, Tim Mitchell tdmitch@... [SQLQueriesNoCode] <SQLQueriesNoCode@yahoogroups.com> wrote:

      Depends on the granularity of the data.  Will you have more than one budget entry matching the other key fields?  If so, it makes a lot of sense (from a technical perspective, anyway) to split it up to avoid data duplication.  

      The two-table approach is certainly more normalized in the academic sense.  But personally, I'm not dogmatic about achieving any particular level of academic normalization.  A single table approach in this situation, while not fully normalized, might make more sense in terms of how the business uses the data, which is truly the end goal.

      On Thu, Jun 26, 2014 at 8:35 AM, Charles Carroll seniorprogrammerguru@... [SQLQueriesNoCode] <SQLQueriesNoCode@yahoogroups.com> wrote:

      Let say you have a simple database like this:


      ID   Quad     FKDept  Est  St   FKPer     Budget
      1     North     88      1973    MD    990      7,238,123
      2     South     77     1978    CA     123      9,876,902
      3     West     12      1984    MO    479    19,876,902

      etc. with some additional Data after Budget....

      My inclination would be to setup 2 tables


      ID  FKsample Quad     FKDept  Est  St   FKPer
      1    777          North     88      1973    MD    990
      2    145          South     77     1978    CA     123
      3      84          West     12      1984    MO    479


      ID   Budgeted  Field2    Field3  Field4
      777    7,238,123
      145    9,876,902
      84    19,876,902

      rather than store every thing/foreign key value that could be queried
      with the data.

      Am I "smelling the right direction for the solution?

      Or is the top table fine, and the 2 table approach bad?

      I did not do the example fully normalized with domain tables mainly to
      illustrate the concept more clearly. The final implementation of
      LinkSample would be mostly Foreign keys (Quadrant, etc.)

      I have encountered quite a few tables like the one above and I prefer
      the second approach, from querying, design, and SOC/Simplicity
      standpoint but maybe I am wrong. So I was curious about the design /
      data warehousing / BI considerations from the experts here.

      Tim Mitchell, MCTS
      SQL Server MVP
      Blog: http://www.TimMitchell.net
      Twitter: http://twitter.com/Tim_Mitchell

    • Show all 7 messages in this topic