Many to many relationships in JSON

Question

Consider this scenario:

You want to send some data to the client in JSON format, and you don't want to go back to the server. The data consists of 15 teachers with 100 students. The relationship between these entities is many to many (each student learn many teachers and each teacher teaches to many students).

In client, user is presented with the list of students. On click of any student, the list of his/her teachers would be presented to the user, and on click of a teacher, the list of all students of that teacher would be presented. This results in infinite click-through style navigation from students to teachers and vice verca.

Now, as you know, JSON only represents one-to-many relationship in this form:

{ "s1" : [ "t1", "t2"], "s2" : [ "t2", "t4" ], "s3" : [ "t1", "t3", "t4"], ...}

Do you have any idea on how to do this?

Avestan

Answer 1

As JSON does not have a concept of references, you should not need to worry about them. That which defines what counts as a relation between teachers and students lies outside of the data, i.e. is simply a matter of your interpretation during runtime, through the entities' identifiers.

var faculty = {
 "teachers": {
   "t1": ["s1","s2","s5"],
   "t2": ["s2","s7","s9"]
  },
 "students": {
   "s1": ["t1","t2"],
   "s2": ["t2","t7"]
  }
}

For example:

alert("Teacher t1's students are: " + faculty.teachers.t1.toString() );
alert("Student s2's teachers are: " + faculty.students.s2.toString() );
alert("Student s2's first teacher's students are: " + faculty.teachers[faculty.students.s2[0]].toString() );

Tremml

But this implies that there will be a lot of redundant data. If teachers were full blown objects, and one's phone# changes, it is potentially to be changed at multiple places. Shouldn't we mimic RDMBS's where such a case is solved with a separate 'join' table? – Thereunder

Marten, not at all. { ..[snip]... "t1": { "students": ["s1","s2"], "phone": "[phone]" } } – Tremml

Answer 2

You could make an array of pairs describing the relations as a directed graph?

[// from , to
    ["s1", "t1"],
    ["s1", "t2"],
    ["s2", "t2"],
    ["s2", "t4"],
    ["t1", "s1"],
    ["t1", "s2"],
    ["t1", "s3"],
    ["t1", "s4"]
]

It wouldn't be concise. But it would describe your dataset.

Brainstorming

This is a hack. But it was a good suggestion @Exelian. Thanks and +1. – Avestan

Answer 3

I wanted my JSON to be formatted like SQL tables.

{
  "teachers": [
    { "t_id": 11, "name": "t11" },
    { "t_id": 12, "name": "t12" }
  ],
  "students": [
    { "s_id": 21, "name": "s21" },
    { "s_id": 22, "name": "s22" }
  ],
  "teachers_students": [
    { "t_id": 11, "s_id": 21 },
    { "t_id": 11, "s_id": 22 },
    { "t_id": 12, "s_id": 22 }
  ]
}

In SQL terms, the primary keys of the "teachers" and "students" tables are "t_id" and "s_id". People can have the same name, so surrogate keys are best. "teachers_students" is a junction table that stores the relationship between the two other tables by using their primary keys.

An alternative format for "teachers_students" that I think looks better:

  "teachers_students": [
    [11, 21],
    [11, 22],
    [12, 22]
  ]

Hysterotomy