10 things every dev. should know about rela7onal databases but - PowerPoint PPT Presentation

10 ¡things ¡every ¡dev. ¡should ¡ know ¡about ¡rela7onal ¡databases …but ¡forgot ¡to ¡ask ¡ Piers ¡Williams ¡ @MrPiers ¡

Who ¡He? • So6ware ¡Architect ¡at ¡Birchman ¡(an ¡SMS ¡Company) ¡ • Data-­‑centric ¡.net ¡apps ¡ • BI ¡soluFons ¡on ¡SQL ¡Server ¡ • Build ¡and ¡deployment ¡automaFon ¡ • @MrPiers ¡ • piers7.com ¡

Quick ¡Primer • RelaFonal ¡model ¡actually ¡refers ¡to ¡the ¡data ¡in ¡columns ¡in ¡the ¡same ¡ table ¡ rela%ng ¡to ¡the ¡ key . ¡ • They ¡can ¡also ¡have ¡relaFon ships ¡to ¡other ¡tables ¡of ¡course ¡ • Keys ¡generally ¡system ¡generated ¡to ¡ensure ¡ unique ¡and ¡ immutable ¡ • IncremenFng ¡integers ¡ • Query ¡using ¡ S tructured ¡ Q uery ¡ L anguage ¡(SQL) ¡ • select ¡sum(saleprice) ¡from ¡sales ¡where ¡storeId ¡= ¡21 ¡

SQL ¡Is ¡A ¡Declara7ve ¡Language • Can ¡this ¡query ¡fail? ¡

SQL ¡Is ¡A ¡Declara7ve ¡Language • Can ¡this ¡query ¡fail? ¡ • Yes ¡ • This ¡one ¡too ¡

SQL ¡Is ¡A ¡Declara7ve ¡Language • You ¡tell ¡database ¡ what ¡you ¡want , ¡ not ¡ what ¡to ¡do ¡ • Logical ¡ order ¡of ¡query ¡parts ¡is ¡defined ¡ • Actual ¡ execuFon ¡order ¡can ¡vary ¡ • If ¡you’re ¡tweaking ¡query ¡to ¡force ¡a ¡parFcular ¡order-­‑of-­‑execuFon, ¡ you’re ¡doing ¡it ¡wrong ¡ • Step ¡away ¡from ¡the ¡keyboard ¡

Design

Normaliza7on ¡is ¡a ¡dry ¡subject • Aim ¡for ¡3 rd ¡normal ¡form ¡(3NF) ¡to ¡ensure ¡data ¡integrity ¡ • The ¡key ¡(1NF) ¡ • The ¡whole ¡key ¡(2NF) ¡ • Nothing ¡but ¡the ¡key ¡(3NF) ¡

Normaliza7on ¡Example ¡– ¡Book ¡Library Title ¡ Author ¡ Borrower ¡ ContactEmail ¡ Public ¡Key ¡ Cryptosystems ¡ Alice ¡Key ¡ Robert ¡Plaintext ¡ bob@gmail.com ¡ Lonely ¡Planet ¡Russia ¡ Simon ¡Richmond ¡ Edward ¡Snowden ¡ guest@kremlin.ru ¡ Devilish ¡Hacks ¡ Louis ¡Cypher ¡ Edward ¡Snowden ¡ guest@kremlin.ru ¡ Title ¡ Author ¡ Borrower ¡ Public ¡Key ¡Crypto ¡Systems ¡ Alice ¡Key ¡ Robert ¡Plaintext ¡ Borrower ¡ ContactEmail ¡ Lonely ¡Planet ¡Russia ¡ Lonely ¡Planet ¡ Edward ¡Snowden ¡ Robert ¡Plaintext ¡ bob@worried.com ¡ Devilish ¡Hacks ¡ Louis ¡Cypher ¡ Edward ¡Snowden ¡ Edward ¡Snowden ¡ guest@kremlin.ru ¡

