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

4915RE: [SQLQueriesNoCode] Better approach to Queries and Relationships design Question

Expand Messages
  • Arnie Rowland
    Jun 26, 2014

      I would only separate the data into two tables IF:


      1.       A set of the columns are retrieved significantly more often than the remaining columns

      2.       A set of the columns are often NULL (then it would be a one to less than one relationship)

      3.       There are few requirements where I need to join the two tables.


      Separating into two tables, one with FKeys and other lookup values to facilitate JOINs, filters, etc., is meaningless since all lookups and JOINs will be using indexes, not the tables.


      Especially for DW use, I would not separate the data. Often in DW designs, we will ‘flatten’ and denormalize multiple tables in order to enhance query responsiveness. (Normalization is for storage and data consistency, not query responsiveness.)


      If using SQL Server, and some columns are most frequently NULL, consider using SPARSE COLUMNS and COLUMN SETS.



      Arnie Rowland | Westwood Consulting, LLC | +1-503-246-6172

      Microsoft SQL Server MVP


           "You cannot do a kindness too soon because you never know how soon it will be too late."

            -Ralph Waldo Emerson




      From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com]
      Sent: Thursday, June 26, 2014 7:26 AM
      To: SQLQueriesNoCode@yahoogroups.com
      Subject: Re: [SQLQueriesNoCode] Better approach to Queries and Relationships design Question


      I'm not a DB authority, so maybe I'm missing the point here. What are you trying to accomplish by splitting up a single record?


      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.


    • Show all 7 messages in this topic