Re: sql views for denomalizing

dawn wrote:
> Marshall Spight wrote:
> > >
> > > list students with courseDept = "Math" and with every major <> "Math"
> >
> > Ha! I've been trying to get examples of the value of MV out of
> > you for ages, and at last you slip up and reveal one!
>
> Such serendipity. I'm sure I've provided these in the past when I
> started chatting on cdt. People likely just showed how it was possible
> with SQL (which I knew) and that was that.

None of the examples I've seen from you in the past have been this sophisticated. I'm happy to believe you've posted similarly rich queries elsewhere or at other times, but that doesn't help me if I haven't seen them. I have tried to get illustrative examples from you in the past and been frustrated. I'm interested in the issues that come up in querying nested data, and since you're often going on about the value of nested data, I would expect you'd have illustrative examples ready-made.

> > Let me make sure I understand this: students have many courses;
> > courses have a courseDept; students have many majors; majors
> > is a string. Yes? So if we represented this in SQL, we'd use
> > three tables, Students, Courses (with FK to Students, and with a
> > courseDept) and Majors (with FK to Students, and major.)
>
> Since I've done this with a live system, it was lots more relational
> tables and also more pick files than that,

Of course. We're just doing illustrative examples.

> > The
> > Pick version of this would have a multivalued attribute major
> > inside Students,
>
> Since a major is an attribute of a student's program, rather than a
> student directly (they could be both an undergrad with one set of
> majors and graduate with another, for example), major would still be
> modeled in a separate physical "relation" but a virtual field pointing
> to this list of majors would make it look the same to the user as if
> the field were in the student relation (file).

Ah! Interesting. Do virtual fields have to be set up ahead of time, or can they be made up on a per-query basis?

> > and also a multivaled attribute inside Student
> > that listed the courses that student was taking. Please correct
> > or confirm.
>
> again, this would be a virtual field pointing to where the information
> about courses (likely associated with the term, for example) would be
> found. The "current" part would pull off today's date to find which
> terms were current, all behind the scenes as part of the virtual field
> logic.

So, this virtual field stuff just sounds like views.

The more I learn about pick, the closer to relational it seems.

"Files" <-> Relations
records <-> tuples
Virtual fields <-> views

pick better than SQL:
nested relations. This seems big to me

pick worse than SQL:
no static typing (but SQL's type system is fairly weak anyway) only one key

Also, it seems pick is more closely tied to a single implementation model, but that just seems like an accident of history rather than a model issue.

I'm still not clear on how pick handles many-to-many.

Back to the example query, I want to make sure I'm reading it correctly. courseDept is supposed to be multivalue, right? So I expect the correct interpretation of 'with courseDept = "Math"' is actually 'with *any* courseDept = "Math"' where I use any to indicate "at least one" the same way you use "every" to mean all of them.

> > This seems like a really good example query to study.
>
> Any time I have brought up this simple case of multiple one to many's
> in any discussion (face to face), I get both the "that's just a star
> schema" responses ('cause my user is gonna write one of those, eh?) or
> the SQL code that performs the same query.

Sometimes I get a little frustrated talking with you, because so much of the conversation between you and me ends up being about you and some other person you had a similar conversation with in the past, and you seem to assume that I'm going to respond the same way as those other people did. I might; I might not. I'm certainly not going to get any of the "benefits of dialog" we we referencing earlier in the week if you don't actually *have* the dialog with me as well, though.

So I would like to request that you leave out the part about times you have had discussions with other people, or even other times you've had discussions with me. I try to avoid rhetorical questions (not that I succeed 100%, but I try) so when I ask a question, it's often an actual question that I really want to know the answer to. I try to work step-by-step, to make sure I understand each little piece and nothing is getting glossed over. If you answer the question you believe is behind the question, usually that's not helpful to me.

Marshall Received on Sun Jul 31 2005 - 19:02:10 CEST