Normaliza7on ¡is ¡a ¡DRY ¡subject • Look ¡to ¡avoid ¡duplicated ¡data ¡in ¡your ¡schemas ¡-­‑ ¡ DRY ¡ • If ¡data ¡is ¡duplicated, ¡database ¡ structure ¡ cannot ¡not ¡enforce ¡consistency ¡ during ¡updates ¡ • Introduce ¡denormalizaFon ¡ a6er, ¡ where ¡needed ¡for ¡performance ¡ • You ¡take ¡the ¡burden ¡of ¡consistency ¡

Aside: ¡Avoid ¡EAV ¡an7-­‑paRern ID ¡ BookID ¡ Name ¡ Value ¡ 1 ¡ 1 ¡ Title ¡ Public ¡Key ¡Cryptosystems ¡ 2 ¡ 1 ¡ Author ¡ Alice ¡Key ¡ 3 ¡ 1 ¡ ISBN ¡ 47381749023 ¡ 4 ¡ 1 ¡ ReleaseDate ¡ 20/01/2001 ¡ 5 ¡ 2 ¡ Title ¡ Lonely ¡Planet ¡Russia ¡ 6 ¡ 2 ¡ Author ¡ Simon ¡Richmond ¡ 7 ¡ 2 ¡ ISBN ¡ 6543215432 ¡ 8 ¡ 2 ¡ ReleaseDate ¡ 1/04/2010 ¡

Storage ¡Choices ¡MaRer • Your ¡ storage ¡choices ¡determine ¡how ¡the ¡SQL ¡can ¡be ¡executed ¡ • Table ¡scan ¡ • Table/Index ¡ seek ¡ • Order ¡of ¡columns ¡important ¡in ¡indexes ¡ • Phone ¡book ¡= ¡clustered ¡index ¡on ¡Surname, ¡First ¡Name ¡ • Yellow ¡Pages ¡= ¡ covering ¡index ¡

Corollary ¡-­‑ ¡Not ¡All ¡Queries ¡Are ¡Equal • Some ¡queries ¡can’t ¡take ¡advantage ¡of ¡indexes ¡ • WHERE ¡func(column) ¡= ¡@someparameter ¡ • LIKE ¡‘%suffix’ ¡ • WHERE ¡firstName ¡= ¡@firstName ¡ • When ¡you ¡indexed ¡LastName,FirstName ¡

Not ¡All ¡Queries ¡Are ¡Equal ¡(cont.) ¡ Problems ¡with ¡Search • Need ¡to ¡vary ¡what ¡SQL ¡gets ¡issued ¡ • If ¡your ¡ORM ¡supports ¡dynamically ¡adding ¡filter ¡predicates, ¡do ¡that ¡ • Otherwise ¡add ¡ parameterized ¡SQL ¡dynamically ¡

BeRer ¡Concurrency ¡Through ¡Design • OpFmisFc ¡concurrency ¡violaFons ¡occur ¡when ¡mulFple ¡actors ¡perform ¡ simultaneous ¡updates ¡on ¡a ¡row ¡ • Hard ¡to ¡handle, ¡ so ¡don’t ¡ • Data ¡that ¡can ¡legiFmately ¡be ¡updated ¡in ¡parallel ¡should ¡be ¡ stored ¡ separately ¡ • ParFFon ¡over ¡mulFple ¡tables ¡ • Each ¡has ¡own ¡concurrency ¡indicator ¡(rowversion) ¡

Transac7ons

ACID • A tomicity ¡ • C onsistency ¡ • I solaFon ¡ • D urability ¡

What ¡Happens ¡When ¡You ¡Drop ¡ACID • Concurrency ¡= ¡race ¡condiFons ¡= ¡inconsistent ¡data* ¡

Isola7on ¡= ¡Thread ¡Safety #Fail ¡ #Win ¡

