Foreign keys

[David P. Hochman]

I'm deep inside a big company IT dept exposing a legacy enterprise
database via JSON & XML, and JSON-Schema is solving a bunch of hard
problems. Except for one.

How does JSON-Schema declare a foreign key? "identity" is boolean and
refers to the current object. "format":"url" is close but too
specific.

I'd like something like SQL's REFERENCES keyword, but more
informational than an enforced constraint. The user agent could use it
to build a url to another resource, as a hyperlink or JSONP request or
Ajax, or just visually indicate that it's special.

What do you think about a new "references" property that's a string
containing a table name or resource name, and an instance value is its
primary key?

Is there some other way to declare a foreign key relationship in JSON-
Schema?

[Kris Zyp]

I totally agree with you that this needed, and have been discussing [1]
possibilities for using an expanded JSON schema definition [2] for
describing RESTful relationships in data with the restful-json group.
However, I think my proposal in the referenced thread doesn't actually
cover your use case sufficiently. I would like to put together a more
formal proposal/spec for the hyper-json definition, that does include a
mechanism for describing properties that should be interpreted as
relationships.

It should be noted you can already do something like this with JSON
referencing (which is basically a convention for defining
hyper-references/links in JSON using "$ref" property). Suppose you have
a couple tables, Project and Task, where the Task table has a project_id
property that is a foreign key to the Project table (Project has a
one-to-many relationship with Task). Now, if we have these tables mapped
to URL namespaces like /Project/{project_id} and /Task/{task_id}, a Task
row like
task_id name project_id
1 Mow the lawn 3
This one row in the Task table is related to the Project row with an id
of 3, which would be mapped to a URL /Project/3. This Task could be
mapped to JSON with a reference to the Project row like (assuming it has
a URL of /Task/1):
{task_id:1, name:"Mow the lawn", project: {$ref: "/Project/3"}}

The user agent can then interpret the project property to be a link to
the appropriate Project resource. Key RESTful aspects of this is that we
are hiding the underlying storage implementation and we utilizing
standard relative URLs for our links.

However, I am guessing that you will be quick to complain about the pain
of having to convert your rows from their more natural JSON mapping with
project_id holding the actual id value, into this alternate JSON
referencing format. In reality, you should be able to do something like
this:
{task_id:1, name:"Mow the lawn", project_id: 3}
And then have a schema that can adequately describe to the user agent
how the project_id should be used to resolve to correct target Project
resource/row that it is related to. Something like (going off your
naming suggestion here):

{
properties: {
task_id: {type:"integer", identity:true},
name: {type:"string"},
project_id: {references: "/Project/"}
}
}
The value of the "references" attribute in the schema would indicate
what the base URL is to use for relative URL resolution of the instance
value. In this case resolving "3" with the base URL of "/Project/" would
resolve to "/Project/3", which would be the correct URL for the target
Project resource/row. Using this approach would also properly hide the
storage implementation (you can map the tables to URLs in any form you
want), defining and abstracting the relationships in terms of URLs. Does
that sound like what you are looking for?

Anyway, hopefully soon, I will be working on getting this into spec form.

[1]
http://groups.google.com/group/restful-json/browse_thread/thread/cf4b0bd444f5fd83
[2] http://www.json-schema.org/hyper-schema

[David P. Hochman]

Yes, your example solves the problem, and answers my unstated
objection that you correctly anticipated!

I should have mentioned that I considered "$ref" and passed on it for
the reason you wrote (the pain of converting stored values to relative
URLs) and because the user agent would then have to extract the actual
value from the link to display it, rather than decorate the actual
value as a hyperlink.

Maybe there's a way to do both, rather than derive one from another.
Another possible low tech approach (using current semantics) is to
represent the actual value, and also add the hyperlink or the table
name or resource name or other meaningful name as a separate parallel
property that could be understood by a UI, e.g.

{ task_id:1, name:"Mow the lawn", project_id:3, project_id_extras:
{href:"/Project/3"} }

with this (verbose) schema:

{
properties: {
task_id: {type:"integer", identity:true},
name: {type:"string"},

project_id: {type:"integer"},
project_id_extras: {
type:"object",
hidden:true,
optional:true,
requires:"project_id",
properties: {
href: {type:"url", optional:true},
label: {type:"string", optional:true}
}
}
}
}

After reading the discussion you referenced in restful-json and poring
over the specs, I'm reticent about this suggestion, because it
ventures outside of using JSON-Schema to define the structure, and
into guidance for the UI.

OTOH, the CREATE TABLE syntax (a.k.a. DDL - Data Definition Language)
is also a common way to define the data structure, and it explicitly
defines relationships that are used to document and validate, and
incidentally useful to a UI. That seems to be the only kind of
structural property that has no corresponding property in JSON-
Schema. And since it's familiar to enterprise IT, it helps bridge OSI
layers 8 & 9 (i.e. politics & economics).

