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

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

Expand Messages
  • Charles Carroll
    Jun 26, 2014
      Well actually bad design has a way of slowing things down as data grows, and never gets fixed. So I try to fix bad design where I see it - lack of normalization, incorrectly structured tables, etc. so that the next people inheriting the system don't get a mess.

      As for abstracting things away Views, ORMs, etc. may hide bad design but when people want to add fields and new tables to the underlying data, want to localize, or add more dimensions to the data, the bad design can create a maintenance nightmare. As for speed, data that grows will encounter bloated, or "too many indexes" issues when the data grows in size that may be much harder to fix after people all write code to manipulate and query the badly structured data. Because you have to fix the data and the code then.

      Abstractions help but many OR/Ms are not abstractions - In most cases (excepting high end ones like NHibernate) they are just 1:1 corresponding to tables and relationships that are there (for better or worse) and providing clean C# access to work with the data. But garbage in, garbage out.

      On Thu, Jun 26, 2014 at 11:20 AM, Peter Brunone peter.brunone@... [SQLQueriesNoCode] <SQLQueriesNoCode@yahoogroups.com> wrote:

      Isn't most of that abstracted out with modern programming techniques? You have everything from Views to ORM to data access layers, ad infinitum; most of the structural questions of database development seem to be less relevant unless you're talking about massive scaling (which is really the only time you'll need to worry about performance).

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

      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