Re: Storing derived and derivable data

David Cressey wrote:

>
> "Kenneth Downs" <knode.wants.this_at_see.sigblock> wrote in message
> news:ue2ti3-u0a.ln1_at_pluto.downsfam.net...
>

>> Another drawback is more of a balance question.  Materializing
>> derivations within the transaction lengthens the transaction, takes more
>> space, and
>> increases in some cases the chances of contention.  But then the reads
>> are
>> fast.  With a view, the transactions are kept shorter, at the price of an
>> ever-increasing read cost as the complexity increases.
>>

Thanks.

>
> It's going to take me a long time to go through the material you've
> pointed us to.
>
> In the meantime, I'd like to understand your main point a little better.
> Oracle has a tool called a "Snapshot"
> that was new to me on one of my projects. When the DBA described
> snapshots,
> my reaction was that it sounded like a materialized view. Her response
> was that that is exactly what it is.

I googled variations of "oracle snapshot" and seem to be getting features that look like backups, which i don't think is what you mean. Can you tell me more?

>
> In retrospect, I think my response was an oversimplification. Some
> transforms can be carried out in a snapshot that can't, to my knowledge,
> be
> implemented in a view. I've got two questions for you. First, is what
> I've
> just said true or not? Second, if true, is it relevant to the point you
> made in your earlier post?

If you can flesh out more about snapshots I'll try to answer.

>
>
>

>> Finally, SQL itself is a piss-poor language in which to store the
>> authoritative fundamental definition of derived data, so the view

>> itself can't be your method of definition.  You need a better language in
>> which to define the variations, and then you decide whether to use a view
>> as an implementation method.
>>

Let me clarify.

SQL is great as an interface to relational a DMBS. It has its detractors, but I'm not one of them. It is childishly simple to generate SQL (much easier than generating HTML or XML for instance) and so in practical terms I love it. It is Good Enough(tm).

My complaint is that a view definition, written in SQL, is a terrible format in which to store meta-data. Technically the view definition tells you everything you need to know about where the derived columns came from, but if your entire system (the other tiers besides the server) are based on the use of a data dictionary, you need that dictionary in very simple clean tables for easy querying. SQL DDL is a dead letter here, you need something else.

Therefore you want some superior syntax for defining the derived columns. In my case, that syntax allows human beings to type files that get converted into data and loaded to data dictionary tables. Then you generate the view definition, though in my case I generate trigger code instead.

>
>

>> >
>> > I like most of what you wrote in your normalization + automation
>> > column, with only minor quibbles about the normalization issue.  I,
>> > too, believe that we should treat our metadata, including business
>> > rules, as data.
>>
>> I wish I were more persuasive on this point.  It seems so self-evident to

>> that I often find myself at a loss to explain it.
>>

point well taken

-- 
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth_at_(Sec)ure(Dat)a(.com)