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 ¡relaFonships ¡to ¡other ¡tables ¡of ¡course ¡
• Keys ¡generally ¡system ¡generated ¡to ¡ensure ¡unique ¡and ¡immutable ¡
• IncremenFng ¡integers ¡
• Query ¡using ¡Structured ¡Query ¡Language ¡(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 ¡3rd ¡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 ¡ Lonely ¡Planet ¡Russia ¡ Lonely ¡Planet ¡ Edward ¡Snowden ¡ Devilish ¡Hacks ¡ Louis ¡Cypher ¡ Edward ¡Snowden ¡ Borrower ¡ ContactEmail ¡ Robert ¡Plaintext ¡ bob@worried.com ¡ 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

• Atomicity ¡
• Consistency ¡
• IsolaFon ¡
• Durability ¡

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 ¡ Uncommired ¡

(maximum ¡crazy) ¡

Read ¡ Commired ¡

(unhinged) ¡

Repeatable ¡ Read ¡

(occasional ¡outbursts) ¡

Serializable ¡

(boringly ¡sane) ¡

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 ¡

ACID ¡has ¡a ¡(hidden) ¡cost

• Log ¡has ¡to ¡record ¡all ¡changes, ¡to ¡disk, ¡right ¡then ¡
• Actual ¡data ¡pages ¡may ¡be ¡wriren ¡to ¡mulFple ¡Fmes ¡in ¡memory, ¡but ¡only ¡hit ¡

the ¡disk ¡once, ¡someFme ¡later ¡

• Log ¡writes ¡can ¡be ¡order-­‑of-­‑magnitude ¡more ¡than ¡data ¡writes ¡
• Indexes ¡make ¡this ¡even ¡worse: ¡300MB ¡-­‑> ¡4GB ¡
• ImperaNve ¡to ¡use ¡minimally ¡logged ¡operaFons ¡for ¡large ¡data ¡inserts ¡
• Bulk ¡load ¡
• Some ¡internal ¡data ¡movement ¡can ¡be ¡minimally ¡logged ¡under ¡right ¡

condiFons ¡

Queries

Use ¡set-­‑based ¡opera7ons

Use ¡set-­‑based ¡opera7ons ¡… ¡ex except ¡ ¡when ¡ ¡you ¡ ¡ sho shouldn’t uldn’t

• CalculaFons ¡based ¡on ¡a ¡row’s ¡posiFon ¡in ¡the ¡sequence: ¡
• Total-­‑to-­‑date ¡
• Rolling ¡averages ¡
• Carry-­‑forward ¡balances ¡

Nulls ¡are ¡just ¡plain ¡weird

• They ¡don’t ¡work ¡like ¡curly-­‑bracket ¡

languages ¡

• Makes ¡comparisons ¡of ¡nullable ¡

columns ¡painful ¡

• They ¡are ¡excluded ¡from ¡

aggregates ¡(eg ¡averages) ¡

…slowly

• Refactoring ¡schema ¡is ¡harder ¡than ¡code ¡
• Not ¡changing ¡at ¡all ¡is ¡just ¡as ¡bad ¡

Plan ¡to ¡refactor

Protect ¡the ¡Data

• Plan ¡upfront ¡your ¡approach ¡for: ¡
• SQL ¡InjecNon ¡(sFll ¡OWASP ¡#1) ¡
• Data ¡Integrity ¡
• Growth ¡
• Disaster ¡Recovery ¡
• Remember: ¡data ¡is ¡valuable ¡
• Gains ¡to ¡others ¡if ¡they ¡get ¡it ¡
• Loss ¡to ¡you ¡if ¡you ¡lose ¡/ ¡corrupt ¡it ¡

Avoid ¡Off-­‑by-­‑One ¡Errors

• BETWEEN ¡is ¡end-­‑inclusive. ¡ ¡
• Not ¡normally ¡what ¡you ¡want ¡
• Prefer: ¡WHERE ¡x ¡>= ¡start ¡AND ¡x ¡< ¡end ¡
• Be ¡very ¡wary ¡of ¡implicit ¡conversions ¡with ¡Dates ¡
• DateFme ¡-­‑> ¡Date ¡
• DateFme/offset ¡-­‑> ¡DateFme ¡
• More ¡like ¡off-­‑by-­‑UTC-­‑offset ¡than ¡off-­‑by-­‑1 ¡

10 ¡Things…

• 1. SQL ¡is ¡declaraFve ¡
• 2. NormalizaFon ¡= ¡DRY ¡
• 3. Storage ¡choices ¡marer ¡
• 4. Not ¡all ¡queries ¡are ¡equal ¡
• 5. Use ¡set-­‑based ¡operaFons ¡... ¡mostly ¡
• 6. Nulls ¡are ¡just ¡plain ¡weird ¡
• 7. Know ¡your ¡isolaFon ¡
• 8. Reduce ¡concurrency ¡contenFon ¡through ¡table ¡design ¡
• 9. Plan ¡to ¡refactor ¡… ¡slowly ¡
• 10. Protect ¡the ¡data ¡

Avoid ¡History

• Storing ¡historical ¡versions ¡of ¡rows ¡in-­‑line ¡is ¡problemaFc ¡
• Weakens ¡integrity ¡constraints ¡
• Can ¡make ¡joins ¡extremely ¡painful ¡
• Reduced ¡performance ¡due ¡to ¡table ¡bloat ¡
• OpFons ¡
• Store ¡history ¡in ¡separate ¡tables ¡
• Store ¡events, ¡not ¡historical ¡state ¡
• Store ¡history ¡in ¡separate ¡(OLAP) ¡database ¡
• Use ¡a ¡database ¡system ¡that ¡supports ¡point-­‑in-­‑Fme ¡state ¡naFvely ¡

Special ¡Notes ¡for ¡ORMs

• Chances ¡are ¡the ¡SQL ¡they ¡spit ¡out ¡is ¡great ¡
• …for ¡what ¡you ¡asked ¡for. ¡
• Avoid ¡
• Lazy ¡loads ¡
• Really ¡deep ¡trees ¡
• Loads ¡of ¡varying ¡‘depth’ ¡onto ¡the ¡same ¡object ¡type ¡
• Navigable ¡reverse ¡associaFons ¡= ¡category ¡searches ¡
• Must ¡index ¡your ¡foreign ¡keys ¡

Data ¡Types ¡MaRer

• Align ¡storage ¡types ¡to ¡those ¡in ¡your ¡applicaFon ¡where ¡possible ¡
• Eliminates ¡whole ¡category ¡of ¡runFme ¡errors ¡
• Use ¡constraints ¡if ¡necessary ¡(e.g. ¡Oracle) ¡
• Don’t ¡store ¡external ¡idenFfiers ¡as ¡numbers ¡
• If ¡you ¡don’t ¡control ¡the ¡rules, ¡be ¡conservaFve ¡

Step ¡away ¡from ¡the ¡keyboard

• The ¡database ¡is ¡not ¡slow ¡ ¡
• You ¡know ¡when ¡people ¡claim ¡the ¡compiler ¡has ¡a ¡bug… ¡
• If ¡something ¡seems ¡hard, ¡don’t ¡do ¡it ¡

When ¡To ¡Use?

RDBMS ¡

• Large ¡scale ¡structured ¡data ¡
• Consistency ¡is ¡important/criFcal ¡
• Ad-­‑hoc/aggregate ¡reporFng ¡

requirements ¡

Something ¡else ¡

• Massive ¡scale, ¡unstructured ¡data ¡
• Consistency ¡less ¡important ¡
• Lirle ¡need ¡to ¡look ¡at ¡the ¡data ¡
• n-­‑aggregate ¡
• Hierarchical ¡(graph) ¡data ¡