ACID ¡not ¡a ¡panacea “A ¡customer, ¡whose ¡ID ¡is ¡101, ¡contacted ¡his ¡local ¡shop ¡to ¡get ¡his ¡phone ¡recharged. ¡He ¡pays ¡ the ¡amount. ¡But ¡Fll ¡the ¡Fme ¡his ¡phone ¡was ¡about ¡to ¡recharge, ¡the ¡scheduled ¡Fme ¡of ¡the ¡ second ¡script ¡fired ¡the ¡second ¡script. ¡The ¡second ¡script ¡loaded ¡the ¡records ¡of ¡50,000 ¡ customers ¡in ¡the ¡memory. ¡In ¡this ¡in-­‑memory ¡records, ¡one ¡of ¡the ¡record ¡of ¡this ¡customer ¡too. ¡ […] ¡ Now ¡what ¡happened ¡is ¡that ¡is ¡the ¡actual ¡table, ¡the ¡column: ¡"CurrentAccountBalance" ¡gets ¡ updated ¡to ¡150, ¡ but ¡the ¡in-­‑memory ¡records ¡on ¡which ¡the ¡second ¡script ¡was ¡working ¡had ¡ the ¡customer's ¡old ¡balance ¡i.e, ¡100 ” ¡ hrp://stackoverflow.com/quesFons/4071763/is-­‑there-­‑any-­‑way-­‑to-­‑update-­‑the-­‑most-­‑recent-­‑ version-­‑of-­‑row-­‑through-­‑sql ¡ ¡ ¡

Know ¡Your ¡Isola7on ¡– ¡ANSI ¡levels • Read ¡Uncommired ¡/ ¡nolock ¡/ ¡Dirty ¡Read ¡ • What ¡you ¡get ¡is ¡what ¡you ¡get ¡ • Read ¡Commired ¡(default) ¡ • Anything ¡can ¡change ¡right ¡a6er ¡you ¡looked ¡at ¡it ¡(non-­‑repeatable, ¡phantoms) ¡ • Some ¡consistency ¡issues ¡can ¡be ¡observed ¡ while ¡ reading ¡a ¡table ¡ • Repeatable ¡Read ¡ • Rows ¡you’ve ¡seen ¡are ¡locked ¡(hence ¡repeatable) ¡ • Doesn’t ¡mean ¡more ¡can’t ¡be ¡added ¡(‘phantoms ¡reads’), ¡so ¡queries ¡not ¡ really ¡ repeatable, ¡and ¡totals ¡can ¡change ¡ • Serializable ¡ • Rows ¡and ¡ranges ¡locked ¡once ¡looked ¡at ¡ • This ¡is ¡what ¡you ¡thought ¡you ¡were ¡geung ¡all ¡along ¡

Know ¡Your ¡Isola7on • Different ¡isolaFon ¡levels ¡available ¡(ANSI ¡model) ¡ Best ¡Concurrency ¡ Best ¡Consistency ¡ Read ¡ Read ¡ Repeatable ¡ Serializable ¡ (boringly ¡sane) ¡ Uncommired ¡ Commired ¡ Read ¡ (unhinged) ¡ (occasional ¡outbursts) ¡ (maximum ¡crazy) ¡

Know ¡Your ¡Isola7on ¡– ¡BoRom ¡Line • Simple ¡updates ¡safe ¡at ¡READ ¡COMMITTED ¡(as ¡atomic) ¡ • UPDATE ¡Credit ¡set ¡creditLimit ¡= ¡creditLimit ¡+ ¡100 ¡where ¡id=x ¡ • When ¡you ¡ need ¡ mulF-­‑statement ¡consistency, ¡use ¡SERIALIZABLE ¡ • Avoids ¡race ¡condiFons ¡when ¡updaFng ¡one ¡piece ¡of ¡data ¡based ¡on ¡another ¡ • Where ¡available, ¡ read-­‑consistent ¡ (snapshot) ¡approaches ¡cause ¡least ¡ weirdness ¡for ¡readers ¡ • Standard ¡behavior ¡for ¡Oracle ¡ • SET ¡READ_COMMITTED_SNAPSHOT ¡ON ¡for ¡SQL ¡Server ¡2005+ ¡ • NB: ¡change ¡from ¡pessimisFc ¡to ¡opFmisFc ¡concurrency ¡