Skip to content

DB Schema Table Demos

Single Table

Simple

users
PK id INTEGER
name TEXT
dob DATE
email TEXT

With Details

users
PK id INTEGER AUTOINCREMENT
name TEXT NOT NULL
dob DATE NOT NULL
email TEXT

Two Tables

departments
PK code TEXT
name TEXT
manager TEXT
employees
PK id INTEGER
name TEXT
FK dept TEXT
notes TEXT

Two Tables Reversed

employees
PK id INTEGER
name TEXT
FK dept TEXT
notes TEXT
departments
PK code TEXT
name TEXT
manager TEXT

Three Tables

orders
PK id INTEGER
date DATE
name TEXT
email TEXT
contains
FK PK order_id INTEGER
FK PK prod_id INTEGER
quantity INTEGER
products
PK id INTEGER
name TEXT
price REAL
description TEXT

Four Tables

customers
PK id INTEGER
name TEXT
email TEXT
address TEXT
orders
PK id INTEGER
date DATE
FK cust_id INTEGER
completed BOOLEAN
contains
FK PK order_id INTEGER
FK PK prod_id INTEGER
quantity INTEGER
products
PK id INTEGER
name TEXT
price REAL
description TEXT

Four Tables, No Types

customers
PK id
name
email
address
orders
PK id
date
FK cust_id
completed
contains
FK PK order_id
FK PK prod_id
quantity
products
PK id
name
price
description

Four Tables, Seq Adjusted

customers
PK id
name
email
address
orders
PK id
date
FK cust_id
completed
contains ++
FK PK order_id
FK PK prod_id
quantity
products --
PK id
name
price
description

Highlight

customers !!
PK id INTEGER
name TEXT
email TEXT
address TEXT
orders
PK id INTEGER
date DATE
FK cust_id INTEGER
completed BOOLEAN

customers
PK id INTEGER
name TEXT
email TEXT
address TEXT
orders !!
PK id INTEGER
date DATE
FK cust_id INTEGER
completed BOOLEAN

Data Table

employees
PK id name dob email
--------- ------ ---------- -----------------
1 Dave 2000-01-01 dave@chips.net
2 Karen 1986-06-02 karen@angry.nz
3 Pierre 1998-12-24 pierre@berets.org

Data Tables

employees
PK id name FK dept notes
--------- ------ ------- -----------------
1 Dave ENG Great at Python
2 Karen MRK
3 Pierre ENG DB architect
4 Nigel ADM
5 Sheila MRK
departments
PK code name manager
----------- -------------- ----------
ADM Administration Tim Smith
ENG Engineering Lucy Pivot
MRK Marketing Mandy Hero

Highlighting

Whole Table

employees
PK id name FK dept notes
--------- ------ ------- -----------------
1 Dave ENG Great at Python
2 Karen MRK
3 Pierre ENG DB architect
4 Nigel ADM
5 Sheila MRK
departments !!
PK code name manager
----------- -------------- ----------
ADM Administration Tim Smith
ENG Engineering Lucy Pivot
MRK Marketing Mandy Hero

Record

employees
PK id name FK dept notes
--------- ------ ------- -----------------
1 Dave ENG Great at Python
2 Karen MRK
3 !! Pierre ENG DB architect
4 Nigel ADM
5 Sheila MRK

Field

employees
PK id name !! FK dept notes
--------- ------ ------- ---------------
1 Dave ENG Great at Python
2 Karen MRK
3 Pierre ENG DB architect
4 Nigel ADM
5 Sheila MRK

employees
PK id name FK dept !! notes
--------- ------ ------- ---------------
1 Dave ENG Great at Python
2 Karen MRK
3 Pierre ENG DB architect
4 Nigel ADM
5 Sheila MRK

Both

employees
PK id name !! FK dept notes
--------- ------ ------- ---------------
1 Dave ENG Great at Python
2 Karen MRK
3 !! Pierre ENG DB architect
4 Nigel ADM
5 Sheila MRK

More Complex

