Re: sql views for denomalizing

Lauri wrote:
> dawn wrote:
> > 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).
> >
> >
> >>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.
> >
> >
> Let me see if I have understood this correctly:
>
> 1) first the "DBA" creates the tables (or whatever they are called in
> Pick), more or less as he/she would if using an SQL DBMS

Quite close, however, it is often a systems analyst who would execute a CREATE FILE (like CREATE TABLE) command, and often without including any attributes. This would be done during a prototyping phase, with attributes described and redescribed, possibly generating some prototype update screens until a system design is established. Unlike what is commonly considered good engineering of applications, I suspect it is very common in pick to work first on a prototype and then migrate that exact prototype into a final product, with the code and the data descriptions being complete at the same time.

[Aside on methodology, skip below if not interested. Using some of the metaphors for software engineering, where SQL-DBMS app development might look more like a team producing a film, PICK app development might look more like a team climbing a mountain (or perhaps producing an indie film on a tight budget). While either methodology could be used in either environment, I would guess a pick shop is more likely to employ a methodology leaning toward "agile" (even if never having heard of XP) and a SQL-DBMS more likely to have heard of the CMM.

The pick projects would more likely scale using what is now termed an SOA (service-oriented architecture) rather than by adding new roles (e.g. DBA, HCI, analyst, designer, coder, ...) That is, project partitioning is more vertical (separate services) than horizontal (separate roles).

This is all a generalization, of course, but on the whole I sense a different org culture in the different shops that isn't strictly related to the size of shop.]

> 2) then the "DBA" creates virtual attributes, which, in effect, are
> references to certain other tables (or whatever they are called in Pick)

These are typically done in a "just in time" fashion, as needed during the life of a system. Higher level reporting tools help make it easy to write these on the fly and not to require them for any data to which one can navigate, only for derived data.

Without such tools, the base system is one with an extensible vocabulary for each file, as well as for the system in general. One flavor of pick calls the top level the master dictionary (which includes names for access to all code, data, virtual data, commands, etc -- the whole system), while another calls it the vocabulary file. Each file defined within the voc file has its own vocabulary. To anyone querying the data, the "base table" and the virtual fields look identical. They simply ask a question about this entity. Multiple vocabulary terms can describe the same data in different ways. Multiple logical files in the master dictionary can describe a file differently.

> 3) after this the developer/user can use these virtual attributes as if
> they belonged to the table.

That is true of the query language. The CRUD API is only for the base table, which can be described in a variety of ways, but comes down to what goes into the key of the file (0th position), the 1st position, 2nd position, etc. The only thing fixed is this ordinal, not the way anything is described.

> These virtual attributes would contain
> lists,

yes, as can udf's in sql server. Additionally, the base table can include such lists.

> if the referenced table had several rows per "master" row, as
> seems to be the case in your example

yes, which also permits lists of foreign keys, so you are more likely in pick to think of the foreign key in the parent, often multivalued, pointing to the child tables. Just in in postgresql, if you list everything in the parent table, you are likely (if virtual attributes have been identified or your reporting tool does it) to get all attributes of this parent and its children.

When teaching a pick person how to work with SQL, one of the most difficult concepts is that with SQL you link (foreign key) from the child to the parent even when you are asking a question of the parent.

Files are typically designed with data redundancy in the area of foreign keys with "return links" from one file back to another that points to it. There are exceptions, but almost all referential integrity other than parent-child where both are in the same file (which is very common) is done by services written by the s/w developers rather than services provided by the dbms, and, yes, this does introduce errors not found in a SQL-DBMS application. There are tradeoffs.

> 4) these virtual attributes are first class citizens in the query
> language, and in the case of lists, can be queried with the likes
> of "every" any "any"

yes.

> 5) if the developer/user needed a new viewpoint he/she would ask the DBA
> to create new virtual attributes

I have not seen any shops, including large ones, where the developer would not do this themselves. I have seen plenty of shops where an end-user also does this themselves. The end-user can be given their own version of the description of a file (it is a logical view) and they can add to it (depends on the site, of course).

> It would be interesting if you could provide the complete DDL (or
> whatever it is called in Pick) for your mini-example together with the
> queries.

Since I don't have a pick environment handy, before I go through the trouble of loading one and making an example, there is a high level summary intro in a little "triology" that I prepared on business-card stock a few years ago. It can be read at http://tincatgroup.com/mv/trilogy.html . You can likely zip through the data and commands slides quickly. Then ask questions (or give opinions). Unlike what I write here, the trilogy has gone through a QA process.

And again, I'm not suggesting that the exact spec for this system specified 40 years ago is the future, but that the flexibility and productivity for the developer using this data model is very high, both in development and maintenance.

Cheers! --dawn

> regards,
> Lauri Pietarinen
Received on Sun Jul 31 2005 - 22:01:36 CEST