Denormalise Your Database - Schema Design for Modern Database Systems

Slide 1

Slide 1 text

Denormalise Your Database Schema design for modern database systems

Slide 2

Slide 2 text

No content

Slide 3

Slide 3 text

Database Examples MongoDB PostgreSQL MySQL MS Access

Slide 4

Slide 4 text

Normalisation

Slide 5

Slide 5 text

Unnormalised ID album founded published tracks 1 Anastacia - Not That Kind 1999 2000 {1. Not That Kind, 2. I’m Outta Love, 3. Cowboys & Kisses, … } 2 Pink Floyd - Wish You Were Here 1965 1975 {1. Shine On You Crazy Diamond} 3 Anastacia - Freak Of Nature 1999 2001 {1. Paid My Dues }

Slide 6

Slide 6 text

1 NF: mixed data ID album artist founded published track title 1 Not That Kind Anastacia 1999 2000 1 Not That Kind 1 Not That Kind Anastacia 1999 2000 2 I’m Outta Love 1 Not That Kind Anastacia 1999 2000 3 Cowboys & Kisses 2 Wish You Were Here Pink Floyd 1965 1975 1 Shine On You Crazy Diamond 3 Freak Of Nature Anastacia 1999 2001 1 Paid My Dues

Slide 7

Slide 7 text

2 NF: Tracks album_id track title 1 1 Not That Kind 1 2 I’m Outta Love 1 3 Cowboys & Kisses 2 1 Shine On You Crazy Diamond 3 1 Paid my Dues

Slide 8

Slide 8 text

2 NF: Albums album_id album artist founded published 1 Not That Kind Anastacia 1999 2000 2 Wish You Were Here Pink Floyd 1965 1975 3 Freak Of Nature Anastacia 1999 2001

Slide 9

Slide 9 text

3 NF: Artist artist_id artist founded 1 Anastacia 1999 2 Pink Floyd 1965

Slide 10

Slide 10 text

3 NF: Albums album_id album artist_id published 1 Not That Kind 1 2000 2 Wish You Were Here 2 1975 3 Freak Of Nature 1 2001

Slide 11

Slide 11 text

Entities /** @ORM\Entity() */ class Artist { /** @ORM\Column(type="string", length=255) */ private $name; /** @ORM\Column(type="integer") */ private $founded; /** @ORM\OneToMany(targetEntity=Album::class, mappedBy="artist") */ private $albums; }

Slide 12

Slide 12 text

Entities /** @ORM\Entity() */ class Album { /** @ORM\Column(type="string", length=255) */ private $title; /** * @ORM\ManyToOne(targetEntity=Artist::class, inversedBy="albums") * @ORM\JoinColumn(nullable=false) */ private $artist; /** @ORM\Column(type="integer") */ private $published; /** @ORM\OneToMany(targetEntity=Track::class, mappedBy="album") */ private $tracks; }

Slide 13

Slide 13 text

Entities /** @ORM\Entity() */ class Track { /** @ORM\Column(type="integer") */ private $trackNumber; /** @ORM\Column(type="string", length=255) */ private $title; /** * @ORM\ManyToOne(targetEntity=Album::class, inversedBy="tracks") * @ORM\JoinColumn(nullable=false) */ private $album; }

Slide 14

Slide 14 text

Fetching Data SELECT * FROM artists WHERE name = 'Anastacia'; SELECT * FROM albums WHERE artist_id = 1; SELECT * FROM tracks WHERE album_id = 1; SELECT * FROM tracks WHERE album_id = 3;

Slide 15

Slide 15 text

Fetching Single Album SELECT * FROM albums JOIN artists ON albums.artist_id = artists.id WHERE albums.title = 'Freak Of Nature’ AND artists.name = 'Anastacia';

Slide 16

Slide 16 text

Fetching Single Track SELECT * FROM tracks JOIN albums ON tracks.album_id = albums.id JOIN artists ON albums.artist_id = artists.id WHERE artists.name = 'Anastacia' AND albums.title = 'Not That Kind' AND tracks.title = 'Not That Kind';

Slide 17

Slide 17 text

Let’s Try Something New

Slide 18

Slide 18 text

