Toying with Relational Databases using Dataview


created: 2021-04-27
time: 09:39:13
tags: Share-and-Showcase

Toying with Relational Databases using Dataview

Assuming a prerequisite knowledge of Dataview and Relational Databases, for example the ones in Notion, we proceed to their implementation using the Dataview plugin. I am taking a toy example of a college hub to go though this process.

Creating multiple Databases

In Obsidian Dataview, creating separate databases must be created through creating notes(or pages). Each page must be organised into a specific database, though some field, most appropriate might be a hash#tag, but to reduce clutter one may use a category: field.

Here, Categories (each being a separate database or group of pages)
= {Semester, Course, Person, Lecture, Readings, Tutorial, Test}

We start now creating typical pages for each category:

Title: Semester 1 Title: MAT101 Title: Nikunj
category: semester

# Semester 1 Courses

~~~dataview
Table subject, syllabus, teacher, teacher.mail
FROM ""
SORT semester
WHERE category="course" AND semester=[[Semester 1]]
~~~
category:: course
semester:: [[Semester 1]]
subject:: [[MAT]]
syllabus:: [[a.pdf]]
teacher:: [[Nikunj]]


This course is taught by `= this.teacher`, available at `= this.teacher.mail`.
mail:: [email protected]

Which gives this as preview:


Thus we have already prepared a relational database connection with teacher and course.

Proper creation of relation

Relation (for example Relation in Notion Databases) is not bidirectional in Dataview. Only in one of the pages the field+link can be made, and if the practice is not made properly, the database can break.

Proceeding with an example, let’s consider our course MAT101 has some lectures attached to it.

File: [[MAT101.L01]]

category:: lecture
course:: [[MAT101]]
date:: @23MAY21

And each lecture has some pre-lecture reading and post-lecture tutorials. I will make this two connection in two ways that it can be made.

File: [[MAT101.R01]]

category:: reading
lecture:: [[MAT101.L01]]
material:: [[Matter.pdf]]

File: [[MAT101.L01]] ~ changes made from above

category:: lecture
course:: [[MAT101]]
date:: @23MAY21
tutorials:: [[MAT101.T01]]

It is evident that the Readings page has field+link to the lecture(s) and the lecture pages themselves have the field+link to the tutorial pages. Lecture pages are higher in the hierarchy but the linking in done in two ways: reading->lecture and lecture->tutorial.


Each arrow represents a field+link here. In Notion, the relation link appears bidirectionally <->.

So, it can be now understood, all Readings pages must have the lecture field. It cannot be the other way around in the middle, a lecture having reading field. In Notion, this shows up bidirectionally. But here the field+link cannot be placed in both places automatically.

Rollups

If the premise one must follow the appropriate placement of field in all of the user defined-category pages continuously we can have proper Dataview rollups, no matter the type connection we are making (the reading->lecture way or the lecture->tutorial way; where lectures are higher in the hierarchy)

Following examples gives the rollup of readings and tutorials with-respect-to the list of lectures.

For the reading->lecture rollup is a bit tricky, we make a table of reading files and group them by lectures. The lecture->tutorial rollup is fairly straightforward.

File: [[MAT101.L01]] ~ addition made after above

# Pre-Lecture Reading Material by Lectures
~~~dataview
Table lecture,rows.file.link AS "readings", lecture.date, lecture.course.teacher
WHERE category="reading"
flatten lecture
group by lecture
sort lecture
~~~

# Post-Lecture Tutorials by Lectures

~~~dataview
Table tutorials, tutorials.tutor
FROM ""
WHERE category="lecture"
SORT file.name
~~~

We can also prepare a overall list of all lectures and their readings and tutorials by using:

~~~dataview
Table lecture,rows.file.link AS "readings", lecture.tutorials AS "tutorials", lecture.date
FROM ""
WHERE category="reading"
flatten lecture
group by lecture
sort lecture
~~~

which previews:

Caveats

Due to unidirectional nature of field+link relations, there is one caveat in the reading->lecture type of relation. If there are multiple categories like reading, let’s say another picnic category, where we place the category:: picnic lecture:: [[MAT101.L01]] then calling Dataview to prepare a overall table with lecture, reading and picnic columns is not possible.


In this example we cannot have this through Dataview:

We can have this:


through:

~~~dataview
Table lecture,rows.file.link AS "readings and picnics"
WHERE category="reading" OR category="picnic" 
flatten lecture
group by lecture
sort lecture
~~~

Thus we can have only one reading->lecture type of relation, to build overall lists (separated into columns). This is possible in Notion because of it’s bidirectional relations. Building singular lists for reading and picnic is obviously possible.

Overall lists is possible for multiple lecture->tutorial type of relation though.

Sidenote:
As far as I found, we cannot show rows.file.link into two separate columns based on criteria, do start a conversation here if you can find a way to do that ; )

Feats

Obsidian-Dataview Databases have a overall non-hierarchical non-categorical structure. The database is build from pages → table not table → pages. We do not have to place any page in any specific category/database/hierarchy to create it. Just placing it in the correct folder (if the user uses folders) and placing the correct fields and the work is done. Hence we do not have to maintain a long table of all lectures ever taken in all semesters to view a table of that size.

With inline Dataview, calling up data, that is placed hierarchically above, is easily possible.

For example, we can place in MAT101 page’s YAML

room:
	Mon: LC61
	Wed: LC45
	Fri: LC55

And in a lecture page we can call = this.course.room.Mon based on the days (through templates probably) and this data does not have to placed on page by page basis (creating a lecture template for each course!). In other words Dataview Databases makes rollups more flexible than in Notion.

But another way of looking at this setup is that: Obsidian-Dataview Databases, because of the lack of bidirectional relations, motivates a more hierarchical structure between Semester - Course - Lectures - Readings & Tutorials (high to low in hierarchy).

To be continued…

I shall add more snippets regarding this topic if any, and also updates in the future. :eyes:

The examples in this article is available as a Notion page of databases. ^^ Here


author.discord:: riddyrayes#7583

39 Likes

Thank you for sharing, finally, I got some understanding of the nature of the Dataview plugin. In the past, I just use it to query Todos or other basic things and now I realized that this is a powerful tool to integrate information just like an automatically assembled information center.

2 Likes

i appreciate the way you broke down these examples. it opened my mind up to possibilities. something about your teaching style made it so easy to wrap my head around dataview.

thank you for your efforts!

2 Likes

That’s fascinating. I found your example quite interesting. Especially the use case of study management.

I was wondering what the potential of such a set up could be for Learning Management (Systems). That is delivering content, completing / consuming content and monitoring completion.

Just thinking out loud but one big issue in education is data security …. With obsidian, all is on the students / teacher device.

What would be hard though is to report back any work / completion to any teacher. Also markdown would not be to familiar in that context.

Anyhow, I see some potential for tertiary education / life long learning courses.

1 Like

I would love to be kept in the loop on this topic and would be interesting to explore learning usecase

1 Like

DavaView可视化

5 Likes

I can only read English (I know, shame on me), but this looks VERY interesting.

2 Likes

kinda unrelated but can you please share the name of the theme? :sweat_smile:

1 Like

That was Spectrum theme with a bit of css to add color to the headings!

1 Like