Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Denormalise Your Database - Schema Design for Modern Database Systems

7ea0eec719c20e8d7880bfdb35f78b4e?s=47 alcaeus
September 27, 2019

Denormalise Your Database - Schema Design for Modern Database Systems

For years we have designed our databases around the rules of database normalisation. Modern database systems offer more than just scalar fields, and it’s time we start using them: we’ll take a look at how to leverage complex data types in relational databases. We’re also going to explore document databases like MongoDB to better design our schema while putting our data first instead of our database.

7ea0eec719c20e8d7880bfdb35f78b4e?s=128

alcaeus

September 27, 2019
Tweet

Transcript

  1. Denormalise Your Database Schema design for modern database systems

  2. None
  3. Database Examples MongoDB PostgreSQL MySQL MS Access

  4. Normalisation

  5. 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 }
  6. 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
  7. 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
  8. 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
  9. 3 NF: Artist artist_id artist founded 1 Anastacia 1999 2

    Pink Floyd 1965
  10. 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
  11. 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; }
  12. 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; }
  13. 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; }
  14. 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;
  15. Fetching Single Album SELECT * FROM albums JOIN artists ON

    albums.artist_id = artists.id WHERE albums.title = 'Freak Of Nature’ AND artists.name = 'Anastacia';
  16. 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';
  17. Let’s Try Something New

  18. 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"} ] } ] } );
  19. Documents /** @ODM\Document() */ class Artist { /** @ODM\Field(type="string") */

    private $name; /** @ODM\Field(type="int") */ private $founded; /** @ODM\EmbedMany(targetDocument=Album::class) */ private $albums; }
  20. 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; }
  21. Documents /** @ODM\EmbeddedDocument() */ class Track { /** @ODM\Field(type="int") */

    private $trackNumber; /** @ODM\Field(type="string") */ private $title; private $album; }
  22. Fetching Data db.artists.find({ name: 'Anastacia' });

  23. Fetching Single Album db.artists.find( { name: 'Anastacia'}, { albums: {

    $elemMatch: {'title': 'Not That Kind'} } } );
  24. Query Result { "albums": [ { "title": "Freak Of Nature",

    "published": 2001, "tracks": [ { "trackNumber": 1, "title": "Paid My Dues"} ] } ] }
  25. Fetching Single Track db.artists.find( { name: 'Anastacia'}, { albums: {

    $elemMatch: {'title': 'Not That Kind'} }, 'albums.tracks': { $elemMatch: {'title': 'Not That Kind'} } } );
  26. Oops { "ok" : 0, "errmsg" : "Cannot use $elemMatch

    projection on a nested field.", "code" : 2, "codeName" : "BadValue" }
  27. 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([ ]);
  28. Query Result { "name": "Anastacia", "founded": 1999, "albums": { "title":

    "Not That Kind", "published": 2000, "tracks": { "trackNumber": 1, "title": "Not That Kind" } } }
  29. JSON Columns CREATE TABLE artists ( id INTEGER NOT NULL

    PRIMARY KEY, name TEXT NOT NULL, founded INTEGER NOT NULL, albums JSON );
  30. 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"}] } ]' );
  31. 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;
  32. 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';
  33. 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" } ] }
  34. Normalising MongoDB { "_id": 1, "name": "Anastacia", "founded": 1999, "albums":

    [ { "$ref": "albums", "$id": 1 }, { "$ref": "albums", "$id": 2 } ] }
  35. DBRef Objects • Store collection name and identifier • Work

    across databases • Don’t check referential integrity (remember MyISAM?) • Don’t work well in aggregation pipelines
  36. 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" } ] }
  37. 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'} } } );
  38. 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" } ] }
  39. Fetching Single Track db.albums.find( { 'artist.name': 'Anastacia', name: 'Not That

    Kind' }, { 'tracks': { $elemMatch: {'title': 'Not That Kind'} } } );
  40. 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" } ] }
  41. 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 );
  42. 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';
  43. 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 );
  44. 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;
  45. 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 );
  46. 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 );
  47. 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[] );
  48. 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';
  49. 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';
  50. 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 );
  51. 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)] );
  52. 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
  53. Data Integrity

  54. Entity Validation class Price { /** * @var string */

    private $currency; /** * @var int * @GreaterThan(0) */ private $price; }
  55. 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(); }
  56. 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) );
  57. Check Constraints CREATE DOMAIN price_amount AS INT CHECK ( VALUE

    > 0 ); CREATE TYPE price AS ( currency VARCHAR(3), amount price_amount );
  58. 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) );
  59. Schema Validation db.artists.createCollection('artists', { validator: { $jsonSchema: { bsonType: 'object',

    required: [ 'name', 'founded', 'albums' ], properties: { name: { bsonType: 'string' }, ... } } } });
  60. 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'] }} ]}, ] } });
  61. https://joind.in/talk/396dc

  62. Thanks! @alcaeus github.com/alcaeus symfony-devs: @alcaeus