Re: Object-oriented thinking in SQL context?

David BL wrote:

>On Jun 24, 5:12 am, rp_at_raampje.(none) (Reinier Post) wrote:
>> David BL wrote:
>> >I am suggesting you can have a "database" type where a given value of
>> >that type is a set of named relation values, where the relations
>> >values are of distinct types. More specifically I'm thinking that the
>> >database type fixes the names and types of the relations.
>>
>> In a book on database design I've used, your idea is called 'composition'.
>> Recursion is disallowed there.

[...]
>> [...] can we only have finite nonrecursive instances such as
>> S3 =D= {A: {{B:3, C: {A: {{B:1, C:{}}}}}}}
>> ?
>
>Yes, allow that

Clear.

[...]

>[...] Note that a "database value" is really just a tuple with RVAs.
>So it should be sufficient to be able to deal with RVAs and tuple
>valued attributes.

Not quite. RVAs are relation-valued, and you propose database-valued attributes. Tuple-valued attributes don't necessarily come into play.

>> Finally, your motivation is that database-valued attributes
>> give us the same addition in expressive power that surrogate
>> identifiers do, when compared to flat, surrogate-less databases.
>> How do you know? What do you mean by it?
>> (I have an idea, but this posting is growing too long.)
>
>Actually I suspect that is not the case. i.e. one cannot always
>eliminate abstract identifiers.

But in that case, what is the attraction of your idea?

>A Triangulated Irregular Network
>seems to be an example of that. How can a set of triangles reference
>shared vertices without introducing identifiers for them?

Represent the edges as a relation of two points (or 6 coordinates) or the triangles as a relation of three points (9 coordinates). Vertices will occur multiple times. Updates are a little awkward (single-tuple updates will be invalid) but it works.

>The only
>alternative seems to be to use the value (i.e. x,y,z) of a vertex to
>identify it and then use integrity constraints to somehow reduce the
>degrees of freedom in the model.

Yes, constraints are unavoidable, but is this a problem?

>It's not clear to me whether it
>could be practical. For example, when a user selects and moves a
>vertex around in a GUI, it would be necessary to update multiple
>appearances of the same (x,y,z) in the model. Failing to do so would
>typically cause an integrity constraint violation.

I don't see how introducing OIDs (surrogates) would help here. The discomfort is in that tuples aren't "entities" in the sense that single-tuple updates are invalid, but adding OIDs won't change that.

>A TIN may not be a very good example because in practise it is usually
>based on a Delaunay triangulation. In other words the triangles are /
>calculated/ from the vertices. Therefore it is sufficient to only
>model the vertices, and so we just have a set of (x,y,z) and no need
>to introduce abstract identifiers.

Yes, except that SQL probably isn't good at Delaunay triangulation (I haven't tried). I suppose materialized views can help here.

But you could take other vector-based data as an example.

For motivating OIDs I'd look at modelling problems for which a fixed relational schema with natural keys is hard or impossible.

Your DVAs are designed for that as well, but they're hierarchical. I like hierarchy as a basis for serialization (e.g. XML, YAML, JSON), not as a basis for persistence.

-- 
Reinier