A JSON Db Product?

The last post "solved" the problem of many-to-many table joins by papering over the association table with a RESTful JSON interface. As long as we're using JSON, we might as well take advantage of multi-valued table cells.

I'm naturally wondering where this leads. JSON identifiers and types and SQL identifiers and types overlap so much that their intersection is a useful subset. Camel-case fields in string, number, bool flavors.

Many-to-many occurs often in the world:

• Students in Classes
• Actors in Films (musicians on recorded songs)
• Parts in Assemblies
• Customers and Products (joined by Orders)

The generalized description is that a Table requires a unique identifier for each row. Tables list students, actors, films, customers, and so on. 

An Association Table is has two or more foreign keys that match unique identifiers in other tables. The knowledge of how a FK maps to a specific Table is baked in--we wouldn't want a "table name" column. 

An Association Table, such as Orders, might have considerable additional information besides the two FKs. This Customer and this Product, with date, quantity, price and other line item data.

For Many-to-one relationships, we can introduce FK columns into general Tables, as well. The Films table might include a FK to the Studios table. Boring old one-to-one relationships would work the same way.

GET

Our JSON table needs a way to specify multi-valued columns. The Class table might include:

• classId
• className
• instructor
• room
• department (FK)
• students (array of Student rows found with the StudentClass Association Table)

The students array wants only the necessary subset of student data--how do we specify that? And how do we convey association data, such as signupDate?

Conceptually, the students array found in the Class record is an array of structs (an old C concept found anew in Go). This is an ephemeral "association schema" that exists in our JSON interface, but not in the database. The student struct might look like:

{

    "studentId": 1, // This allows the UI to drill down to the

                    // full Student record, or to delete the association

    "studentName": "",  // built from first and last fields in Students

    "enrollDate": "",   // from Students table

    "signupDate": ""    // from Association table

}

This struct is presumably more compact than the entire Student record. (Our "struct," by the way, is just an associative array in JSON and PHP.)

It might be useful to GET an Association table record. This could be primitive:

{

    "Classes/classId": 34,

    "Students/studentId": 1766,

    "signupDate": 4/29/2017

}

But a primitive approach would be a tedious collection of tiny records. A version using arrays would accomplish more, but it requires that we gesture which side of the association we care about:

{

    "Classes/classId": 34,

    "students": [ { struct }, { struct } ]

}

or

{

    "Students/studentId": 1766,

    "classes": [ { struct }, { struct } ]

}