So, I'm tempted to add "references" and other UI-related properties
myself without stepping on the core. However, the JSON Schema of JSON
Schemas doesn't seem to permit additional properties or enable a
namespace-like extension mechanism. Is there a way to piggy-back
implementation-specific properties that could help drive a UI (e.g.
references, tags, annotation, label, validation function,
transformation function, display template, wrapper element, class
name) and allow me to leverage a schema for more uses?

> [1]http://groups.google.com/group/restful-json/browse_thread/thread/cf4b...

[Ganesh and Sashi Prasad]

Are foreign keys meaningful for hierarchical data structures (as opposed to relational structures)?

I can't think of a good counterexample, but if the model itself is the wrong one, then there could be some esoteric case somewhere that will break it by introducing cyclicality or some such...

Ganesh

2009/7/2 David P. Hochman <david....@gmail.com>

[Kris Zyp]

JSON schema doesn't set additionalProperties: false, therefore it is
allowed to have additional properties. It is intended to be extensible
in that sense, so you can use implementation-specific properties to
annotate with additional information, just as you suggest.
Kris

[Tatu Saloranta]

On Thu, Jul 2, 2009 at 2:19 AM, Ganesh and Sashi
Prasad<g.c.p...@gmail.com> wrote:
> Are foreign keys meaningful for hierarchical data structures (as opposed to
> relational structures)?
>
> I can't think of a good counterexample, but if the model itself is the wrong
> one, then there could be some esoteric case somewhere that will break it by
> introducing cyclicality or some such...

I think that foreign keys are not meaningful for (object) hierarchies:
3 major data models (relational, hierarchic, object/frames) have
impedance between each.

So I think correct way of thinking of foreign key references is to
consider it something to reduce impedance between what JSON schema is
designed to handle (object model, which is sort of hierarchy plus
identity referencing), and relational model. But not trying to define
relational model -- that should be out of scope, really.

So why support it at all? Probably because serializing relational data
is a very common use case, so it makes some sense to add "non core"
support.

-+ Tatu +-

[Tatu Saloranta]

Btw, forgot to prefix all of that with "IMHO" -- just my thinking. I
hope to be able to use JSON schema for defining object
binding/serialization aspects, similar to xml schema. And as such, my
main focus are object-related aspects. I think it is a good idea to
consider interoperability cases too, including compatibility with
relational model, just wanted to mention that there's a
separation-of-concerns aspect here. :-)

-+ Tatu +-

[Kris Zyp]

Do you think that the being able to use a schema to define the mapping
of property values to URLs for RESTful serialization of relational rows,
where the relationalships are abstracted to URL hyperlinks? IMO, this
provides the appropriate separation of concerns because the client is
only aware of resources and the property values that references other
resources (hyperlinks), thus the underlying storage model isn't
necessarily exposed. At the same time, it is easy to define a schema
that would allow the underlying db data to coincide with the serialized
JSON and implied hyperlinks (by mapping keys to URLs, thus foreign keys
can be used a relative URLs with the appropriate base URL defined by the
schema.).
Kris

[Tatu Saloranta]

On Wed, Jul 15, 2009 at 1:51 AM, Kris Zyp<kri...@gmail.com> wrote:
>
> Tatu Saloranta wrote:
...

>> So I think correct way of thinking of foreign key references is to
>> consider it something to reduce impedance between what JSON schema is
>> designed to handle (object model, which is sort of hierarchy plus
>> identity referencing), and relational model. But not trying to define
>> relational model -- that should be out of scope, really.
>>
>> So why support it at all? Probably because serializing relational data
>> is a very common use case, so it makes some sense to add "non core"
>> support.
>>
> Do you think that the being able to use a schema to define the mapping
> of property values to URLs for RESTful serialization of relational rows,
> where the relationalships are abstracted to URL hyperlinks? IMO, this
> provides the appropriate separation of concerns because the client is
> only aware of resources and the property values that references other
> resources (hyperlinks), thus the underlying storage model isn't
> necessarily exposed. At the same time, it is easy to define a schema

Yes, that sounds reasonable to me.

> that would allow the underlying db data to coincide with the serialized
> JSON and implied hyperlinks (by mapping keys to URLs, thus foreign keys
> can be used a relative URLs with the appropriate base URL defined by the
> schema.).

Yes. I think it is reasonable to expect that using
apps/frameworks/systems can do proper binding to concrete model (for
Objects, to object definitions, for DBs, schemas), from more generic
model based on URLs. And ideally, using agreed-upon conventions would
make this both flexible and non-intrusive and convenient to
use/implement.

-+ Tatu +-

[Dan Reverri]

Would it be possible to see an example of a JSON schema with a foreign
key and how one might map it to an RDBMS?

[JF Gauvin]

Hi,

Anyone has been able to do this?

I've also seen a more recent thread talking about foreign keys (https://groups.google.com/forum/?hl=fr#!topic/json-schema/0b3rp49m21o), but it's not the ideal solution.

Thanks.