Using MongoDB db.artists.insert( { "name": "Anastacia", "founded": 1999, "albums": [ { "title": "Not That Kind", "published": 2000, "tracks": [ { "trackNumber": 1, "title": "Not That Kind" }, { "trackNumber": 2, "title": "I'm Outta Love" }, { "trackNumber": 3, "title": "Cowboys & Kisses" } ] }, { "title": "Freak Of Nature", "published": 2001, "tracks": [ { "trackNumber": 1, "title": "Paid My Dues"} ] } ] } );

Slide 19

Slide 19 text

Documents /** @ODM\Document() */ class Artist { /** @ODM\Field(type="string") */ private $name; /** @ODM\Field(type="int") */ private $founded; /** @ODM\EmbedMany(targetDocument=Album::class) */ private $albums; }

Slide 20

Slide 20 text

Documents /** @ODM\EmbeddedDocument() */ class Album { /** @ODM\Field(type="string") */ private $title; private $artist; /** @ODM\Field(type="int") */ private $published; /** @ODM\EmbedMany(targetDocument=Tracks::class) */ private $tracks; }

Slide 21

Slide 21 text

Documents /** @ODM\EmbeddedDocument() */ class Track { /** @ODM\Field(type="int") */ private $trackNumber; /** @ODM\Field(type="string") */ private $title; private $album; }

Slide 22

Slide 22 text

Fetching Data db.artists.find({ name: 'Anastacia' });

Slide 23

Slide 23 text

Fetching Single Album db.artists.find( { name: 'Anastacia'}, { albums: { $elemMatch: {'title': 'Not That Kind'} } } );

Slide 24

Slide 24 text

Query Result { "albums": [ { "title": "Freak Of Nature", "published": 2001, "tracks": [ { "trackNumber": 1, "title": "Paid My Dues"} ] } ] }

Slide 25

Slide 25 text

Fetching Single Track db.artists.find( { name: 'Anastacia'}, { albums: { $elemMatch: {'title': 'Not That Kind'} }, 'albums.tracks': { $elemMatch: {'title': 'Not That Kind'} } } );

Slide 26

Slide 26 text

Oops { "ok" : 0, "errmsg" : "Cannot use $elemMatch projection on a nested field.", "code" : 2, "codeName" : "BadValue" }

Slide 27

Slide 27 text

Aggregation { $match: { 'name': 'Anastacia' } }, { $unwind: '$albums' }, { $match: { 'albums.title': 'Not That Kind' } }, { $unwind: '$albums.tracks' }, { $match: { 'albums.tracks.title': 'Not That Kind' } }, db.artists.aggregate([ ]);

Slide 28

Slide 28 text

Query Result { "name": "Anastacia", "founded": 1999, "albums": { "title": "Not That Kind", "published": 2000, "tracks": { "trackNumber": 1, "title": "Not That Kind" } } }

Slide 29

Slide 29 text

JSON Columns CREATE TABLE artists ( id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL, founded INTEGER NOT NULL, albums JSON );

Slide 30

Slide 30 text

Inserting JSON Data INSERT INTO artists (id, name, founded, albums) VALUES ( 1, 'Anastacia', 1999, '[ { "title": "Not That Kind", "published": 2000, "tracks": [ { "trackNumber": 1, "title": "Not That Kind" }, { "trackNumber": 2, "title": "I''m Outta Love" }, { "trackNumber": 3, "title": "Cowboys & Kisses" } ] }, { "title": "Freak Of Nature", "published": 2001, "tracks": [{ "trackNumber": 1, "title": "Paid My Dues"}] } ]' );

Slide 31

Slide 31 text

Fetching Data SELECT artists.name, album.albumTitle, track.* FROM artists, JSON_TABLE( artists.albums, "$[*]" COLUMNS( albumTitle TEXT PATH "$.title", published INT PATH "$.published", tracks JSON PATH "$.tracks" ) ) as album, JSON_TABLE( album.tracks, "$[*]" COLUMNS( trackNumber INT PATH "$.trackNumber", title TEXT PATH "$.title" ) ) as track;

Slide 32

Slide 32 text

Fetching Data SELECT name, album->>'title' albumTitle, track->>'trackNumber' trackNumber, track->>'title' title FROM artists, json_array_elements(albums) album, json_array_elements(album->'tracks') track ORDER BY album->>'published', track->>'trackNumber';

Slide 33

Slide 33 text

