Re: Views for denomalizing

David Cressey wrote:
> "Dawn M. Wolthuis" <dwolt_at_tincat-group.comREMOVE> wrote in message
> news:cts9lf$9h3$1_at_news.netins.net...
> > The top of this posting is intended to be devoid of opinions, so
please
> > correct if I have any misconceptions prior to the question.
> >
> > 1) SQL-DBMS's (at least those that conform to SQL92) provide no
> constraints
> > on the user creating new tables to restrict base tables from being
> > denormalized EXCEPT in the case of the first normal form.
>
>
> On May 7, 2004, mAsterdam (a really good contributor to this
newsgroup)
> provided a correction to your definition of 1NF. In particular, mA
> mentioned that part of 1NF included the no duplicates allowed rule.
At that
> time, your responses seem to indicate that you accepted and
internalized
> mA's comment.
>
> Now you are starting essentially the same thread you did last year,
but
> you've once again dropped the no duplicates allowed part of 1NF. Did
you
> forget what you picked up almost a year ago?

I thought I remembered something about a def for 1NF and I did connect it to mAsterdamm so I looked for a cdt glossary but didn't see the definition in the one I found. Drat!!! Yes, I DID forget and when YOU get to be a 48-year-old woman, I'll remember to cut you some slack too ;-) I am constantly appalled at what I cannot seem to remember.

One issue I have is that the aspect of 1NF that I am interested in doesn't seem to have a consistent name. What do you call the modeling process of removing attributes from one relation when the values of that attribute are lists (or relations or tables or arrays or other type of non-scalar collections of data) and modeling them with another relation? I think I need a name for that because otherwise I revert to calling it "1NF".

The other issue is that there are a ton of terms in the relational world and equally many in non-relational circles. I don't "live" in the relational theory space day in and day out like many of you do, so I return to my home and the terms revert to the other world.

And my final excuse is that it was groundhog's day -- that just can't be a coincidence, can it? So, my apologies, I will look for the information from mAsterdam from a year ago.

> Are you going to start the
> same conversation again in 2006?

At this point I would not put it past me, but I will do my best to put the def of 1NF that you found in some safe place on my computer and attempt to create a pointer to it in this didn't-used-to-be-so-forgetful brain.

> Are we going to have to cover this ground
> again?

What ground? ;-)

> Incidentally, it's possible to "get around" the repeating groups
part of
> the 1NF rule by "faking out" the metadata.
> Essentially, if you crosstabulate a relational table, you get data
that is
> not in 1NF. By suitably disguising the column names, you can make
it look
> like there are no repeating groups, even though there are.

I'm not sure that I understand what you mean by this. Can you give an example -- I'll try not to ask again next year, really!

> The reason I mention this is that I've seen more than one SQL based
database
> in the field that was trying to operate on
> crosstabulated data. As one might expect, they got neither the
flexibility
> and power that one expects from the RDM, nor the performance they
needed in
> the real world.

So you believe there are better tools than a SQL-DBMS for managing data intended for certain types of retrieval, is that right? What data model would you recommend for storing crosstab data? Does it have a name (cube, star schema, or ?) What about for storing granular data that needs to be represented/viewed in a crosstab, but with "drill-down" capabilities -- is there a different data model for that or the same as for the crosstab data?

Is there a way to identify data that are modeled with a relational approach that will never be needed in a crosstab view or do we need two separate models with redundant data across the two for all data (such as an operational database plus a data warehouse or data mart)?

> This is not an argument against crosstabulating data. It's just an
argument
> against storing crosstabulated data in an SQL table, and then
pretending
> that it's relational.

I'm with you on that -- but I'm not sure we could get concensus here on it -- can we?

> This may be somewhat tangential to the point you are trying to make.
But
> it's important enough so that it needs to be mentioned. Hopefully
not
> again, next year.

I'm both upset with my brain and embarassed that I really did state the same thing again, with only a tiny recollection that I should look in the glossary (and that was wrong -- I needed to look at other postings from mAsterdam). Dang! But thanks for calling me on it -- I'll bring this e-mail to the doctor and see what can be done. smiles. --dawn Received on Thu Feb 03 2005 - 22:37:02 CET