Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

my favorite normalization case study Expand / Collapse
Posted Tuesday, January 17, 2012 3:10 AM


Group: General Forum Members
Last Login: Wednesday, January 16, 2013 4:23 PM
Points: 415, Visits: 2,333
L' Eomot Inversé (1/16/2012)
patrickmcginnis59 (1/16/2012)
PaulB-TheOneAndOnly (1/16/2012)
Please do not take it the wrong way but, out of curiosity, why is the subject of this thread "my favorite normalization case study" if you never intended to normalize the data and you don't even care about the basics of data modeling?

I'm not judging, you can do it anyway you want

It wasn't actually meant to be all that detailed a thread, but I'm interested that you felt it important to mention that "you don't even care about the basics of data modeling" when I clearly posted both the normalized and denormalized views, and actually shipped the normalized view to our reporting system. I took advantage of the denormalized view to help me ship. Its not like I didn't consider the normalized view, like I said, I materialized it for reporting.

Why did you feel the need to say "you don't even care about the basics of data modeling"? I thought it was an interesting case, but now your reaction is the interesting item in this thread

So tell me about why you believe I don't care or didn't care when I produced this system?

Don't get too upset Patrick.

Its ok. Paul is welcome to his opinion.

Paul is (I think - I hope he'll correct me if I'm wrong) taking things from the point of view of someone who uses an RDBMS which sorts out all those storage issues for him, whereas you are taking the point of view of someone who has to design the optimal storage and then present a relational view on top of it.
Obviously you will differ sometimes (probably almost always, given the two points of view), and this is one of those times.

What I think is useful is to consider alternatives, and I think its useful to attempt to understand why folks do consider alternatives. As I have mentioned, there seems to be an orthodoxy in the RDBMS world which prevents this, while I think its less than useful, I'm ok that it exists as I know how strongly many folks feel about it. Its worth my consideration that I may have asked for such a dismissal from Paul by even posting this anecdote on an SQL forum.

It doesn't help, of course, that none of our currently available RDBMSs can be told what sort of queries they will get with what frequency and attempt to optimise the storage mechanism to fit, but have instead a fixed set (usually two members to that set) of storage organisations within which they permit the user to attempt to optimise by choosing indices and clustering factors. It's not clear to me that the relational model prefers one of those two views over the other, but for most people it seems to be absolutely clear that the relational model implies Paul's view, not yours (needless to say, I disagree with them; that doesn't mean that I think your storage structure is relational, of course, it just means that I think it's fairly clear that it can be used to support a relational view of the data; I don't for a moment think that Microsoft's view storage view of pages, files, and filegroups is the slightest bit relational either, but it certainly can supprt something close to a relational view (maybe exactly a relational view) of the data. But I do think it's unreasonable to ask for an explanation of storage engine primitives in terms of relational primitives - that's confusing the cart and the horse.

I believe that there is a continuum of cases with varying degrees of mappability to relational solutions so to speak. While there are clearly cases where relational mappings do no good, and there are cases where they do much good, I'm of the belief that there are some grey areas. To me, I saw clear benefit in "pre-joining" elements, and it looked much like Oracle's vararrays, and what I found interesting was the benefit I got in using this approach.
Post #1237066
Posted Tuesday, January 17, 2012 7:24 AM

Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
patrickmcginnis59 (1/16/2012)
Why did you feel the need to say "you don't even care about the basics of data modeling"? I thought it was an interesting case, but now your reaction is the interesting item in this thread

So tell me about why you believe I don't care or didn't care when I produced this system?

Let me start by appologizing for my harsh language - didn't mean to be offensive but I recognize it didn't come out that well.

Having said that, the process of data modeling is pretty straight forward - after getting your business requirements and collecting the data you need to work with what we usually do is:

1- Create a ER model where we identify the Entities that make up our system as well as the Relationships between such Entities.

2- After having a validated ER model we usually decide either to go with a Relational Physical Model or with a Dimensional Physical Model. First option which I prefer for OLTP systems requires to normalize data usually not below the 3NF; second option which I prefer for DSS a.k.a. Data Warehouse systems requires to develop a star-schema model.

In this particular - and certainly interesting case - I didnt' see a single trace of these expected steps therefore I assumed you decided to be adventurous and don't care about traditional data modeling techniques.

It also surprised me that the stated objective of the design was to favor data entry over reporting when each piece of data will enter the system just once but will be used for reporting forever after - this fact of life usually means that the underlying design favors "reporting" over "data entry" by creating a data structure that favors the process of retrieving the data.

Having said all of that, at the end of the day it don't matter if the cat is white or black, all that matters is the cat getting the mice.

Sorry again, no personal offense was meant.

Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Post #1237211
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse