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 showrows.file.linkinto 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. 
The examples in this article is available as a Notion page of databases. ^^ Here
author.discord:: riddyrayes#7583
