Chapter 02 - Data Model & Query Languages

Talk about what's SQL, NoSQL. How SQL has mismatch and we have to use ORM.

Many to one relationship

In SQL we can do something like

User table

Country table

Why dont we just use country_name directly in user table.

• it's for ease of updating and searching. Consistent between all users
• This technique is called normalising in db since it remove duplication (i.e every user have a country_name instead of country_id)

In NoSQL database, we're emulate join in the Application layer code

Document DB

DocumentDB advantage: closer to application code

• Use if your application data has a tree like (mostly one-to-many) datastructure
• If it's mostly one-to-many relationship, bad support in join query might not be a problem
  Relational Database: better support for join, many-to-one and many-to-many
• Use if your applicaiton needs a many to many relationship that need join.
• For highly interconnected data, Relational or Graph datamodel is preferred.

[!NOTE]
We specificly only discuss about document db in this section, since now NoSQL has column db, the following only applicable to document db

Schema in DocumentDB

The schema in DocumentDB is called schema on read — the applicaiton itself enforces the schema. Whereas in Relational database is the schema on write — the database enforces the schema.

For example, if your application now instead of storing the full name, we need to store the first name and last name separately:

DocumentDB: we fix in the application code to correct it when reading

if (user && user.name && !user.first_name) {
// legacy behavior:
    user.first_name = user.name.split(" ")[0];
}

SQL: you run a migration and fix it once. However it might requires some down time

ALTER TABLE users ADD COLUMN first_name text;
UPDATE users SET first_name = split_part(name, ' ', 1);

Data locality

Data Locality means storing related data close together in the same record/document so that it can fetch everything in one read.

• This is very good in document database where everything is closed together.
• In relational database, we need to do multiple joins since the data is spread out, which becomes not good

However, this is only good when we want to read the whole object at once. It's inefficient if you often update or read only small part of a large document:

• Reading or updating the small part of the document still requires fetching/overwriting to the whole document.
• Because of this, we often try to keep the size of the document small

Nowadays, there are options to allow locality that's not limited to just document db i.e

1. Google Spanner: the schema allow the table row should be nested within parent table
2. Column family (Cassandra, HBase): also allow locality

MapReduce querying

The map reduce concept is popularised by Google, the idea is you map your dataset first and then reduce to what you want.

Imagine you're biologist and you want to add to your record everytime you see an animal in the ocean, SQL will look something like

SELECT date_trunc('month', observation_timestamp) AS observation_month, sum(num_animals) AS total_animals
FROM observations
WHERE family = 'Sharks'
GROUP BY observation_month;

[!note]
The date_trunc('month', timestamp) here will return a timestamp representing the start of the month

To write this in map reduce, we can do

db.observations.mapReduce(
    function map() {
        var year = this.observationTimestamp.getFullYear();
        var month = this.observationTimestamp.getFullMonth() + 1;
        emit(year + '-' + month, this.numAnimals);
    },
    function reduce(key, values) {
        return Array.sum(values);
    },
    {
        query: {family: "Sharks"},
        out: "monthlySharkReport"
    }
);

The map function will emit the year-month, numAnimals and then consumed by the reduce function. Output will look like

{
    observationTimeStamp: Date.parse("Mon, 25 Dec 1995..."),
    family: "Shark",
    species: "Shark X",
    numAnimals: 3
},
{
    observationTimeStamp: Date.parse("Tue, 12k Dec 1995..."),
    family: "Shark",
    species: "Shark Y",
    numAnimals: 4
}

However this is Imperative language, which might not be optimised. As a result, mongodb write aggregate function which is a Declarative language version of this

