VODataService becoming WD, then PR

[Gerard]

Hi Paul and Ray

I agree with Paul that existence of foreign keys is very important
information that should be captured if possible. Modeling this should not be
hard.
To support situations where multiple columns are used in a key one follows
relational database practice, which defines foreign keys independently of
the column definitions. 
One could add to the XSD something like


  <xs:complexType name="ForeignKey">
    <xs:sequence>
      <xs:element name="targetSchema" type="xs:string"/>
      <xs:element name="targetTable" type="xs:string"/>
      <xs:element name="fkColumn" type="vs:FKColumn" maxOccurs="unbounded"/>

    </xs:sequence>
  </xs:complexType>

  <xs:complexType name="FKColumn">
    <xs:sequence>
      <xs:element name="fromColumn" type="xs:string"/>
      <xs:element name="targetColumn" type="xs:string"/>
    </xs:sequence>
  </xs:complexType>


And use this the definition of the Table complexType:

 <xs:complexType name="Table">
   <xs:sequence>
 ...
     <xs:element name="foreignKey" type="vs:ForeignKey" minOccurs="0"
maxOccurs="unbounded"/>
 ...


I am not using IDREFs, though they might be more appropriate, but
xs:string-s, assumed to represent the names of the corresponding objects. 

A question. Is this specification supposed to support all features required
by TAP metadata?
If so, does it make sense to allow adding SQL datatypes to column
(TableParam) definitions?

Best regards

Gerard

> -----Original Message-----
> From: Ray Plante [mailto:rplante at poplar.ncsa.uiuc.edu] 
> Sent: Monday, November 17, 2008 4:39 PM
> To: IVOA Registry WG
> Subject: Re: VODataService becoming WD, then PR
> 
> Hey Paul,
> 
> Thanks a 10^6 for having a look.
> 
> On Mon, 17 Nov 2008, Paul Harrison wrote:
> > I have one immediate issue with the latest version of the 
> > VODataService schema/specification. I think that the table 
> description 
> > should contain an explicit definition of how to specify 
> foreign keys 
> > that define the relations between tables - these relations 
> are, after 
> > all, fundamental to the way that the relational databases function, 
> > and it is impossible to do a multi-table query without this 
> information.
> 
> As you alluded, the <relationalJoin> element provoked some 
> criticism that led to it being dropped.  The most cogent 
> argument was that this was it was not shown effective in an 
> existing prototype, although the problem you mention is 
> recognized (see item 6 under 
> http://www.ivoa.net/cgi-bin/twiki/bin/view/IVOA/VODataService#
> Proposed_Changes,
> if you haven't already).
> 
> One of the problems that has been cited is that primary and 
> foreign key matching is not always that simple; in 
> particular, keys sometimes span across multiple columns.  On 
> the other hand, a simple solution as you suggest may satisfy 
> a majority of situations.
> 
> > I think that it is possible to come up with something much 
> simpler - 
> > e.g an optional fkey attribute on a TableParam that points to the 
> > primary key by using the "table.column" syntax for the attribute 
> > value.
> 
> As a means of prototyping such a solution, the document does 
> provide several mechanisms for extension (section 3.3.2).  
> Two that might work
> include:
> 
>    o  extending the TableParam type to add the additional 
> metadatum as an
>       element and invoking it with the usual xsi:type mechanism.
> 
>    o  decorating the column element with an attribute from an external
>       schema; this is allowed because the TableParam type 
> definition now
>       includes a '<xs:anyAttribute namespace="#other" />'.
> 
> Nevertheless, I would like to hear more discussion on this.  
> May I put out a call for comment on this specific issue?  It 
> may help if a specific proposal is recommended.  I don't want 
> to slow the progress VODataService, but if there is 
> discussion about it, it would be completely appropriate to 
> have it extend into the official RFC period if necessary.
> 
> thanks,
> Ray
> 
>