Re: Pizza Example

In message <4081d6bd$0$64453$e4fe514c_at_news.xs4all.nl>, mAsterdam <mAsterdam_at_vrijdag.org> writes
>Anthony W. Youngman wrote:
>> mAsterdam writes:
>>> ...What would a Pick-schema look like that
>>> would support Ice Cream orders?
>> Let's say I wanted to keep ORDER_ITEMS, rather than create a new
>>ICE_CREAMS FILE, I would probably have two FIELDs in ORDER_ITEMS, the
>>first called BASE and the second TOPPING.
>> I would need a lookup-file containing permitted bases, which might
>>contain the records "ice cream", "thin pizza", "crusty pizza", etc.
>>I'd also have a lookup file of permitted toppings, and the matching
>>bases, so I'd have records like "fetta; thin pizza, crusty pizza",
>>"chocolate; ice cream".
>> Okay, validation is being pushed into the app layer (I'd like
>>triggers to enforce it when the app tried to write to the database),
>>but if my toppings file is indexed on the "matching base", the app
>>would present the user with a list of bases for the ORDER_ITEMS. As
>>soon as they have selected one, I read the index to get a list of
>>permitted toppings which I can then present to the user for selection.
>
>As soon as the constraints get a little cumbersome to formulate, the
>validation is pushed out, the triggers will be implemented in some next
>release. Yeah right. After the damage is done. It happens to most
>databases, not just those implemented in Pick-a-likes. The expressional
>poverty of most DB languages causes this, IMHO.

Same with current relational dbs :-) You're relying on the analyst or programmer to do the right thing, so am I :-)
>
>Date requires the DB language ('D') to be computationally
>complete. While this would not prevent all this from happening
>it would take away excuses to not prevent it.
>
>>>> As a Pick database designer, I would have one FILE (our equivalent
>>>>of "table") per real-world object type.
>>>
>>> Even if there would be support for Icecreams and wine it wouldn't
>>> be even close to re-use of those data by other
>>> applications/processes/use-cases, let alone sharing data
>>> between them.
>>>
>>> From this I get the impression that the Pick view on the term
>>> "database" is a sort of deluxe filesystem for one application
>>> instead of a shared repository. Is that correct?
>> What do you mean? You mean you'd describe a hard disk as a deluxe
>>filesystem for a single application called "the operating system"?
>
>No. Why would I do that?
>
>> Actually, Pick did start as a sort of operating system, so it would
>>be more correct to view it as an environment where you can run
>>multiple applications on it that share their data. So as I see it, it
>>*must* be a "repository", or database.
>
>Ok.
>
>>> I am not suggesting this is a wrong view, it just a different
>>> type of beast than what I have in mind when I use the term database.
>>> A databases (as I use the term) by definition contains shared data.
>>> As a consequence the database (schema) should be designed to support
>>>mutliple applications.
>> Yup. Pick is.
>
>So the restrucuring you proposed for including Ice cream orders affects
>all other applications. Including the duplicated validations for say
>the application 'ingredient purchasing'.

Well, we've just completely changed the design parameters. I can't see a relational database surviving a similar change unscathed, either. Despite it being called an ORDER_ITEMS table or FILE, it was actually a "pizza order items" table, as far as I can make out. I've just turned it into a true ORDER_ITEMS file, for which I've needed to add validations, filters etc. The havoc such a change would inflict on a relational database would be similar, and it's all down to poor original analysis.

But I thought you asked me how *I* would do it, and that's the way I would.
>
>>>> As a Pick database designer, I would have one FILE (our equivalent
>>>>of "table") per real-world object type. The data in this file *is*
>>>>*normalised*. It's just that it's NFNF (non first normal form).
>>>
>>> Could you please elaborate on the normalisation as you use the term?
>>> A google on 'Pick Normalise data' doesn't help much.
>> I use it as I understand everyone else uses it - you analyse your
>>data to remove redundant repetition. It's just that, as far as I am
>>concerned, "normalised" does not mean "*first* normal".
>
>I am really trying to understand what you mean by normalization or
>'removing redundant repetiton'. From this I don't.
>What normalisation do you mean if not 1..5(or6)NF as described
>by the relational theorists? That is how I understand everyone
>else uses the word in a database context.
>I am not asking you to completely explain the procedure.
>A reference will do. I'll buy a book if necessary.

I thought I was using it the same way. I get the impression my view of "relational normalisation" is unfairly influenced by what I see of SQL views ...
>
>>>> So. Imagine you've defined a view, in your relational database,
>>>>that joins all tables representing an object. You then "list" (sorry
>>>>I don't know the relational term) one object in your view. In your
>>>>two-dimensional view, imagine that all duplicated values just "don't
>>>>exist". You now have the equivalent of a Pick RECORD (a bit like
>>>>your row). We don't duplicate a simple attribute because it doesn't
>>>>make sense to do so - why list it repeatedly when it only exists once per object?
>>>
>>> ISTM that this is a reporting issue. Is there more to it?
>> See my other comments. Dawn has said I'm confusing relational and
>>SQL (which is likely true). If you select a view that includes a
>>"many" join, then the view will likely contain redundant repeated data.
>
>So for a clean report you would like to have the
>duplication filtered out.
>I would to.
>Every dbms I have seen comes with
>a reporting facility capable of doing that.
>
>Am I missing the point you make?
>
I would too (have a clean report, that is). But I understood that relational meant that you weren't supposed to need (or be able) to distinguish between views and tables. Given that tables are closed and views are not, I seem to have understood wrong.

But taking that into account, it seems to me that a properly designed Pick RECORD is actually pretty much exactly what a true relational view should be :-)

Given that most practitioners equate "SQL" with "relational", it's becoming obvious why the academic theoreticians hate SQL with a vengeance :-)

Cheers,
Wol

-- 
Anthony W. Youngman - wol at thewolery dot demon dot co dot uk
HEX wondered how much he should tell the Wizards. He felt it would not be a
good idea to burden them with too much input. Hex always thought of his reports
as Lies-to-People.
The Science of Discworld : (c) Terry Pratchett 1999