4916Re: [SQLQueriesNoCode] Better approach to Queries and Relationships design Question
- Jun 26, 2014But 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.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.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 abovewhich 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.
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
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
- << Previous post in topic Next post in topic >>