🎓 A1Learner
🔍
0 of 47 lessons completed

SQL or NoSQL

SQL or NoSQL is a choice between tables with fixed fields and documents with varying fields. The right choice follows the shape of the data, not a promise of extra speed.

The same people as table rows

A table gives every row the same fields. The printed rows all follow one layout.

rows = [
    [1, "Ana", "Lisbon"],
    [2, "Ben", "Porto"],
    [3, "Cora", "Lisbon"],
    [4, "Dan", "Braga"],
]
print("id | name | city")
for row in rows:
    print(row[0], "|", row[1], "|", row[2])

The same people as documents

A document can carry an extra field that other documents leave out. The printed documents vary from one to the next.

documents = [
    {"id": 1, "name": "Ana", "city": "Lisbon"},
    {"id": 2, "name": "Ben", "city": "Porto", "nickname": "Benny"},
    {"id": 3, "name": "Cora", "city": "Lisbon", "club": "Chess"},
    {"id": 4, "name": "Dan", "city": "Braga"},
]
for doc in documents:
    print(doc)

A lookup by city in both shapes

A lookup by city runs over the rows and over the documents. Both shapes print the same matches.

rows = [
    [1, "Ana", "Lisbon"],
    [2, "Ben", "Porto"],
    [3, "Cora", "Lisbon"],
    [4, "Dan", "Braga"],
]
documents = [
    {"id": 1, "name": "Ana", "city": "Lisbon"},
    {"id": 2, "name": "Ben", "city": "Porto", "nickname": "Benny"},
    {"id": 3, "name": "Cora", "city": "Lisbon", "club": "Chess"},
    {"id": 4, "name": "Dan", "city": "Braga"},
]
from_rows = [row[1] for row in rows if row[2] == "Lisbon"]
from_docs = [doc["name"] for doc in documents if doc["city"] == "Lisbon"]
print("Rows in Lisbon:", ", ".join(from_rows))
print("Documents in Lisbon:", ", ".join(from_docs))

Example

rows = [
    [1, "Mia", "Porto"],
    [2, "Leo", "Lisbon"],
    [3, "Ivy", "Porto"],
    [4, "Sam", "Braga"],
    [5, "Zoe", "Porto"],
]
documents = [
    {"id": 1, "name": "Mia", "city": "Porto", "team": "Support"},
    {"id": 2, "name": "Leo", "city": "Lisbon"},
    {"id": 3, "name": "Ivy", "city": "Porto"},
    {"id": 4, "name": "Sam", "city": "Braga", "team": "Design"},
    {"id": 5, "name": "Zoe", "city": "Porto", "club": "Rowing"},
]
from_rows = [row[1] for row in rows if row[2] == "Porto"]
from_docs = [doc["name"] for doc in documents if doc["city"] == "Porto"]
print("Rows in Porto:", ", ".join(from_rows))
print("Documents in Porto:", ", ".join(from_docs))
print("Matches from rows:", len(from_rows))
print("Matches from documents:", len(from_docs))

Key points

  • SQL: data goes into tables made of rows, and every row in a table carries the same fields.
  • NoSQL: data goes into documents, and one document can carry fields that another document leaves out.
  • Fixed fields: a table states its fields up front, so each new row follows the same layout.
  • Use a table to store records that all share the same fields, like orders with an id, a customer and a total.
  • Use documents to store records whose fields differ, like listings where a book has pages and a shoe has a size.

In this example, one group of customers is stored as rows and as documents, and a lookup by city runs over both. The two shapes return the same matches, and the documents still carry extra fields on some records.

Note

Neither shape is simply faster, because speed depends on the work the system does.

Try it Yourself

Edit the code, then press Run.

Running is simulated

Exercise

A system stores listings where some items have a size, some have pages, and some have neither. Which shape fits this varying data best?