customers
PK id name email address
--------- ------ ---------- --------------
1 Dave dave@... 23 High Street
2 Karen kaz@... 14A Beat St
3 Pierre pierre@... 25 La Rue
4 Nigel beast@... 1 The Avenue
5 Sheila lady@... 15 Bellevue
orders
PK id date FK cust_id completed
------ ---------- ------- ---------
1 2025-01-01 3 true
2 2025-02-12 1 true
3 2025-05-10 5 false
4 2025-05-30 1 true
5 2025-06-01 2 false
contains
FK order_id FK prod_id qty
1 223 3
1 369 1
2 369 7
2 333 3
3 101 6
3 501 1
3 402 5
4 101 1
5 333 2
products
PK id name price description
-------- -------- ----- -----------
101 Cat 10 Mangy
223 Hat 20 Tall
333 Goat 50 Stinks
369 Trousers 45 Short
402 Cheese 12 Delicious
501 Pickles 8 Lumpy

More Complex, Sequence Adjusted

customers
PK id name email address
--------- ------ ---------- --------------
1 Dave dave@... 23 High Street
2 Karen kaz@... 14A Beat St
3 Pierre pierre@... 25 La Rue
4 Nigel beast@... 1 The Avenue
5 Sheila lady@... 15 Bellevue
orders
PK id date FK cust_id completed
------ ---------- ------- ---------
1 2025-01-01 3 true
2 2025-02-12 1 true
3 2025-05-10 5 false
4 2025-05-30 1 true
5 2025-06-01 2 false
contains ++
FK order_id FK prod_id qty
1 223 3
1 369 1
2 369 7
2 333 3
3 101 6
3 501 1
3 402 5
4 101 1
5 333 2
products --
PK id name price description
-------- -------- ----- -----------
101 Cat 10 Mangy
223 Hat 20 Tall
333 Goat 50 Stinks
369 Trousers 45 Short
402 Cheese 12 Delicious
501 Pickles 8 Lumpy

Relationships

  • customers
    • one-to-many
  • orders
    • many-to-many
  • products

With Highlights

  • customers !!
    • one-to-many
  • orders
    • many-to-many
  • products

  • customers
    • one-to-many
  • orders
    • many-to-many !!
  • products

  • customers
    • 1:m
  • orders
    • 1:m !!
  • contains !!
    • m:1 !!
  • products

With Seq Adjustments

  • customers !!
    • one-to-many
  • orders --
    • many-to-many
  • products --

  • customers ++
    • one-to-many
  • orders ++
    • many-to-many
  • products !!

  • customers
    • one-to-many
  • orders
    • many-to-many
  • products

  • customers
    • 1:m
  • orders
    • 1:m
  • contains ++
    • m:1
  • products --

Combined

Here...

  • customers
    • one-to-many
  • orders
    • many-to-many
  • products

Problem...

  • customers
    • one-to-many
  • orders
    • many-to-many !!
  • products

Fix...

  • customers
    • 1:m
  • orders
    • 1:m !!
  • contains !!
    • m:1 !!
  • products --

Giving...

  • customers
    • 1:m
  • orders
    • 1:m
  • contains ++
    • m:1
  • products --

Schema...

customers
PK id
name
email
address
orders
PK id
date
FK cust_id
completed
contains ++
FK PK order_id
FK PK prod_id
quantity
products --
PK id
name
price
description

Example Data...

customers
PK id name email address
--------- ------ ---------- --------------
1 Dave dave@... 23 High Street
2 Karen kaz@... 14A Beat St
3 Pierre pierre@... 25 La Rue
4 Nigel beast@... 1 The Avenue
5 Sheila lady@... 15 Bellevue
orders
PK id date FK cust_id completed
------ ---------- ------- ---------
1 2025-01-01 3 true
2 2025-02-12 1 true
3 2025-05-10 5 false
4 2025-05-30 1 true
5 2025-06-01 2 false
contains ++
FK order_id FK prod_id qty
1 223 3
1 369 1
2 369 7
2 333 3
3 101 6
3 501 1
3 402 5
4 101 1
5 333 2
products --
PK id name price description
-------- -------- ----- -----------
101 Cat 10 Mangy
223 Hat 20 Tall
333 Goat 50 Stinks
369 Trousers 45 Short
402 Cheese 12 Delicious
501 Pickles 8 Lumpy