5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
BusinessFaxSolutionsSend&[Link],SOX&GLBCompliant.
DavidMcCaldin
Follow
MScStudent/PenetrationTester/MedicalDevices/CSV
Tutorial:StepbyStepDatabaseDesigninSQL
Feb22,2015
23,195views
67Likes
7Comments
Pleasecheckoutmyrelatedarticle"Howdidthemodernrelationaldatabase
cometobe?"whichiscurrentlytrendinginBigDataandfollowmefordaily
articlesontechnology,digitalmarketing,psychologyandpharmaceuticals.
DatabaseDesignandImplementationisapplicableforwhateverindustry
[Link]
databaseinyourorganisation,usingspecificdatafromasweetshop
[Link]&Information
[Link],youwillknowaboutdatabases,
advantagesofdatabasessystemoverregularfilesystem,thestepsofa
databasedesignprocess,softwaredevelopmentlifecycle,qualitiesofa
wellbuiltdatabase,relationsandrelationships,dataintegrity,andmore.
Databasesareusedineveryindustry,includingthepharmaceutical
industry.
BackgroundofDatabases
Thedatabasesystemapproachtodatamanagementovercomesmanyofthe
[Link]
[Link]
isthatalthoughthedatamaybespreadacrossmultiplephysicalfiles,the
[Link]
1/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
[Link]
datainsinglelogicalrepositoryallowsforeasymanipulationandqueryingofthe
data,incontrastwithtraditionalfilesystemswheretheprogrammermustspecify
whatandhowthedataretrievalisdone.
Withdatabasesystems,itneedonlybespecifiedwhatmustbedone,theDBMS
(DatabaseManagementSystem)[Link]
approachisthat,becausedataislocatedinonesingledatabase,dataindifferent
[Link]
[Link]
[Link],
errorscanhappenifoneinstanceofthedataisalteredandanotherinstance
[Link],moremaintenanceand
systemresourcesarerequiredtoensurethatdataisalwaysintegral.
Oneofthegreatestbenefitsofdatabasesisthatdatacanbesharedorsecured
[Link]
thedataismanagedbecausethedataallresidesinonedatabase.
Ifthereareshortcomingstodatabasesystems,itsthatmuchmorepowerfuland
sophisticatedsoftwareisneededtocontrolthedatabaseanddesigningthe
[Link]
knowledgeofhowtousethedatabaseisrequired,thusmakingthedatabase
[Link]
logicalrepository,evenasmallerrorcandamagetheentiredatabaseandreduce
[Link]
complete,integral,simple,understandable,[Link]
alsaysthatdatabasemodellingstrivesforanonredundant,unified
[Link]
softwaredevelopmentlifecyclemethodology,andbyusingthedatamodels,the
databasedesignidealsarefulfilledandwillminimizethedisadvantages.
Databases&theSoftwareDevelopmentLifecycle
Thestepsindevelopinganyapplicationcanberepresentedasalinearsequence
whereeachstepinthesequenceisafunction,whichpassesitsoutputtoits
[Link]
software,whichiscomplete,efficient,usable,consistent,correctand
[Link]
[Link]
[Link]
[Link]
2/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
summarizedasfollows:
Requirementsspecification>Analysis>Conceptualdesign>
ImplementationDesign>PhysicalSchemaDesignandOptimisation
Inconsultationwithallpotentialusersofthedatabase,adatabasedesignersfirst
[Link]
containsaconciseandnontechnicalsummaryofwhatdataitemswillbestored
inthedatabase,[Link]
datarequirementsdocument,furtheranalysisisdonetogivemeaningtothe
dataitems,[Link]
[Link]
[Link],thedatabasedesignermodels
howtheinformationisviewedbythedatabasesystemandishowitisprocessed
[Link],the
conceptualdesignistranslatedintoamorelowlevel,DBMSspecificdesign.
DataModels&SchemasasaMeansofCapturingData
Thedatabasedevelopmentdesignphasesbringsuptheconceptofdatamodels.
Datamodelsarediagramsorschemas,whichareusedtopresentthedata
[Link]
DevelopmentLifeCycleistodrawuparequirementsdocument.
Figure1:Abasicexampleofarequirementsdocument
Therequirementsdocumentcanthenbeanalysedandturnedintoabasicdataset
(asshowninFigure2)[Link]
resultoftheconceptualdesignphaseisaconceptualdatamodel(Figure3),which
provideslittleinformationofhowthedatabasesystemwilleventuallybe
[Link]
databasesystem.
[Link]
3/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
Figure2:ADatabaseDataSetistheResultofanalyzingtheInformationfrom
[Link].
Figure3:ANormalizedEntityRelationshipmodel(ERD)inCrowsFoot
NotationisanExampleofaConceptualDataModelandprovidesno
informationofhowthedatabasesystemwilleventuallybeimplemented
Intheimplementationdesignphase,theconceptualdatamodelistranslatedinto
[Link]
thelogicalfunctioningandstructureofthedatabaseanddescribeshowthe
dataisstored([Link],whatconstraintsareapplied)butisnot
[Link],
whichmustbetranslatedtoaphysicaldesign.
[Link]
4/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
Figure4:Intheimplementationdesignphase,theconceptualdatamodel(ERD)
istranslatedintoalogicalrepresentation(logicalschema)ofthedatabase
system:adatadictionary.
Physicalmodellingdealswiththerepresentationalaspectsandtheoperational
aspectsofthedatabase,[Link]
andhowtheDBMSinteractswiththedata,[Link]
translationfromlogicaldesigntophysicaldesignassignsfunctionstoboththe
machine(theDBMS)andtotheuser,functionssuchasstorageandsecurity,and
additionalaspectssuchasconsistency(ofdata)andlearnabilityaredealtwithin
thephysicalmodel/[Link],aphysicalschemaistheSQL
codeusedtobuildthedatabase.
Onebenchmarkofagooddatabaseisone,whichiscomplete,integral,simple,
understandable,[Link]
nonredundant,[Link]
followingtheabovemethodology,andbyusingthedatamodels,thesedatabase
[Link],herearetwoexamplesofwhyusingdata
modelsisparamounttocapturingandconveyingdatarequirementsofthe
informationsystem:
1. Bydrawingupalogicalmodel,extradataitemscanbeaddedmoreeasilyin
[Link]
easilyaccordingtoneedsofthecompanyisimportant,becauseitensuresthe
finaldatabasesystemiscompleteanduptodate.
2. [Link]
model,boththedesignerandtheorganizationareabletounderstandthe
[Link]
conceptualmodel,theorganizationwouldnotbeabletoconceptualizethe
[Link]
5/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
databasedesignandmakesurethatitactuallyrepresentsallthedata
requirementsoftheorganization.
3. Bycreatingaphysicalmodel,thedesignerscanhavealowleveloverviewof
howthedatabasesystemwouldoperatebeforeitisactuallyimplemented.
SQLStatementsImplementingtheDatabase
Thefinalstepistophysicallyimplementthelogicaldesignwhichwasillustrated
[Link],[Link]
themainstepsinimplementingthedatabase:
[Link]
ThetablescomedirectlyfromtheinformationcontainedintheDataDictionary.
Thefollowingblocksofcodeeachrepresentarowinthedatadictionaryandare
[Link]
allthedataitems(COMPANY,SUPPLIER,PURCHASES,EMPLOYEEetc),their
attributes(names,ages,costs,numbersandotherdetails),theRelationships
betweenthedataitems,[Link]
isalreadydetailedintheDataDictionary,butnowweareactuallyconvertingit
andimplementingitinaphysicaldatabasesystem.
[Link]
6/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
Explanation:
1. Thecreatetablestatementindicatesthatyouwantatabletobecreated.
WhatisLinkedIn?
JoinToday
SignIn
2. Thenameofthetableproceedsthefirst'('
Pulse
3. ThetableattributesandDataIntegrityRulesaredefinedwithinthetwo
[Link]
Tutorial:StepbyStepDatabase
DesigninSQL
DavidMcCaldin
twoparentheses([Link])
4. Thenotnullstatementmeansthatifyoutrytopopulatethetablewith
values,butleavethevalueofthatattributeempty,youwillgetanerror.
Googledodgesa$9billion
bulletTheAshleyMadison
files:adulterersarebadboys
JohnCAbell
business,tooandmorenews
5. Thevarchar2(19)meansastringof19characters.
6. Thenumber(6,2)meansanumberwhichcanhaveanumberofupto6
digits,2ofthembeingafterthedecimalplace,[Link]
Facebookandothersthink
messagingasaplatformisthe
[Link]?
from0.0to1234.56.
7. Thedatemeansthatthatattributewillberepresentedasadatewithinthe
ChrisMoore
[Link]
7/47
5/27/2016
HowTechnologyHijacks
PeoplesMindsfroma
MagicianandGooglesDesig
TristanHarris
Ethicist
Tutorial:StepbyStepDatabaseDesigninSQL
databasesystem.
8. [Link]
statementwillbeusedtodescribewhichoftheattributesareprimarykeys
andwhich(ifany)oftheattributesareforeignkeys(referencinganother
CEOpayisn'tslowing
Investorscan'tgetenoughof
now$18billionSnapchat,an
IsabelleRoughol
morenews
HiringManagers:StopInsulting
theJobCandidates
BriandeHaaff
table).
9. TheCONSTRAINTstatementisintheformCONSTRAINTxxxPRIMARY
KEY(name_of_attribute_that_you_want_as_the_primary_key)or
CONSTRAINTyyyFOREIGN
KEY(name_of_attribute_that_references_another_table)REFERENCES
hhh(name_of_attribute_that_references_another_table)wherethevalues
ofxxxandyyyarejustarbitrarilymadeupnameswhicharenotimportant.
HeresWhyYoureWrongTo
ThrowShadeAtTheTSAAnd
TheirFiredExecutive
hhhisthenameofthebasetablebeingreferenced.
[Link]
UseSQLstatementstopopulateeachtablewithspecificdata(suchasemployee
names,ages,wagesetc).
[Link].
WriteSQLstatementstoobtaininformationandknowledgeaboutthecompany,
[Link],totalprofitetc.
Keys&DataIntegrityRules
[Link]
implicitlyorexplicitlydefinethesetofconsistentdatabasestate(s).So,integrity
rulesensurethatdatabasestatesandchangesofstateconfirmtospecifiedrules.
Dataintegrityrulesareoftwotypes:EntityintegrityrulesandReferential
integrityrules..
Howdokeysrelatetoensuringthatchangesindatabasestatesconfirmto
specifiedrules?
Well,forexample,youcouldensurethattheprimarykeyofanentitycannotbe
[Link]
benull,thentherewouldbenowayofensuringthatindividualentitieswere
[Link]
identifiablethenyoucantensurethatthedatabaseisintegral,whichisacore
[Link],byensuringthatkeysfollow
certainrules,youcanensureintegrityofdata.
[Link]
8/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
Anotherwayofenforcingintegrityofdataviakeys,istoensurethat,iftwotables
arerelatedtoeachother,anattributeofonerelationmustbethesameasthe
primaryattribute(primarykey)[Link]
referentialintegrityofdata.
So,wedoneedintegrityrules,andproperdefiningofkeysareameansof
enforcingthem.
Relationships
Wheninitiallyexplainingtherelationalmodel,[Link]
shouldbeabstractedfromtheinternalrepresentationofthedata,suchthatifthe
internalrepresentationofthatdataweretochange([Link]
growth),[Link]
iswhyheproposedthatusersshouldonlyinteractwithacollectionoftime
[Link]
togetherwithitsdomain([Link],andthe
employeesareownedbythedepartment)ratherthantherelation(table)itself.
Inusingthetermsrelationandtableassynonyms,Coddmusthaveimplied
thatatableshouldbeviewedintermsofitsrelationshipwithothertables.
Relationshipsarewhatbindtherelations/tablesinadatabasetogether,soproper
understandingisneeded.
Incorrectunderstandingofrelationshipsmayleadtoincorrectlydefined
[Link]
couldleadtodatanotbeingupdatedcorrectlyinsometables,orcouldcausea
[Link]
incompleteinformationinthedatabase,whichinturnresultsinincomplete
knowledge.
Databases, SoftwareDevelopment, DataAnalysis
FeaturedInBigData
Writtenby
DavidMcCaldin
Like
Comment
Follow
67likes 7comments
Addyourcomment
[Link]
9/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
Popular
HudaNaiemMohamedOsman
Interpreter:LanguageResources,TaxReturns'Preparer,
Programmer/DeveloperofDatabase/System
DavidMcCaldin:)Iamspeechlesstowardsyourconisecourseon
[Link]
[Link]
[Link]
essentialandwillfollownaturally,[Link]
relevantprogramcodesandquerieswillneedlearning,butallthesewill
leadyounowhereifyoudonotknowhowtoinitiateyourdatasystemand
warehouseTOWORKONwiththelattercoding,queriesandreporting.
Thankyouonbehalfofmypeerreadersandonbehalfofmyselfforthe
treasuredinput.
Like (3)
Reply
February24,2015
HamzaTariq,DavidMcCaldin,andOLAYEMIOYEWALE
ShowMore
JohnCAbell
Follow
ManagingEditor,NewsatLinkedInExReutersExWired
Googledodgesa$9billionbulletTheAshley
Madisonfiles:adulterersarebadboysinbusiness,
tooandmorenews
May26,2016
59,720views
494Likes
17Comments
FacebookandMicrosoftarebuildingwhatwillbe"thehighest
capacitysubseacabletoevercrosstheAtlantic."Therearehundreds,for
[Link]
10/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
therecord,butdemandkeepsgrowing.ThisoneshouldbedonebyOctober2017,
[Link],not
telecommunicationintermediariestwoyearsagoAlphabetannouncedaPacific
cableprojectwithfiveAsiabasedtelecomcompanies.
WIRED
Follow
@WIRED
SoFacebookandMicrosoftaregoingtobuildagiantundersea
cableacrosstheAtlanticoh,andit's160TB/[Link]/249ApCV
1:08PM26May2016
FacebookandMicrosoftAreLayingaGiantCableAcrossth
Internetgiantsarestartingtobuildenormousnetworksoftheirown,
takingovertheroletraditionallyplayedbytelecomcompanies.
[Link]
262
176
Moreevidencethe2017iPhonewillhavea(mostly)[Link]
chairmanof"longtimeiPhonechassismakerCatcherTechnology"toldhis
annualmeetingit'sasurething,[Link]
steelcase,whichwethinkcoulddouble(uhoh)asanantenna.
Googleprevailedina$9billionlawsuitbroughtOracleallegingthe
[Link]
11/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
[Link]
casehasbouncedaroundvariouscourtsforyears(theSupremeCourtdeclinedto
hearthefederalone),andOraclesaiditwouldappealtheverdict,byaCalifornia
[Link],readGregLeffler'sanalysis.
Makeofthiswhatyouwill:ArelativelysmallnumberofAshleyMadison
[Link]
[Link]:Theywerewere
likelytohavebeendisciplinedbytheSEC,andwereaboveaveragein"bribery
andfraudscandals,taxdisputes,humanrightsviolationsandproductquality
problems,"[Link]
upside:Theseestablishments"alsoappearedtobemoreinventiveandcreative"
aboveaverageinpatents,which"coveredawiderrangeoftechnologies,"and
"tendedtobeinhighgrowthsectors."
DonaldTrumpclinchedtheRepublicanpresidentialnominationwith
1,239delegates,accordingtoacountfirstreportedbytheAP.
CoverArt:IllustrationsofherostyleG7leadersbuiltforaNGOtoencourage
themtobeUniversalHealthCoveragesuperheroes,Isecity,Mieprefectureon
May26,2016.WorldleaderskickofftwodaysofG7talksinJapanonMay26
withthecreakyglobaleconomy,terrorism,refugees,China'scontroversial
maritimeclaims,andapossibleBrexitheadliningtheirpackedagenda.
FeaturedInTechnology,DailyDigest
Writtenby
JohnCAbell
Like
Comment
Follow
497likes 17comments
Addyourcomment
Popular
AndyBoura
SeniorInformationSecurityArchitectThomsonReuters(DirectorSecure
ProductArchitecture|CISSP|MPhys)
[Link]
wasprettyclearfromthatOraclewerehavingapunttoseeiftheycouldget
[Link]'sreallyimportantprecidentforsoftwareengineeringbasedon
myunderstandingAPIsarenotcopyrightable.
[Link]
12/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
Like (5)
Reply(4)
17hoursago
SuzyEkman,DiegoValeroSeyffert,BeatGaldsAizpurua,+2
AndyBoura
SeniorInformationSecurityArchitectThomsonReuters
(DirectorSecureProductArchitecture|CISSP|MPhys)
[Link]'tdevelopeanapplicationinJava
[Link]
machinetorunAndroidJavaapplicationswithoutpaying
Oraclefortheirvirtualmachine.
Like
5hoursago
KhaledHussain
SeniorSystemsEngineeratGlobalCharge
ErikGrobIthinkthey'dabsolutelywantyoutodevelopyour
softwareinJavaonthepremisethatifyoursoftwareturnsout
tobesuccessful,they'dtake"apunttoseeiftheycouldget
anything"fromyou,[Link]
theywon't...
Like
5hoursago
ShowMore
ShowMore
ChrisMoore
Follow
Facebookandothersthinkmessagingasaplatform
[Link]?
May26,2016
10,461views
[Link]
236Likes
26Comments
13/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
Asinvestorsinthemobileecosystem,weareconstantlyworkingtobetter
understandandpredicthowtheuniqueaspectsofthemobilecomputingplatform
willallowentrepreneurstobuildnewandlastingbusinessesacrossindustries.
Inspiredbyrecentindustrydevelopmentsandconversationswevebeenhaving
withfriendsandfounderscreatingmobilefirstservices,wedecidedtoopen
sourcemoreofourconversationsandcreateaforumtosurfacedifferent
perspectivesonmobilerelatedissuesthathaverelevanceacrossarangeof
categories.
Ourintentistoshareopinionsandpracticaladvicearoundwhatsworkingaswell
[Link]
[Link]
commentary,metricsandanalysis,guestpostsandquicktakesinthisvein(like
thisperspectiveonGboardfrommypartnerJamieDavidson).
Latelywevebeenawashinthemessagingasaplatformdiscussionsasallthe
majorplayersarecomingoutwithmessaging+[Link]
announcedAlloasakindofnewsmartmessagingapp.FacebooksF8messaging
newshadalreadybeentopofmindasbothasourceofpromiseandapainful
reminderofhowchallengingthecurrentappdiscoveryanddistributionstructure
[Link]
providers?[Link]
theobviousreasonthateveryoneislookingforanotherwaytoreachconsumers
[Link]
basedinterfaceforthefullrangeofapps,andtheinevitabilityofthesame
discoverychallengesifthemodelgainsawarenessandtraction.
Oneappwhoseapproachtodistributionandengagementcouldinformusmore
broadlyisRiffsy(alsoaRedpointportfoliocompany).AttherecentMobileApps
Unlockedconference([Link]
LovalloandJayWeintraub)IdidanonstageinterviewwithRffsyfounderand
CEODavidMcIntoshwherewetalkedaboutRiffsysapproachtoleveraging
[Link]
makingRiffsyavailableacrossmultiplemessagingplatformswaskeytoachieving
[Link],Kik,Twitter,and
[Link]
GIFsacrossmultiplemobilemessagingplatformsnosmallfeatgiventhe
[Link]
fromtheTencentplaybookwheretheWeChatplatformdominatesthemessaging
[Link]
[Link]
14/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
arguethatmakesthewholelandscapemoreinterestingandanareawethinkis
stillinitsearlystageshereintheU.S.
WillmessengerasaplatformbeaTHING?Andwhatdoesthatportendforallthe
platformsandappmakerscompetingforourattentionaswellasthemobile
ecosystematlarge?Aswereactivelyexploringotherideastotackletheproblem
ofgettingandkeepingpeoplesattentioninthiscrowdedmobilelandscape,were
[Link]
messagingasaplatformandhowwecanbuildaneffectivedistributionand
engagementmodelformobilecontentandservices.
VentureCapital, MobileApplications, StartUps
FeaturedInTechnology,Entrepreneurship,Editor'sPicks,VC&PrivateEquity,Mobile
Writtenby
Follow
ChrisMoore
Like
Comment
236likes 26comments
Addyourcomment
Popular
MarkParrish
[Link],ProductDevelopmentatTMGHealth
Yawn![Link]
[Link]
Pythonsays,thiswillincrease"SPAMSPAMSPAM".Dobusinessesreally
needtobeattachedtocustomersviamessagingapps?
Like (3)
Reply(3)
19hoursago
JonathandePotter,BrianHochstetter,[Link]
TaitusiVuataki
ProjectCoordinator
NopejustFacebook
Like
5hoursago
[Link],ProductDevelopmentatTMGHealth
It'sverymuchmissingthepointasasolution...theproblem,
thatisalludedtoisthattheywantamechanismthatwillget
peopletobuythingsviaachatsystemthebottleneckwiththat
[Link]
characterised,[Link]
solutionisnottheplatform,butthemechanismofpurchase.A
platformforsales(ormaybe"arena")couldbeasupermarket,
butthemechanismisthetillinfluencingfactorsarethe
[Link]
15/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
ambience(music,temperature,aroma,colours),theabsence
ofclocks,thelayoutoftheproducts,etc...youcouldarguethat
asolutionmechanismmightbehavingthetillfittedtothe
trolleyyoupusharound,orattheendofeachaisle,etc...The
realplatform,mightbethetrolleyorthemannedtillandself
servicetill,andisverymuchadependencyofthemechanism.
That'[Link]
sufferingfromafailureofproblemanalysisandrequirements
capture.
Like (2)
7hoursago
KristianWildeandDallasStephens
ShowMore
ShowMore
TristanHarris
Follow
DesignEthics&ProductPhilosopheratGoogle
HowTechnologyHijacksPeoplesMindsfroma
MagicianandGooglesDesignEthicist
May26,2016
7,013views
354Likes
39Comments
[Link]
whyIspentthelastthreeyearsasaDesignEthicistatGooglecaringabouthowto
designthingsinawaythatdefendsabillionpeoplesmindsfromgettinghijacked.
Whenusingtechnology,weoftenfocusoptimisticallyonallthethingsitdoesfor
[Link].
[Link]
16/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
Wheredoestechnologyexploitourmindsweaknesses?
[Link]
blindspots,edges,vulnerabilitiesandlimitsofpeoplesperception,sotheycan
[Link]
pushpeoplesbuttons,youcanplaythemlikeapiano.
(Thatsmeperformingsleightofhandmagicatmymothersbirthdayparty)
[Link]
psychologicalvulnerabilities(consciouslyandunconsciously)againstyouinthe
racetograbyourattention.
Iwanttoshowyouhowtheydoit.
Hijack#1:IfYouControltheMenu,YouControltheChoices
[Link]
17/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
[Link]
ofusfiercelydefendourrighttomakefreechoices,whileweignorehowthose
choicesaremanipulatedupstreambymenuswedidntchooseinthefirstplace.
[Link]
whilearchitectingthemenusothattheywin,[Link]
emphasizeenoughhowdeepthisinsightis.
Whenpeoplearegivenamenuofchoices,theyrarelyask:
whatsnotonthemenu?
whyamIbeinggiventheseoptionsandnotothers?
doIknowthemenuprovidersgoals?
isthismenuempoweringformyoriginalneed,orarethechoicesactuallya
distraction?([Link])
[Link]
18/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
(Howempoweringisthismenuofchoicesfortheneed,Iranoutof
toothpaste?)
Forexample,imagineyoureoutwithfriendsonaTuesdaynightandwantto
[Link]
[Link]
[Link],comparingcocktail
[Link]?
Itsnotthatbarsarentagoodchoice,itsthatYelpsubstitutedthegroups
originalquestion(wherecanwegotokeeptalking?)withadifferentquestion
(whatsabarwithgoodphotosofcocktails?)allbyshapingthemenu.
Moreover,thegroupfallsfortheillusionthatYelpsmenurepresentsacomplete
[Link],theydontsee
[Link]
[Link]
showuponYelpsmenu.
[Link]
19/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
(Yelpsubtlyreframesthegroupsneedwherecanwegotokeeptalking?in
termsofphotosofcocktailsserved.)
Themorechoicestechnologygivesusinnearlyeverydomainofourlives
(information,events,placestogo,friends,dating,jobs)themoreweassume
[Link]
it?
Themostempoweringmenuisdifferentthanthemenuthathasthe
[Link],itseasy
tolosetrackofthedifference:
Whosfreetonighttohangout?becomesamenuofmostrecentpeople
whotextedus(whowecouldping).
Whatshappeningintheworld?becomesamenuofnewsfeedstories.
Whossingletogoonadate?becomesamenuoffacestoswipeonTinder
(insteadoflocaleventswithfriends,orurbanadventuresnearby).
[Link]
(insteadofempoweringwaystocommunicatewithaperson).
[Link]
20/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
Whenwewakeupinthemorningandturnourphoneovertoseealistof
notificationsitframestheexperienceofwakingupinthemorningarounda
menuofallthethingsIvemissedsinceyesterday.(formoreexamples,seeJoe
EdelmansEmpoweringDesigntalk)
[Link]
21/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
Byshapingthemenuswepickfrom,technologyhijacksthewayweperceiveour
[Link]
optionsweregiven,themorewellnoticewhentheydontactuallyalignwithour
trueneeds.
Hijack#2:PutaSlotMachineInaBillionPockets
[Link]
22/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
Ifyoureanapp,howdoyoukeeppeoplehooked?Turnyourselfintoaslot
machine.
[Link]?Are
wemaking150consciouschoices?
Onemajorreasonwhyisthe#1psychologicalingredientinslot
machines:intermittentvariablerewards.
Ifyouwanttomaximizeaddictiveness,alltechdesignersneedtodoislinka
usersaction(likepullingalever)[Link]
immediatelyreceiveeitheranenticingreward(amatch,aprize!)ornothing.
Addictivenessismaximizedwhentherateofrewardismostvariable.
Doesthiseffectreallyworkonpeople?[Link]
moneyintheUnitedStatesthanbaseball,movies,andtheme
[Link],peopleget
problematicallyinvolvedwithslotmachines34xfasteraccordingtoNYU
professorNatashaDowSchull,authorofAddictionbyDesign.
[Link]
23/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
Butherestheunfortunatetruthseveralbillionpeoplehaveaslot
machinetheirpocket:
Whenwepullourphoneoutofourpocket,wereplayingaslotmachineto
seewhatnotificationswegot.
Whenwepulltorefreshouremail,wereplayingaslotmachinetoseewhat
newemailwegot.
WhenweswipedownourfingertoscrolltheInstagramfeed,wereplayinga
slotmachinetoseewhatphotocomesnext.
Whenweswipefacesleft/rightondatingappslikeTinder,wereplayinga
slotmachinetoseeifwegotamatch.
Whenwetapthe#ofrednotifications,wereplayingaslotmachineto
whatsunderneath.
Appsandwebsitessprinkleintermittentvariablerewardsallovertheirproducts
becauseitsgoodforbusiness.
Butinothercases,[Link],thereisno
maliciouscorporationbehindallofemailwhoconsciouslychosetomakeitaslot
[Link].
NeitherdidAppleandGooglesdesignerswantphonestoworklikeslot
[Link].
[Link]
24/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
ButnowcompanieslikeAppleandGooglehavearesponsibilitytoreducethese
effectsbyconvertingintermittentvariablerewardsintolessaddictive,more
[Link],theycouldempowerpeopleto
setpredictabletimesduringthedayorweekforwhentheywanttocheckslot
machineapps,andcorrespondinglyadjustwhennewmessagesaredeliveredto
alignwiththosetimes.
Hijack#3:FearofMissingSomethingImportant(FOMSI)
Anotherwayappsandwebsiteshijackpeoplesmindsisbyinducinga1%chance
youcouldbemissingsomethingimportant.
IfIconvinceyouthatImachannelforimportantinformation,messages,
friendships,orpotentialsexualopportunitiesitwillbehardforyoutoturnme
off,unsubscribe,orremoveyouraccountbecause(aha,Iwin)youmightmiss
somethingimportant:
Thiskeepsussubscribedtonewslettersevenaftertheyhaventdelivered
recentbenefits(whatifImissafutureannouncement?)
Thiskeepsusfriendedtopeoplewithwhomwehaventspokeinages
(whatifImisssomethingimportantfromthem?)
Thiskeepsusswipingfacesondatingapps,evenwhenwehaventevenmet
upwithanyoneinawhile(whatifImissthatonehotmatchwholikesme?)
Thiskeepsususingsocialmedia(whatifImissthatimportantnewsstoryor
fallbehindwhatmyfriendsaretalkingabout?)
Butifwezoomintothatfear,welldiscoverthatitsunbounded:wellalways
misssomethingimportantatanypointwhenwestopusingsomething.
TherearemagicmomentsonFacebookwellmissbynotusingitforthe6th
hour([Link]).
TherearemagicmomentswellmissonTinder([Link]
partner)bynotswipingour700thmatch.
Thereareemergencyphonecallswellmissifwerenotconnected24/7.
Butlivingmomenttomomentwiththefearofmissingsomethingisnthowwere
builttolive.
Anditsamazinghowquickly,onceweletgoofthatfear,wewakeupfromthe
[Link],unsubscribefromthose
[Link]
25/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
notifications,orgotoCampGroundedtheconcernswethoughtwedhavedont
actuallyhappen.
Wedontmisswhatwedontsee.
Thethought,whatifImisssomethingimportant?isgeneratedinadvanceof
unplugging,unsubscribing,[Link]
recognizedthat,andhelpedusproactivelytuneourrelationshipswithfriendsand
businessesintermsofwhatwedefineastimewellspentforourlives,insteadof
intermsofwhatwemightmiss.
Hijack#4:SocialApproval
[Link],tobeapprovedor
[Link]
socialapprovalisinthehandsoftechcompanies.
WhenIgettaggedbymyfriendMarc,Iimaginehimmakingaconsciouschoiceto
[Link]
inthefirstplace.
Facebook,InstagramorSnapChatcanmanipulatehowoftenpeoplegettaggedin
photosbyautomaticallysuggestingallthefacespeopleshouldtag([Link]
showingaboxwitha1clickconfirmation,TagTristaninthisphoto?).
[Link]
26/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
SowhenMarctagsme,hesactuallyrespondingtoFacebookssuggestion,not
[Link],Facebook
controlsthemultiplierforhowoftenmillionsofpeopleexperiencetheirsocial
approvalontheline.
(Facebookusesautomaticsuggestionslikethistogetpeopletotagmorepeople,
creatingmoresocialexternalitiesandinterruptions.)
ThesamehappenswhenwechangeourmainprofilephotoFacebookknows
thatsamomentwhenwerevulnerabletosocialapproval:whatdomyfriends
thinkofmynewpic?Facebookcanrankthishigherinthenewsfeed,soitsticks
[Link]
orcommentonit,wellgetpulledrightback.
Everyoneinnatelyrespondstosocialapproval,butsomedemographics
(teenagers)[Link]
recognizehowpowerfuldesignersarewhentheyexploitthisvulnerability.
[Link]
27/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
Hijack#5:SocialReciprocity(Titfortat)
YoudomeafavorIoweyouonenexttime.
Yousay,thankyouIhavetosayyourewelcome.
Yousendmeanemailitsrudenottogetbacktoyou.
Youfollowmeitsrudenottofollowyouback.(especiallyforteenagers)
[Link]
Approval,techcompaniesnowmanipulatehowoftenweexperienceit.
Insomecases,[Link],textingandmessagingappsaresocial
[Link],companiesexploitthisvulnerabilityon
purpose.
[Link]
socialobligationsforeachotheraspossible,becauseeachtimetheyreciprocate
(byacceptingaconnection,respondingtoamessage,orendorsingsomeoneback
foraskill)[Link]
spendmoretime.
LikeFacebook,[Link]
aninvitationfromsomeonetoconnect,youimaginethatpersonmakinga
consciouschoicetoinviteyou,wheninreality,theylikelyunconsciously
[Link],LinkedIn
turnsyourunconsciousimpulses(toaddaperson)intonewsocialobligations
[Link]
peoplespenddoingit.
[Link]
28/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
Imaginemillionsofpeoplegettinginterruptedlikethisthroughouttheirday,
runningaroundlikechickenswiththeirheadscutoff,reciprocatingeachother
alldesignedbycompanieswhoprofitfromit.
Welcometosocialmedia.
Afteracceptinganendorsement,LinkedIntakesadvantageofyourbiasto
reciprocatebyoffering*four*additionalpeopleforyoutoendorseinreturn.
[Link]
29/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
Imagineiftechnologycompanieshadaresponsibilitytominimizesocial
[Link]
publicsinterestsanindustryconsortiumoranFDAfortechthatmonitored
whentechnologycompaniesabusedthesebiases?
Hijack#6:Bottomlessbowls,InfiniteFeeds,andAutoplay
Anotherwaytohijackpeopleistokeepthemconsumingthings,evenwhenthey
arenthungryanymore.
How?[Link],andturnitintoa
bottomlessflowthatkeepsgoing.
[Link]
30/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
CornellprofessorBrianWansinkdemonstratedthisinhisstudyshowingyoucan
trickpeopleintokeepeatingsoupbygivingthemabottomlessbowlthat
[Link],peopleeat73%more
caloriesthanthosewithnormalbowlsandunderestimatehowmanycaloriesthey
ateby140calories.
[Link]
autorefillwithreasonstokeepyouscrolling,andpurposelyeliminateanyreason
foryoutopause,reconsiderorleave.
ItsalsowhyvideoandsocialmediasiteslikeNetflix,YouTubeor
Facebookautoplaythenextvideoafteracountdowninsteadofwaitingforyouto
makeaconsciouschoice(incaseyouwont).Ahugeportionoftrafficonthese
websitesisdrivenbyautoplayingthenextthing.
Techcompaniesoftenclaimthatwerejustmakingiteasierforuserstoseethe
videotheywanttowatchwhentheyareactuallyservingtheirbusinessinterests.
Andyoucantblamethem,becauseincreasingtimespentisthecurrencythey
competefor.
Instead,imagineiftechnologycompaniesempoweredyoutoconsciouslybound
[Link]
boundingthequantityoftimeyouspend,butthequalitiesofwhatwouldbe
timewellspent.
[Link]
31/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
Hijack#7:[Link]
Companiesknowthatmessagesthatinterruptpeopleimmediatelyaremore
persuasiveatgettingpeopletorespondthanmessagesdeliveredasynchronously
(likeemailoranydeferredinbox).
Giventhechoice,FacebookMessenger(orWhatsApp,WeChatorSnapChatfor
thatmatter)wouldprefertodesigntheirmessagingsystemtointerrupt
recipientsimmediately(andshowachatbox)insteadofhelpingusersrespect
eachothersattention.
Inotherwords,interruptionisgoodforbusiness.
Itsalsointheirinteresttoheightenthefeelingofurgencyandsocialreciprocity.
Forexample,Facebookautomaticallytellsthesenderwhenyousawtheir
message,insteadoflettingyouavoiddisclosingwhetheryoureadit(nowthat
youknowIveseenthemessage,Ifeelevenmoreobligatedtorespond.)
Bycontrast,ApplemorerespectfullyletsuserstoggleReadReceiptsonoroff.
Theproblemis,maximizinginterruptionsinthenameofbusinesscreatesa
tragedyofthecommons,ruiningglobalattentionspansandcausingbillionsof
[Link]
shareddesignstandards(potentially,aspartofTimeWellSpent).
Hijack#8:BundlingYourReasonswithTheirReasons
[Link]
32/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
Anotherwayappshijackyouisbytakingyourreasonsforvisitingtheapp(to
performatask)andmaketheminseparablefromtheappsbusiness
reasons(maximizinghowmuchweconsumeoncewerethere).
Forexample,inthephysicalworldofgrocerystories,the#1and#2mostpopular
[Link]
maximizehowmuchpeoplebuy,sotheyputthepharmacyandthemilkatthe
backofthestore.
Inotherwords,theymakethethingcustomerswant(milk,pharmacy)
[Link]
supportpeople,theywouldputthemostpopularitemsinthefront.
[Link],whenyouyou
wanttolookupaFacebookeventhappeningtonight(yourreason)theFacebook
appdoesntallowyoutoaccessitwithoutfirstlandingonthenewsfeed(their
reasons),[Link]
haveforusingFacebook,intotheirreasonwhichistomaximizethetimeyou
spendconsumingthings.
Inanidealworld,appswouldalwaysgiveyouadirectwaytogetwhatyouwant
separatelyfromwhattheywant.
Imagineadigitalbillofrightsoutliningdesignstandardsthatforcedthe
productsusedbybillionsofpeopletosupportempoweringwaysforthemto
navigatetowardtheirgoals.
Hijack#9:InconvenientChoices
[Link]
33/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
Weretoldthatitsenoughforbusinessestomakechoicesavailable.
Ifyoudontlikeityoucanalwaysuseadifferentproduct.
Ifyoudontlikeit,youcanalwaysunsubscribe.
Ifyoureaddictedtoourapp,youcanalwaysuninstallitfromyourphone.
Businessesnaturallywanttomakethechoicestheywantyoutomakeeasier,
[Link]
[Link],
andhardertopickthethingyoudont.
Forexample,[Link]
[Link],they
sendyouanemailwithinformationonhowtocancelyouraccountbycallinga
phonenumberthatsonlyopenatcertaintimes.
Insteadofviewingtheworldintermsofavailabilityofchoices,weshouldview
[Link]
[Link]
34/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
choiceswerelabeledwithhowdifficulttheyweretofulfill(likecoefficientsof
friction)andtherewasanindependententityanindustryconsortiumornon
profitthatlabeledthesedifficultiesandsetstandardsforhoweasynavigation
shouldbe.
Hijack#10:ForecastingErrors,FootintheDoorstrategies
SummaryAndHowWeCanFixThis
Areyouupsetthattechnologyhijacksyouragency?[Link]
[Link],
seminars,workshopsandtrainingsthatteachaspiringtechentrepreneurs
[Link]
inventnewwaystokeepyouhooked.
Theultimatefreedomisafreemind,andweneedtechnologythatsonourteam
tohelpuslive,feel,thinkandactfreely.
Weneedoursmartphones,notificationsscreensandwebbrowserstobe
exoskeletonsforourmindsandinterpersonalrelationshipsthatputourvalues,
notourimpulses,[Link]
thesamerigorasprivacyandotherdigitalrights.
TristanHarriswasaProductPhilosopheratGoogleuntil2016wherehestudied
howtechnologyaffectsabillionpeoplesattention,[Link]
moreresourcesonTimeWellSpent,see[Link]
UPDATE:Thefirstversionofthispostlackedacknowledgementsto
thosewhoinspiredmythinkingovermanyyearsincludingJoe
Edelman,AzaRaskin,RaphDAmico,JonathanHarrisandDamon
Horowitz.
[Link]
35/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
MythinkingonmenusandchoicemakingaredeeplyrootedinJoe
EdelmansworkonHumanValuesandChoicemaking.
FeaturedInEntrepreneurship,Design,SocialMedia,Editor'sPicks
Writtenby
TristanHarris
[Link]
Follow
36/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
[Link]
37/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
[Link]
38/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
[Link]
39/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
[Link]
40/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
[Link]
41/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
[Link]
42/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
[Link]
43/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
[Link]
44/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
[Link]
45/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
[Link]
46/47
5/27/2016
Tutorial:StepbyStepDatabaseDesigninSQL
[Link]
47/47