db.obersvations.aggregate({
    { $match: { family: "Sharks" }},
    { $group: { 
        _id: {
            year: { $year: "$observationTimestamp"},
            month: { $month: "$observationTimstamp"},
        },
        totalAnimals: { $sum: "$numAnimals" }
    }
})

GraphLike data Model

There are 2 types:

1. Property graph: Neo4J, Titan, Infinite Graph
2. Tripe-stores: Datomic AllegroGraph

Languages

1. Declarative language queries for graph: Cypher, SPARQL and Datalog
2. Imperative language: Gremlin, Pregel

Property graph

graph LR
    %% ── Nodes (vertices): Label + properties ──
    alice["Person<br/><b>Alice</b><br/>age: 32"]
    bob["Person<br/><b>Bob</b><br/>age: 29"]
    acme["Company<br/><b>Acme Inc</b><br/>founded: 1999"]
    berlin["City<br/><b>Berlin</b><br/>country: DE"]

    %% ── Relationships (edges): TYPE + properties ──
    alice -->|"KNOWS<br/>since: 2015"| bob
    alice -->|"WORKS_AT<br/>role: Engineer"| acme
    bob   -->|"WORKS_AT<br/>role: Designer"| acme
    alice -->|"LIVES_IN"| berlin
    acme  -->|"LOCATED_IN"| berlin

    %% ── Style nodes by label ──
    classDef person  fill:#e3f2fd,stroke:#1976d2,color:#0d47a1;
    classDef company fill:#f3e5f5,stroke:#7b1fa2,color:#4a148c;
    classDef city    fill:#e8f5e9,stroke:#388e3c,color:#1b5e20;

    class alice,bob person;
    class acme company;
    class berlin city;

Vertex(node) consists of

1. Unique identifier
2. Set of outgoing edges
3. Set of incoming edges
4. Collection of properties (key/value storage)

Edge consists of

1. Unique identifier
2. Tail vertex (where the edge starts)
3. Head vertex (where the edge ends)
4. Label description
5. Collection of properties (key/value storage)

We can technically represent like this in SQL

CREATE TABLE vertices (
    vertex_id integer PRIMARY_KEY,
    properties json
)


CREATE TABLE edges (
    edge_id integer PRIMARY_KEY,
    tail_vertex integer REFERENCES vertices (vetex_id),
    head_vertex integer REFERENCES vertices (vetex_id),
    label text,
    properties json
)

CREATE INDEX edges_tails ON edges (tail_vertex)
CREATE INDEX edges_heads ON edges (head_vertex)

1. Any vertex can have an edge connecting with any other vertex, no shcema that restrict it
2. Given any vertex, you can find both incoming and outgoing edges and traverse the graph
3. By using different labels for different kinds of relationship, we can combine different kind of information for a single graph while maintain a clean data model

Cypher query language

CREATE CONSTRAINT person_name  IF NOT EXISTS FOR (p:Person)  REQUIRE p.name IS UNIQUE;
CREATE CONSTRAINT company_name IF NOT EXISTS FOR (c:Company) REQUIRE c.name IS UNIQUE;
CREATE INDEX      person_age   IF NOT EXISTS FOR (p:Person)  ON (p.age);

CREATE
  (alice:Person  {name: 'Alice', age: 32}),
  (bob:Person    {name: 'Bob',   age: 29}),
  (acme:Company  {name: 'Acme Inc', founded: 1999}),
  (berlin:City   {name: 'Berlin', country: 'DE'}),

  (alice)-[:KNOWS    {since: 2015}]->(bob),
  (alice)-[:WORKS_AT {role: 'Engineer'}]->(acme),
  (bob)  -[:WORKS_AT {role: 'Designer'}]->(acme),
  (alice)-[:LIVES_IN]->(berlin),
  (acme) -[:LOCATED_IN]->(berlin);

Query example

// Who works at Acme, and in what role?
MATCH (p:Person)-[w:WORKS_AT]->(c:Company {name: 'Acme Inc'})
RETURN p.name AS person, w.role AS role;

// Alice's contacts (edge property filter)
MATCH (:Person {name: 'Alice'})-[k:KNOWS]->(friend)
WHERE k.since <= 2015
RETURN friend.name, friend.age;

// Multi-hop: people who live in the same city as their employer
MATCH (p:Person)-[:WORKS_AT]->(c:Company)-[:LOCATED_IN]->(city:City),
      (p)-[:LIVES_IN]->(city)
RETURN p.name AS person, c.name AS company, city.name AS city;

[!NOTE]
Graph database can technically present as a SQL query however it's very complicated and need nested recursion

Triple-stores

In triple store we store the data in terms of (subject, predicate, object).

• subject is like a vertex
• predicate is similar to edge
• object could be another vertex or a value

For example:

• (Jim, likes, bananas)

Summary

1. Document databases: use for self-cotnained document
2. Graph database: many-to-many heavy relationship
3. Graph database & document database dont enforce the data, however most application will enforce some schema. It's either we want
   1. Enforce on write (relational database)
   2. Enforce on read (Document database, Graph database)