Normalising MongoDB { "_id": 1, "name": "Not That Kind”, "artist": { "$ref": "artists", "$id": 1 }, "published": 2000, "tracks": [ { "trackNumber": 1, "title": "Not That Kind" }, { "trackNumber": 2, "title": "I'm Outta Love" }, { "trackNumber": 3, "title": "Cowboys & Kisses" } ] }

Slide 34

Slide 34 text

Normalising MongoDB { "_id": 1, "name": "Anastacia", "founded": 1999, "albums": [ { "$ref": "albums", "$id": 1 }, { "$ref": "albums", "$id": 2 } ] }

Slide 35

Slide 35 text

DBRef Objects • Store collection name and identiﬁer • Work across databases • Don’t check referential integrity (remember MyISAM?) • Don’t work well in aggregation pipelines

Slide 36

Slide 36 text

Alternative References { "_id": 1, "name": "Not That Kind”, "artist_id": 1, "published": 2000, "tracks": [ { "trackNumber": 1, "title": "Not That Kind" }, { "trackNumber": 2, "title": "I'm Outta Love" }, { "trackNumber": 3, "title": "Cowboys & Kisses" } ] }

Slide 37

Slide 37 text

Fetching Single Track artist_id = db.artists.findOne({ name: 'Anastacia' })._id; db.albums.find( { 'artist.$id': artist_id, name: 'Not That Kind' }, { 'tracks': { $elemMatch: {'title': 'Not That Kind'} } } );

Slide 38

Slide 38 text

Denormalising Data { "_id": 1, "title": "Not That Kind", "artist": { "$ref": "artist", "$id": 1, "name": "Anastacia" }, "published": 2000, "tracks": [ { "trackNumber": 1, "title": "Not That Kind" }, { "trackNumber": 2, "title": "I'm Outta Love" }, { "trackNumber": 3, "title": "Cowboys & Kisses" } ] }

Slide 39

Slide 39 text

Fetching Single Track db.albums.find( { 'artist.name': 'Anastacia', name: 'Not That Kind' }, { 'tracks': { $elemMatch: {'title': 'Not That Kind'} } } );

Slide 40

Slide 40 text

Denormalising Data { "_id": 1, "name": "Anastacia", "founded": 1999, "albums": [ { "$ref": "albums", "$id": 1, "title": "Not That Kind" }, { "$ref": "albums", "$id": 2, "title": "Freak Of Nature" } ] }

Slide 41

Slide 41 text

Denormalising Data CREATE TABLE albums ( id INT PRIMARY KEY NOT NULL, title VARCHAR(255) NOT NULL, artist_id INT NOT NULL, artist_name VARCHAR(255) NOT NULL, published INT NOT NULL, FOREIGN KEY fk_artist(artist_id) REFERENCES artists(id) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY fk_artist_name(artist_name) REFERENCES artists(name) ON UPDATE CASCADE ON DELETE CASCADE );

Slide 42

Slide 42 text

Fetching Single Track SELECT * FROM tracks JOIN albums ON tracks.album_id = albums.id WHERE albums.artist_name = 'Anastacia' AND albums.title = 'Not That Kind' AND tracks.title = 'Not That Kind';

Slide 43

Slide 43 text

Composite Types CREATE TYPE artist_ref AS ( id INT, name VARCHAR(255) ); CREATE TABLE albums ( id INT PRIMARY KEY NOT NULL, title VARCHAR(255) NOT NULL, artist artist_ref NOT NULL, published INT NOT NULL );

Slide 44

Slide 44 text

Inserting Data INSERT INTO artists VALUES (1, 'Anastacia', 1999); INSERT INTO albums VALUES (1, 'Not That Kind', ROW (1, 'Anastacia'), 2000); SELECT (artist).name, title FROM albums WHERE (artist).id = 1;

Slide 45

Slide 45 text

Foreign Key Limitations CREATE TABLE albums ( id INT PRIMARY KEY NOT NULL, title VARCHAR(255) NOT NULL, artist artist_ref NOT NULL, published INT NOT NULL, FOREIGN KEY (artist.id) REFERENCES artists(id) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (artist.name) REFERENCES artists(name) ON UPDATE CASCADE ON DELETE CASCADE );

Slide 46

Slide 46 text

Foreign Keys CREATE TABLE artists ( artist artist_ref PRIMARY KEY, founded INT NOT NULL ); CREATE TABLE albums ( id INT PRIMARY KEY NOT NULL, title VARCHAR(255) NOT NULL, artist artist_ref NOT NULL, published INT NOT NULL, FOREIGN KEY (artist) REFERENCES artists(artist) ON UPDATE CASCADE ON DELETE CASCADE );

Slide 47

Slide 47 text

Array Fields CREATE TYPE album_ref AS ( id INT, title VARCHAR(255) ); CREATE TABLE artists ( artist artist_ref PRIMARY KEY, founded INT NOT NULL, albums album_ref[] );

Slide 48

Slide 48 text

Inserting Data INSERT INTO artists VALUES (ROW(1, 'Anastacia'), 1999); INSERT INTO albums VALUES (1, 'Not That Kind', ROW (1, 'Anastacia'), 2000); UPDATE artists SET albums = ARRAY[CAST(ROW(1, 'Not That Kind') AS album_ref)] WHERE (artist).name = 'Anastacia';

Slide 49

Slide 49 text

Modifying Data INSERT INTO albums VALUES (1, 'Freak of Nature', ROW (1, 'Anastacia'), 2001); UPDATE artists SET albums = albums || ARRAY[CAST(ROW(1, 'Freak of Nature') AS album_ref)] WHERE (artist).name = 'Anastacia';

Slide 50

Slide 50 text

Value Objects CREATE TYPE price AS ( currency VARCHAR(3), amount INT ); CREATE TABLE products ( id INT PRIMARY KEY NOT NULL, price price NOT NULL, sale_price price NOT NULL );

Slide 51

Slide 51 text

Array Types CREATE TYPE price AS ( currency VARCHAR(3), amount INT ); CREATE TABLE products ( id INT PRIMARY KEY NOT NULL, price price[] NOT NULL, sale_price price[] NOT NULL ); INSERT INTO products VALUES ( 1, ARRAY[CAST(ROW('EUR', 1499) AS price), CAST(ROW('USD', 1999) AS price)], ARRAY[CAST(ROW('EUR', 999) AS price), CAST(ROW('USD', 1499) AS price)] );

Slide 52

Slide 52 text

Embed vs. Reference • Embed data when it’s owned by another entity • Reference data when it’s needed by itself • Use hybrid references to avoid joins • Beware of write penalties when duplicating data

Slide 53

Slide 53 text

Data Integrity

Slide 54

Slide 54 text

Entity Validation class Price { /** * @var string */ private $currency; /** * @var int * @GreaterThan(0) */ private $price; }

Slide 55

Slide 55 text

Entity Validation public function __invoke(Request $request, EntityManagerInterface $entityManager, PriceForm $form) { $form->handleRequest($request); if (!$form->isSubmitted() || !$form->isValid()) { throw new BadRequestHttpException(); } $price = $form->getData(); assert($price instanceof Price); $entityManager->persist($price); $entityManager->flush(); }

Slide 56

Slide 56 text

Check Constraints CREATE TABLE products ( id INT PRIMARY KEY NOT NULL, price price NOT NULL, sale_price price, CONSTRAINT positive_price CHECK ((price).amount > 0) );

Slide 57

Slide 57 text

Check Constraints CREATE DOMAIN price_amount AS INT CHECK ( VALUE > 0 ); CREATE TYPE price AS ( currency VARCHAR(3), amount price_amount );

Slide 58

Slide 58 text

Check Constraints CREATE TABLE products ( id INT PRIMARY KEY NOT NULL, price price NOT NULL, sale_price price, CONSTRAINT valid_price CHECK ((price).amount > (sale_price).amount), CONSTRAINT same_currency CHECK ((price).currency = (sale_price).currency) );

Slide 59

Slide 59 text

Schema Validation db.artists.createCollection('artists', { validator: { $jsonSchema: { bsonType: 'object', required: [ 'name', 'founded', 'albums' ], properties: { name: { bsonType: 'string' }, ... } } } });

Slide 60

Slide 60 text

Schema Validation db.createCollection('products', { validator: { $and: [ { $jsonSchema: { ... } }, { price: { $gt: 0 } }, { $expr: { $gt: [ '$price.amount', '$salePrice.amount' ] }}, { $or: [ { salePrice: { $exists: false } }, { $expr: { $eq: ['$price.currency', '$salePrice.currency'] }} ]}, ] } });