4918Re: [SQLQueriesNoCode] Better approach to Queries and Relationships design Question
- Jun 26, 2014Abstractions 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.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.
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: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.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