Re: Views for denomalizing

>
> That explains why, historically, tools would enforce 1NF -- because
they
> could do so easily.

Since the relational model is based on relations (by definition) it is reasonable to assume that the implementations would enforce 1NF (=relations), which they don't even do, because they allow duplicates.

> Not only is it easy to enforce, it makes it easier FOR
> THE DBMS VENDOR to implement the logic of the database itself if we
restrict
> the user in a variety of ways. Also, I'll grant that if we give more
rope
> to the user, they might be more likely to hang themselves, so we can
help
> force them to design before they implement by having some
restrictions.
>

Exactly. I agree 100%. And it is a good idea to start out carefully. If Codd had started talking about nested relations from day one, the focus of discussion and research would have been muddled. His proposals
were radical enough for that time and there was plenty for the implementors
to digest.

> I can fully understand tools taking this approach in the 70's & 80's,
but
> when you see how software tools are bending over backwards to give
> flexibility to users today, restricting domains on attributes in this
way is
> really not acceptable, at least to me.

In my opinion the relational model as such does not prohibit arbitrarily
complex domains. And I think most implementations do support them. However, since it has been mostly retrofitted, implememtations of complex domains are not as clean as they could be and they have arbitrary restrictions. They also threw in some totally unnecessary stuff like reference types and table types.

And, BTW, I still think it is likely that users will hang them selves if
they think too much in terms of complex domains. Complex domains means complexity! Whenever you hide your data inside the domains they are out of reach of the relational operators. The advantages of relational operators are the following:

• they are very powerful
• they are declarative
• they can be optimized

Which is really saying the same thing in different words, I guess...

> > What do you mean by "how does SQL handle queries against such
views"?
> > What would you want it to do? Could you give an example?
> > regards,
>
> For example, if a UDF named "Email" has a value that is the list of
e-mail
> addresses for a person and another one named Phone had a list of
phones,
> then a view could have tuples such as:
>
> (PersonID, LastName, FirstName, Phone, CompanyName, Email) would lend
itself
> to a SQL select * having output such as:
>
> 12345 Harley George (514) 555-6762 This
Company
> Name gharley_at_aol.com
> (514)
555-1123
> george_harley_at_thiscompanyname.com
>

>

george_at_harleyfamily.org
>
> 22222 Vander Kooi Mary (711) 555-8785 Another
Company
> mvkooi_at_yahoo.com
> (711)
555-1191
>
> etc.

What is it exactly you are trying to provide the end users with? Do they want an Excel sheet with 6 columns? Or a one with four columns, with Phone, Companyname and Email repeated in one cell, or what?

>
> Alternatively, it could do what view on three base tables with the
above
> values -- Person, Phone, Email -- would do if you joined them and
then did a
> select *. It could provide the user a cross-product of the three --

> obviously not user friendly, but better for pouring into Excel (I
didn't
> dare say "flat" ;-)

What is it you need a cross-product for?

>
> I did try this with a PostgreSQL child table a while back (so, not
using
> derived data) but I only had one list attribute in my tuple. I did a
SELECT
> * on it and did get the child table data along with the parent IIRC.
I'm
> pretty sure that SQL3 provides for queries on views with list
attributes,
> but haven't seen any implementation of this that I know to be
standard in
> order to see just how it handles such queries.

I'm sorry, I lost you here.

Maybe, what you are looking for is something like this:

Select FirstName,

       (select company,
               email
           from email_table
           where em_PersonID = PersonID) as Contact

With a result like

 FName Contact

+------------------------+
!Tom  ! +--------------+ !
!     ! !comp1 !a_at_b    ! !
!     ! +------+-------+ !
!     ! !comp2 +c_at_d    ! !
!     ! +--------------+ !
!------------------------!
!Dick ! +--------------+ !
!     ! !comp1 !e_at_f    ! !
!     ! +------+-------+ !
!     ! !comp3 +g_at_h    ! !
!     ! +--------------+ !
!------------------------!
!Harry! +--------------+ !
!     ! +--------------+ !
+------------------------+

>From this result, the report generators
could (presumably) infer information on
how to format the output in a sensible
and user friendly way, and perhaps tools could even automatically construct
master-detail GUIs? I'm all for it!

Best Regards,
Lauri Pietarinen Received on Fri Feb 04 2005 - 00:17:09 CET