What is the schema to specify relationships in a flat-document structure?

Question

I have a question about the best database schema to do this. So, we want our database schema to carry out some generic operation <> involving two units of data, x1 and x2 (e.g. a1).

// CURRENT SCHEMA of single document in NOSQL db
{
  A : { items: [a1] }
  B : { items: [b1, b2, b3] }
  C : { items: [c1, c2] }
}

Here is an example:

• A = country, a1=united states.
• B = cities, b1 = new york city b2= florida, b3 = boston.
• C = tv shows c1 = likes rick n morty c2 likes the office.
• <>= percentage population.

As you can see, it makes snse for a1 <> b1 <> c1 = percentage of population who is ( from us, from nyc, likes rick n morty ) or even b2 <> c2. In this case, every combination (even of different lengths) works and makes sense.

What if I expanded as below:

• A = country, a1=united states a2 = russia.
• B = cities, b1 = new york city b2= florida, b3 = boston b4 = st petersburg b5 = moscow.
• C = tv shows c1 = likes rick n morty c2 likes the office.
• <>= percentage population.

It doesn't work anymore! For example, a2 <> b1 <> c2 or a1 <> b5. The main reason is because <> is no longer well-specified since there is an interaction between country and cities, i.e. you cannot take percentage population of a country that doesn't have that city within it. Of course, you might make the output of the operator 0, but this will have performance issues if there are more intereactions.

MORE CONCRETE EXAMPLE

I want to develop further on the example united states/russia example to demonstrate a possible solution. Here is a collection called "mainObjects".

// CURRENT SCHEMA of single document in NOSQL db
{
  country : { items: [ {id: united states, value: 0.5}, {id: russia, value: 0.5}] }
  cities : { items: [{ id: new york city, value:0.3}, { id:florida, value: 0.2}, { id: boston, value: 0.2}, { id: st petersburg, value: 0.1}, {id:moscow, value: 0.2}] }
  tv shows : { items: [{id: rick and morty, value: 0.3}, {id: the office, value: 0.7}] }
}

In another collection called "nodeToEdges" we store all possible combinations of the units of data, where the key is the node and the array is the nodes it is connected to, i.e. nodes: [ ...nodes connected to], i.e. there is 9 keys.

// LOOKUP TABLE of ids: will be versioned
{
united states: [ new york city, florida, boston, rick and morty, the office] 
russia: [st petersburg, moscow, rick and morty, the office]
new york city: [ united states,florida, boston, rick and morty, the office]
florida: [ united states, rick and morty, the office]
boston: [united states, rick and morty, the office]
st petersburg: [russia, rick and morty, the office] 
moscow: [russia, rick and morty, the office]
rick and morty: [united states, russia, florida, boston, st petersburg, moscow]
the office: [united states, russia, florida, boston, moscow, st petersburg]
}

Query 1

Our main objective is to be able to make use of information "mainObjects" and "nodeToEdges" to be able to get a transformed main object. Notice that if you use the lookup table, there is no ids that are not possible. For example, new york city, florida, boston, rick and morty, the office are all nodes in new york city array. And the same is true for florida, boston, rick and morty, the office.

// QUERIED OBJECT 
{
  country : { items: [ {id: united states, value: 1}] }
  cities : { items: [{ id: new york city, value:0.428}, { id:florida, value: 0.285}, { id: boston, value: 0.285}] }
  tv shows : { items: [{id: rick and morty, value: 0.3}, {id: the office, value: 0.7}] }
}

Query 2

Say perhaps, we want to group within category. For example, join rick and morty and the office. The lookup table will have used "rick and morty" and "the office" in the lookup table.

// QUERIED OBJECT 
{
  country : { items: [ {id: united states, value: 1}] }
  cities : { items: [{ id: new york city, value:0.428}, { id:florida, value: 0.285}, { id: boston, value: 0.285}] }
  tv shows : { items: [{id: rick and morty x the office, value: 1.0}] }
}

Query 3

Say perhaps, we want to group across category. For example, tv shows and city. This one is a little more difficult.

// QUERIED OBJECT 
{
  country : { items: [ {id: united states, value: 1}] }
  cities x tv shows : { items: [

{ id: new york city x rick and morty , value:0.1284}, { id: florida x rick and morty, value: 0.0855}, { id: boston x rick and morty , value: 0.0855}

{ id: new york city x the office , value:0.2996}, { id: florida x the office, value: 0.1995}, { id: boston x the office , value: 0.1995}

] 

}
}

The property still holds that all nodes are connected.

Query 4

Say perhaps, we want to group across category but for specific units of data. For example, rick and morty . This one is a little more difficult.

// QUERIED OBJECT 
{
  country : { items: [ {id: united states, value: 1}] }
  cities x tv shows : { items: [

{ id: new york city x rick and morty , value:0.1284}, { id: florida x rick and morty, value: 0.0855}, { id: boston x rick and morty , value: 0.0855}

{ id: new york city x the office , value:0.2996}, { id: florida x the office, value: 0.1995}, { id: boston x the office , value: 0.1995}

] 

}
}

The transformation of course is business logic. But, I want to know if this is the best schema to do this? Or if there any alternate suggestions?

An alternative would be to store the edges within the main object. For example as below.

{
  country : { items: [ {id: united states, value: 0.5, edges: [ new york city, florida, boston, rick and morty, the office]  }, {id: russia, value: 0.5, edges: [st petersburg, moscow, rick and morty, the office]}]

...

 }

Answer 1

If I had two depended properties such as country and city I would model them as one child object.

{
   locations : [{
      country : "US",
      city : "New York",
   }],
   show : ["the office"]
}

You could then use the $elemMatch operator to query for the appearance of New York and US together.