Modelling an atypical One to Many relation

[Subh]

Consider the following two schemas:

Block{
	_id
	type
	desc
	size
}

User{
	_id
	name
	email
}

One User can refer to a Block multiple times.
Also, one User can refer to multiple Blocks. (Typical One to Many)

Clearly, it’s not the typical One to Many relations.

Considering that we don’t require the Users who are referring a block. We can safely ignore the relation from Block to User!

How shall I model it?

Say I have the following Block:

{_id: 1, type: 'ABC', desc: 'ABC Block', 'size': 20}
{_id: 2, type: 'DEF', desc: 'DEF Block', 'size': 40}
{_id: 3, type: 'GHI', desc: 'GHI Block', 'size': 60}

A user instance: (without blocks)

{
 _id: 100,
 name: 'A',
 email: '[a@abc.com](mailto:a@abc.com)',
}

Shall I model the User with Blocks reference like the following?

{
	_id: 100,
	name: 'A',
	email: 'a@abc.com',
	1: [
		{_id: 100, 'value': 100, 'last_edited': 'DATE'},
		{_id: 200, 'value': 100, 'last_edited': 'DATE'},
	],
	2: [
		{_id: 400, 'value': 120, 'last_edited': 'DATE'},
	]
}

[steevej (Steeve Juneau)]

You sample documents are not consistent with your description.

You show block with _id $in [ 1 ,2 ,3 ] and your example of block reference with the user refers to _id $in [ 100 , 200 , 400 ] which looks more like user _id. You also have fields value and last_edited that do not appears else where.

We need more details about your model. But more importantly we need more information about your use-cases. How data is edited? How data is accessed? What is a typical scenario?

[Subh]

Hi @steevej, thank you for the reply.

As mentioned earlier, “One User can refer to a Block multiple times.” The following are references to block 1 multiple times. The key “1” means references to Block 1. (Same goes for key “2”.) And the individual documents are the multiple references to Block 1. Please note that the _id (100, 200 in here) can be anything (arbitrary).

	1: [
		{_id: 100, 'value': 100, 'last_edited': 'DATE'},
		{_id: 200, 'value': 100, 'last_edited': 'DATE'},
	],

And please consider the user._id as 1000 (and not 100). (That was a mistake). But it shouldn’t matter as there are no relations from the User to anything in which we are interested.

[steevej (Steeve Juneau)]

See Aggregate $sort multiple fields in order - #2 by Stennie and pay particular attention about using numeric field names.

Sorting multiple fields produces wrong order - #3 by Stennie is also of interest.

I strongly suggest you redesign your block accesses using Building with Patterns: The Attribute Pattern | MongoDB Blog to give something like:

{
  user_id: 1000,
  name: 'A',
  blocks :
  [
   { "block_id" : 1,
     "accesses" : [
		{_id: 100, 'value': 100, 'last_edited': 'DATE'},
		{_id: 200, 'value': 100, 'last_edited': 'DATE'},
	] },
    { "block_id" : 2,
      "accesses" : [
		{_id: 400, 'value': 120, 'last_edited': 'DATE'},
	] }
  ]
}

to keep together the accesses of a given block. But, personally, I would use:

{
  user_id: 1000,
  name: 'A',
  email: 'a@abc.com',
  blocks :
  [
    { block_id : 1 , _id: 100, 'value': 100, 'last_edited': 'DATE'},
    { block_id : 1 , _id: 200, 'value': 100, 'last_edited': 'DATE'},
    { block_id : 2 , _id: 400, 'value': 120, 'last_edited': 'DATE'},
  ]
}

[Subh]

Awesome. Somehow I did go with the design you mentioned above. Although the reason was the fact that GraphQL doesn’t support such schema (I originally posted). (We can further add a custom index on “blocks.block_id” for faster access).
The reason I wanted to go with the design I proposed above is because I generally tend to have a notion that access in the dictionary is O(1) amortized. Whereas typical B trees will have O(log n). I still don’t have a convincing argument on why we don’t store things as dictionaries. Maybe because of worst-case time complexity is O(n) or something else.
Let’s leave it for some other day. I really appreciate your help. Thank you again!