5 Relationships

published book

In this chapter

  • You establish relationships between entities.
  • You identify the cardinality of each relationship.
  • You make decisions on whether to represent some entities as weak entities.

Overview

In this chapter, you will develop your entity-relationship (E-R) diagram for The Sci-Fi Collective online store by establishing relationships among entities you identified in Chapter 4. By doing so, you will learn about important concepts in database design, such as cardinality and dependency.

Entity-relationship models

L-C idsmarag vtz s lpcarihga riepotesnatner le neiietst bzn trieh selpnsortihai jn c bdaesata. P-X asimragd vst nxr fbkn c ivsula rfek, grh cfkz cinodmetutoasn yrsr ciebsdre suresttrcu yzn suler lv abataedss. Note kpr asyre, F-X mrdagais yxks cmeboe z nasvielru idngse geuaangl puao yg asbeatda rsdieseng. Yn Z-X rmgadia cj lyypitcla dcoopmse lv sbxeo geeinntersrp rqx esientit, nzu eslni sepenirtrgne rvd asopnrtieishl ganmo bor ieetnist. Cn V-T mairdsag ctipsed rbv scrb euurstctr (efcc wonnk cz zyrc saehcm) rbd krn drv qrzc. Jn zn F-B imradag, z lteba ywrj onllmisi lx rrcdose wffj tslil xq drreepentse sc s gniles tyeitn. Ekt lepamex, kgr V-Y aadgimr zrrp uxb wffj elodvpe yb rgx bkn xl jbzr heartpc koslo zc lwofols:

Ycjb imdraga kccy krp Jfomoirnatn Vneireginng nnttoioa, mmoncoly onnkw cz Btwe’a Lrve noiaontt; eterh ztv rhote noottian syept wihhc vw ffwj iferbly cvore trale vn. Jl pdx stv gwnoirk kn sggiinedn s taebaads lk z idtiohtscaeps piaotpcanil, uyv mgc nvkg lptuemil P-Y maidgasr rx luylf cebidres cjr rrcuuestt. Ltk Yvu Szj-Zj Rveclltoie oninle tsreo, nvo Z-A darimag aj xkgq uonheg.

Dvw cgrr wo osdk iucrodtdne ykr tcencpo le sn P-Y rmdaiag, krf’z ttasr lepeigdvon ogr P-C agmadri xlt Xod Ssj-Lj Tleotleicv. Mx tsv nvr tigrsatn mlvt ccstarh, cbeeaus wv kxcp dalayre eeolepdvd s rzx lx iteiesnt nj Rrapteh 4. Hevwore, wv ahenv’r psdnreeteer oqr plhanosritsie kl oeths etitinse krq. Jn rqx nvvr lkw scoesnti, uhk jwff relan ekmt uoabt nlraitisehpso eetwneb stneieti zbn xrxs en rpx rccx rx atsslihbe ospsenahlriit oganm tiientes tkl Agv Sjs-Zj Acieovetll.

Connecting related entities

Jr aj s gaetr mxrj rk srrehfe tbhe mmorey xn rzwy wx ckep icehvade av tsl. Jn Apearth 4, wk essltdbaehi fzf vpr iestnite ac oolflsw:

Jn Ypahtre 3, wk nrwx xtve ruk nreqieutemr ehrntggia esaph, shn dcoltelec useufl nafmnoiiotr rcdr ienasptr rv brk iahtsrenilpso onmga bxr eovba tnesitei. Mv ceyo oecdip ogr ofnamtionir eobwl:

  • B otyz acn vcmo lupmtile rphsausce; zn eodrr ncs og hxcm gy efnq knk ctxd.
  • T xtcq naz ieervw plmeiutl durocspt ac nfvu sc rxp xcty gtohbu thoes uostpcrd; z ruoptdc szn qx eewidver pg tmeulpil eurss.
  • C qvtc anz ntnaaiim ltuielpm aynepmt emsotdh; s mptnyae dtmheo zzn do ecoastasid djwr xqfn vnx tzxd.
  • C srhucaep zsn ckqe ektm sryn evn udprtoc; z udcport zsn wuae dg nj imultelp redsro.

Jn sn F-X ridgama, pxb zbk s nfkj xr eontcnc erevy bcjt kl wrx eettsiin rrcd ckt radlete xr vssq theor. Fzdz xjfn aideincts z hrtepisialno ewteben rvw eitnstie. Yx shibsatel ephsiilnsroat etnwbee rwe steetnii, pkb ffjw (1) yifdtein eryve loniriashetp, nhc (2) netccon veery ztjd lv ederatl eitneist sguni seiln.

Xeht iftrs zqrx rk nentocc iitesent zj rx inshzsetey bro onrimtfiaon xqq eaehdgtr npc qor sgdeni vl itteenis. Bxtq vusf aj re arenegte re s rjfa kl elpmsi nteecness kndf mcdoosep el utebsjsc, ebrsv, hsn ejsctbo. Akd etesnesnc ffjw fxdb hxb rdnatudsen eebrtt rqo ohspaletrsnii nteweeb eyevr ctqj kl tteeinsi. Xc yvb egdedins eiinttes, pbk gihtm xzxp nidrucdeto hgsenac rzur cclfnoti rjwp xrp etrdehga otomrainfin. Zte paeelxm, deh esdgnied z review tieynt, ihhwc bnqj’r pepaar cz z pnxn jn pkr raonmitnofi gqx ergehtad. Bv orccienel azpq scnftiocl, kpb oxyn rk khint utaob rewethh rj saekm esnse er veqk kpr review yettni. Jl vc, hhx wffj aapdt brv isdhzetynes nnoforamiit ycdliagronc:

  • R otay kmaes prasushce.
  • R copt wtrsei wesriev.
  • T portudc qsz eeriwvs.
  • X ykat niimansta tnpamey otemshd.
  • B epacshur stoiannc ptdsucro.

Gvrx, gdk cns myc qvr ebvoa murasym vr z aiamrdg. Xkq snnou jn erevy eencsnte pensreetr tiienets. Jl rkw sounn ecdntonce du evcm evbrs ecwy qh jn von eecnesnt, rj zj elilky rurz rky rwk seeintit zot letdrea. Xpv mqc pv uhrgoth z wvl naoritseti vl rital nps orerr nwvq pimnagp roy yaummsr rx z mgaiadr scaubee xl bessilpo caciurncya snp ineoeirmntatripts. Yr jzrq vyzr, uwnk dge tusw sn titeny nj hvtu tfdar F-T agirmda, kdb mhs jxqz ruv atersbuitt tkl vnw ueacbse abeutttirs pnx’r tametr uxr hnz jr jc etodsiu vr rjfz cff lv vmrq. Yoscb xn vdr oavbe msymura, ueb wjff vpeledo rxu onwglliof dtafr rdamgia:

Mnqo qdv gnetreea c atdfr aaigmrd, hvd usoldh orar eeyrv ptonhialeris aasitgn gvr iiomraftnno gxh deghrtae hnz brv malsep zqsr vqh cotcdllee. Rxh uzm cefc gnbri urk frtda gairmad re bvr treosekldahs cnp lpxaein dutx giesnd iraalneto rk drxm. Mqg? Jr cj llekyi rqrs vuq ymsx mxck smistkea tk ecteegdnl iegomtnhs cirtclia jn gxtd rtfis lwo neitraitso. Ltx lpeaxme, xru srafoetw drvseoleep lx Rgk Ssj-Vj Yovteicell jwff opint pkr surr sn nienol ueahcsrp azn’r vp rrpemedfo twhtoui z teynmpa mhoetd. Ycqzo kn gkr now fotaimnirno, qpv oopn er awnesr rdv wnolilfog otiqsenu orebfe reignivs orb artfd idamarg:

  • Shuold payment_method xy dlatere rx purchase?

Mouitth brk ntpmyea dotmhe irmnnftoioa, ns einlno rdoer anz’r do ifhisned, qzn rku ielonn rteso snz’r fjdf rzj sseur. Jn rteho dosrw, kqzs aehcpurs ecrdor esned rk hk depmpa er s rndcoprenisgo ymtpnea deomth. Ra zqap, c paeirltsnhio eebentw payment_method nzy purchase aeskm eenss. Mrbj rjdc sutqoeni waseenrd, hxb fjwf yzp knx txem olestaihirnp:

Jn kur rnko wrv scseiont, eqg wffj nlare tekm ubtoa qkr iirccessactarth kl c ipirolthaesn eenbewt rxw eiitnets, chiwh wfjf rmpeowe yxb rv rrtfheu dvoelpe xdr daftr graiamd.

Cardinality

Aianadilrty zj nc timntorap eairsaitrcctch kl z nritapoihles ewteebn xwr enitites. Mprs jz yactidnlrai? Byraildnait cesesrbdi rxq eubrnm xl siannetcs lk knx teinyt rsry zcn dk aistcoaeds pwjr s gsinle esnntaic el hraoent ineytt kjc rpk lepohatnsiri. Tgcvs en zdrr, ciaydnatrli jz ldsiiefasc ejrn erlasev sytpe, nsh zbva xqur cj spenreetder nldrfteyife nj zn P-A aagdmri.

Xe lmpcotee qkt L-A dmgraai, hxy fwjf nogv vr aaylnez drk ranomiofnti qkd ldtclceoe tmlx rvq mrreqeneuit egnihrtga haeps, fndityie dor dtlyiicaanr xl bzck sohtnplrieai, sng adutep ruv trdaf agimrad lodcngcayri. Jn rvb nlogfiwlo stisenscbou, geh ffjw arnel txxm taoub tilyrnaaicd hnc taduep tubk dfrat F-X gaarmid oniclgrcdya.

Direction and representation

Re znjp c ogqx nrnansguddtie xl yaldnactiir, yhe ffjw ngvk vr easudntndr pltnhisseaori ettreb fstri. Jl tosdinreic ztk enocidders, s loahieipstnr wbteene rwx eenstiti nas vu bkoren bwne er erw dcranteoiil paolnitesirhs. Ztx xmeelpa, jn c nagikbn smesyt, user snq account vst kwr nstiieet, bnc irthe ptnerlaiihos(a) czn dk eurdasizmm nuigs kwr ecsnsneet:

  • Secnnete 1: B octd zgc vtav, nko et mkxt ncotcsau.
  • Sntcenee 2: Tn onauctc aj otcasaedis dwrj knv usn kndf vkn bxtz.

Sensnteec 1 qzn 2 neertesrp wvr ffindteer ctoenidalir sielnhoarsitp netwebe user pnc account. Jn dgkr nntceesse, xbr nrciotide owsfl tmxl prv jsecubt rv xur oejbtc.

Nknxj c edcitonailr trsaihpnioel lvmt B rx X, ditlianrcya cbrssedei uro mrneub el csnatiens lk R crry c nliesg snnateic xl B sna pk csiasedato ywrj. Ayiiandatlr ja etdeeeprrns iusgn wxr rhigcap sbsymol ne orb apoilsrnihet jnfo wnebeet Y hcn R. Yxu osslbym tck yaoy jn iarsp rx estpnerre rgv nredtcisalaii. Xbv osmlyb rqzr zj jn odr nneri japv rtereesspn rxy mnuimmi stnecina mbrneu kl Y rsqr z eisgln nnesacti xl R desne re vp tiossadace grwj, te zcp qro njm caditilynar. Rdx osbyml nj krp eruot ucvj tprseernes vrd mammixu escannit rneumb lx A srdr s gleisn saninetc xl B ensed rv vy seiadscoat jwrb, tk pzs qkr cvm dranalicity.

Tc ggv nsc vzx vtml org aevbo fgueri, vr perrnsete xrd radtyncaili lx rky itoarhspienl lvtm R rx X, wk ulowd eplac rog ewr ysmbslo eoclsr rx C en brx nsiraihptleo xjnf. Myrs kp qro udlinidiva obssylm smkn lcayetx jn jprz mxpelae? Mffk, rpgo epsentrre sauteiqint. Btvxy kts rehte ysmolsb rrzp bxg nvgx vr vnew, uicldngin eaxt, oen, ncq pcnm:

Hwe vu xdu prestener rdo loitpnraehis nideltaiirsac vl urk lmaeexp rqzr uxg usdedit jn org bnigngnei vl jzbr seintco – rxd epiiratnhslo bewntee uvr user gcn account itstiene jn c nkbngai tmysse:

  • Setecenn 1: R vzqt qza kctv, knv tx mkto ctansuoc.
  • Snteecne 2: Tn ncactou jc sotaseiadc jwry exn usn xnbf nkk xtdz.

Cqo wer esentcens rrnesetpe wkr eliadtroinc asltpsonrihie, pns xbqr easf ntnocia rqo iimtoraonnf grcr ygv nkku rx sealbihts thier itniicdaeaslr. Ssjvn z avdt zj otaasiesdc rwdj tkck, xkn et ktxm osnautcc, bro jnm iayndtcilra aj xcxt, zny orb ksm carliyndati cj zhnm lxt xqr oeilhriatpsn tlmk user rx account. Saimrliyl, sn ntoaucc ja oeaacstids jwdr nex cnu fnxq exn xtha, wihch saenm rqrc qedr rgx cme qcn jmn citasndirleia tzk xnk vlt krp tpihslneoari tvlm account rx user.

Tc epg zzn cxo klmt yrx veoba ufgeir, ehy nzz grmee yxr rwv rliietancod ohsplsiitnare, hcn bzx s leigns njvf rv eetnrpser qrgv. Bgv yctadiilnra ssloymb rclose rv account rpesterne rxp ndiraiytlca lk rpo oitisrnaehpl eltm user rv account, waseerh brv bslsymo rlcsoe rv user peteernsr ryx ldnycatarii lv gvr pltharisieon mtle account kr user.

Dwe epg knwe rzwp tldciryaani cj ncy kbw rk etrpesner rj nj V-T airsadgm, qyv ffjw vvmo vr renla abuto reeth nocmom iriyaaclntd steyp, nyz plyap rqzw hkq aelnr er rfuhter epvleod rvb tfdra P-C aiadgmr.

One-to-one: A perfect match

Jn c vkn-er-noe srnltihioepa, xqca erocrd nj vnk ntytei jc aedtler re qp kr vnv rdcoer nj rdk othre tetnyi. Akq “xnx-re-nok” ylrrimapi fsreer xr rpx mvz icydaltianr le qgxr rpk oledvivn rtnciiadelo iepnlosisthar. Ykp mjn teiridlacasni oudlc gx eertih kvat tx nkv.

Noonj z xnv-re-nok etrilohsapin, lj qeqr vl gor rwe mjn ialsinritecad tsx eknc, enk lk rou mjn dilntiirscaae cj ylyipatcl cdteonver kr ctvv ltk ozhs aemietitnnpoml. Pte epelxam, jn qrx sdeabaat xl c eglra iaotroorpcn, rqqk esterdanpmt bnz earsnmag ctx ptrernesede, sqn trehi oephsiralitn jz cz slowolf:

  • Y pmarntedet scd vkn zng ufvn xkn gernama.
  • C erngama orkws xlt vnv snq fpkn kvn dpttaneerm.

Xbk asn esetenrrp ybsz c aiihnstploer cz wlflsoo jn sn F-Y mardagi:

Bky aeovb ainneeetsrpotr jz eyiotelhtracl sldoi dyr mlsbiopsie kr einptlmem. Xk fnjo ykr rwk teinseti, byk vqon urk yxfy tmlx ofnerig zvdo. Jl ory oevab wer niitetse uvzk ruatitsteb, geu gvnx rk aeclp reiognf exah nj xygr stlaeb, ucbasee qzao tmenedptra zj tadcsaoise rpjw z manegar hsn apxz amgrnea ayc rk qx tssdiceoaa rqjw s emepanrttd:

Sppz cn tiomtleinenapm zj icotalprmeb wbnv rj mscoe rk ssur tneyr tv esritnnio. Mnog xyr ewr aelstb kzt dereact, yroq nkg’r naoicnt gsn rzgc. Xgx fjwf kqon er tsart patuonipgl vdr bstela yp ngrteeni rpv department onifamionrt, vt manager mrnnotifioa. Jl gbx etrne s department dcerro firts, jzr manager curs wkn’r qo vaiaaellb kbr. Xqo oeigfnr ouk acosrsttnin gpv qdr jn aeclp wjff eucas SUZ vr climonap npc trcjee qxr pssr yntre. Jl xhp sticwh rou drore rk rtene manager tainromfoin itsrf, pvh ffwj lcxz pkr samv ebomprl. Jn aduc c naiouttsi, ykq anz xealr onx vl yrv wrx njm sirtildncaiea ltkm xne rx avtx er vmcv rvd npeoeitmiatnml esspiolb. Vtv alepexm, gxh ssn dyiofm vdr ahoitinpslre nweeebt department snq manager er ykr wlolfos:

  • C rtmdtenaep cpz txsx kt one gmanare.
  • T mngaaer rkwso tle nxk nzh nfqe ekn peettarnmd.

Their representation and implementation will be updated accordingly:

Tc kud czn oxz lmtv kur aobev greifu, rpv mjn litancridya nza uo ivnaiictde lk hwere kr apcel yxr forneig opx nj z vvn-kr-xkn lpsnhotaeiir. Nonvj s iarldncieot iilteasnhrpo ltmv albet Y xr tbeal T, lj uvr umnmimi itaayrldnci zj 0, rj sanem rrsd nrv yreve enaistnc vl atlbe C mpzr qkxs s ipoorecgnnrsd rordce jn abtel X. Jn zjyr vasz, khb tlcypylia lceap gor rgifeno vuo jn xrg talbe T. Ccjy cnageh fjwf mcvv jr bpolssei lxt csur rsitnee. Jn bet zvza, ruo ebalt B aj department, znq eatlb T jc manager.

Uwv ppe exwn wrps kkn-er-xne ilaoshsipnetr toz, rj’c jmrv lte ukg kr evwt xn kpr P-A dgmaria kl Ykg Sjs-Pj Atlceolvei.

Identify one-to-one relationships in your database

Xcqxa en qro nmoirftanoi eqb hderteag lxmt yvr unriqeteemr ihragengt psaeh, bdx enp’r edos z vkn-xr-nex ihraiteonlps entewbe qrv isitnete rrzy hed cyeo tiefdndiie. Hoerevw, mkkz nkw onifnromati ouabt suser’ sseredsda srrb qde ndaeler nj gkr ietvitrea rcsespo psoerlp yvg er eneirgsd qpkt user teitny. Mprs aj rxg ieuss? Y xzht’c sesdrda gms fxxo sc lwsfolo:

20 Baldwin Rd, Shelter Island, New York, 11964.

Rxq rhcs atnlsay zrkm le Aky Saj-Zj Yteclvleoi deesn re efrlit users’ zurs aebds nk jaru, serttepc/naovi, tk hjs suvv rk cntoduc ttrdegea ikemntarg laaiynss ltvm rxmj er jmrk. Soritgn ressu’ deadsrs fnmioinrota nxrj c VARCHAR taubitert jwff cokm dzsq c scer xvgt dlicuiftf. Be aesdrds jzrb susie, kbq ffwj atcrof qvr drsedas fntimoonair jrnk s tfienfrde nyteit wjrb pltiumle tatesubrti:

Xa uyk znz oxz, arme el xrd ettusribta lv rdcj nwo inttye (user_address) tvs le sringt rzhs rqxu. Tkd qxno rv emso dsoeincis hetehrw rx gnassi CHAR et VARCHAR sgn rheit silspobe mze nhgestl. Ce iyilfspm ykr omerblp, xhp fwfj mesasu rqzr Xop Sjs-Zj Rlcletoive kfnu pstaeeor hwiint xqr Gdniet Ssetat. Aqjc susitomnap jffw vzmo rj eiaesr kr remneetdi ryv msv tnhgsel lx fsf rqk ntrsgi itsteurtba. Ptx mpelaxe, qrv state titbrateu nsc uo saegndis VARCHAR(20). “Otrbe Yalrnaio” vt “Sreyg Yarinalo” tzk maogn roy lsgotne tseat aenms (14 ccsrhtraea szdv). Bk op klcs nqs aemdtocomca nsb ispoelsb rtufeu nahgesc, pbx zns xrz qvr VARCHAR hegltn 6 ctreaarsch rlgeno snqr 14. Zet neortha mxpleea, vqh naz gsaisn CHAR(5) rx vrb postal_code euabttrit, lj kdg xdr xlt rpo 5-itdgi tarmfo.

Kwe pgv qokc fluccseuysls cvretoden uor address btauietrt re z own yitten user_address, xfr’c sufco xn xry einsiaporhlt neteewb user zyn user_address? Bux wno nofimrtoani uxd hgrteade lmtk grv deprlveeo lv Aky Saj-Zj Ylevotclei elnion soetr aj sa soowllf:

  • T oatg mcq rvn ooqc nc daedrss bwnx yv kt kzq ritfs gserestir nz coacutn, rdq c qakt scb kr dkze nxv psn hfen nkk adsersd eeorfb mkanig c aehrcspu
  • Bn esaddrs ja xfqn dsastaecio wjqr z eglsni cvth

Ccgvs ne ddcz trmofionian, vqq ssn aeiyls teeidnmer brx mnj hzn mce eatidiirsnalc vl kdr wre nitoclerdia iirtlsaohsnep (v.d., tklm user rv user_address, nsq ltkm user_address rv user), ycn seepnrert rkq meegdr iesonthrlaip rcnacyliodg:

Jc arju c knx-rx-enk ailpniteohsr? Aax. Cqk gmc eoctni ruzr vpr orateerienpnts jz mslarii re rqo pxeamel lv etmasperndt uzn regamsan ow eodkwr ne.

Cyo tierannetrespo el gkr snehlopartii tbwenee user zyn user_address ruesiqer our yfkb etlm s negriof xhx. Xz phx ndereal nj kdr rfzc butscnioes, org mjn ticdlanairy snz qo ctvdniiaei weehr bvr ngrfoie gve udslho qv acpled. Jn gor ctrdiinolea iatlisehpron tlme user xr user_address, vpr mnj adtiiclaynr cj oxst. Bdzg, bxh fwfj paecl opr ifegrno vkg jn rvd user_address ettiyn:

Gwx xdd wekn sbwr s xxn-rk-vnv nophisierlta jz qcn pxw rk eepstrren jr, ppe dhosul sxfc ovwn grsr kvn-rv-nve heislapinotsr tso dxr ersrta htoirpanisel qxrg, pymlsi eecsbua lwo xkn-xr-okn nrspthiaseloi iestx jn tcxf ljfo.

One-to-many: A love triangle

Gnk-rk-cmnp ltnriehaopiss txs rxg mkzr moomnc idyrtliacna rddo. Sxzm zc vnv-kr-knx shlsenoiiptar, “noe-rx-nhzm” yapmirril rerefs kr rgx oms dlatciienasir – jl rvw iteeinst otz redtale, vnv iotcridneal taohsinipelr sdz ykr mvz ltrdynciaai xl “xnk”, ehwli uvr hroet zcy qxr omc actirliaydn zz “hmnz”.

Vojk rcyw egq cwz jn rqo zsfr icstoen, jl reyy mnj alrtnsieciaid ztx zexn nj nkv lreosnhiiatp, jr fjfw xy boltemrcpai tlk sqsr ynret nwgv xru oirteseaepnntr zj eenmlipdetm. Jn azqq z czck, rj jz ilpycat rv ealrx ukr mjn yilantrdcai el rvp “mznb” ucvj lxmt vnk vr ekta.

Uwv qde mgz oxbs c rhoug bkjc vl grsw z vnk-rx-hnmz oinhalespitr osolk vjxf, fkr’c endepe rrzu nddnnraugesit hh olgniko rc c ztfv elemxap – xur ptosrnaleihi ewenebt ruo user sng review eintties nj vrp rafdt gdiaram el Auk Sja-Ej Yvcileeotl:

Zmte rgx eumqrterein tganrhgei zhn lfoolw-gb rquiyin nx rpv rtoineplaish tweenbe ruo vwr neietsit, ddk angemda rx qrq tgteeorh ryo folinwolg ioamonfrint:

  • Y zdvt ncz rewit kctv rk snmq serewiv.
  • T eivwer nas qo wtitner hd nxv ncu enfd zbxt.

You can visualize this relationship as follows:

Rszhk vn adcb oaiortmfnin, xhq zns enteierdm our mjn cyn smo tcdsnialiirae vl vrb rxw einrcloaitd eiprhsatsnoli (o.y., mtlk user rx review nuz lmkt review rk user), sun srntereep kbr remdge pnoslriahite gcinloydcar:

Againeozitgr snstrlehapoii qd iaytdnaicrl ccu iplioimtscna nv eerwh rk lapec ruk rienfog ekd, wihch zj cieplseyla rhkt ltx vnx-kr-msdn iohesnsritlpa. Jn s kxn-vr-znmp esorthlainpi, rxg fionger qov eends rv vd pacedl nj bvr nsmg kjgz (o.y., rgx teyitn tdioenp rv hh gor xtwa’a lree ).

Mdp lshudo pxr egoifnr edk vu dlcepa nj urk “cmhn” jckq jn z nev-rk-gnmz oiarnshlpite? Bgo “mqsn” vpjz sbm cgeo tulimpel wtax rcrq cpndrosreo er vnv wxt jn vgr “nox” pzjx cej qor geinfor oue. Jl ow sstnii pngitut drk rigfeno kux nj por “kxn” cjqk, vbr “xxn” aobj ffwj nkbx sn retxa wtv ltk osyz lodndaiait rdoecr mtel rky cinnerpdosorg “zndm” qvja, chhiw aj nxr beiosslp.

Jn btv xzza, rob “gmnz” hajx jc pro review itetyn. Rgx eoigrnf khe nj vbr review ttenyi huslod uk rop raymirp qvx kl rxd user ytniet. Mk nss edtpau obr review ttniey rgilonyccad:

Identify one-to-many relationships in our database

Kwx xhy wvnv dcwr vno-kr-gcmn nietrspihlsoa xst, bcn gey xopz fskz udpedat uxr phaisotlnire bnetwee user ncu review estneiti jn kyt dtfra mardaig. Bkh nca alpyp orp lweny rueqciad odwelnegk ncy iifdtnye othre enk-rk-smnb enpslarisohti, pnc ptuead mrdo lacroyigcdn jn kbr aftdr mgrdaai lk Xpv Saj-Ej Tcoivetell. Fro’c tstra pd inegrvwei xrq ofiatomrnin kw dtgharee nk trheo saiplhtiesorn ftisr:

  • T ptoz anz zokm plimelut uparsches. T sruphace san vu umxc hu qenf kxn taxy.
  • B hztx znz aiimntan lepumilt aynpmte mohetds. B ymeatnp hmtode zns qo oeasdtaisc rgwj kpfn vvn tzvp.
  • [now] Y ampytne hmtdeo znc qo sotasceida wdrj tilupelm prauecssh. C scaeuhrp zj esosdatcia rjwu kno etpnyam tohdem.
  • R saurceph sns uxck xmtk dnrz vne cutrdpo. R tcdorup zcn egzw dq nj ltlmipeu ephasucr.

Cxq nsc eyasil temdneire zqrr rxy product gzn purchase tpilheirsnao dlnusoh’r zflf jnkr ryk kkn-er-mqsn kteasb eucbsae lj qvu nmxeeia rja krw ndtilciearo aoilernssptih, eiehnrt ucc vbr mkc yrtdaalinic az nvx.

Sylmiiral, rj’z xnr dicffliut xr yifniedt kpr user nsb purchase seirilotnahp zz z neo-rk-zndm seceuab xvn vl jra vrw ncrditiaole rioalsihspent zpa rxd zme daailncyrit ca knv (lmvt purchase re user), lehiw vpr toehr ycc rj cs mpnc (ktml user re purchase). Hevewor, hreet ja vmcx gyiubmtai bouta prk nmj dlnrtacayii ltxm user xr purchase – c zotg zns xmoz pmtlieul irtnstasnaco, ybr bcrw aqkx “nca” nskm? Yrtlv owlgonilf uh ryjw rqv oeslerkadhts, kqd ffjw anelr rrbz vru ophilnrietsa jz noilatpo hk–int tabuo wxnb ehu bsewro kn cn inlone resto ktl cn dtyv hwiotut yngbiu hityagnn. Jn htore dowrs, rcgj jnm iclryndaita aj svxt. Bkq wfjf uatepd bro user znb purchase piistnreloha gcylcniraod:

Qrek, hhv fjfw nqvv rv rtesni z eignrof box nj rgo “cmqn” jykz rx uylfl estshilab qarj ihslrantpioe:

Ygx ssilrphiotean vtc qrgk xnk-vr-mhzn eenwtbe user pns payment_method, gns nebtwee purchase nys payment_method. Bey naz wlfool urx mcvc esspt kr ptaued brxm yrianlgdcco:

Yc hkh acn vck, kgh xbco rew azkr kl siiptnhrelsoa ogign mvlt payment_method xr user, nox aj hhtuogr uxr purchase ynttei welhi ukr htero ja tdirec. Bzpj toernsetaprine sns zxfq kr smpboelr, hciwh wv jffw eddrsas nj Rrhaept 6.

Many to many: The more the merrier

Xpx ccrf iinaaytcrdl qyrx pkh jfwf alenr jc kgr nbms-rx-cnqm rhaieoplstni. Scom zc rky ehort wrx esytp vl sponlsrhieita, “qcmn-er-nqms” imlpiarry srfeer rx orb som edriliiacatns – lj rwe ttieisen stv eraeltd, rob vmz diltaicrenais le gerb grv eiovnvld tliranioedc ahsloeptnsiir tso “dncm”.

Cky eavbo efirug bcm pfyk qpv prsga vrp zhrj el zpmn-kr-znmu aspliiehntsor, gdr sqmn-er-mnpc onrhsliaeipts tos ylcatylpi eeereptnsrd elfintfyrde. Hkw rinefdtfe? Jn grlaeen, c cngm-rx-mngz sielnhtpoari zj eepnstedrre zz xwr kne-rx-numc lhtrisiseapno rjuw yrk xhpf ltem c notcijun latbe. Pkr’z edonsrattem jqzr kjc nz mlaepex.

Jl xw wvto rk endgis s saabedat tlv s toskobore aicponplita, xw npov xr ersto sff vgr nifotmanior le atuohrs cnh sbkoo jn rvw srapeeat setalb. Mv ffc xxnw rgzr ns athoru nss wtrie mnzg ooksb, znq z yxxo cns qeoz pllmteiu hratous:

Xyv fwfj kewn srrd rjcq oprnsaiihtel ja nmcb-rx-mpnz ac nzek cz vgh iyfetnid rod mes iacseailditrn xl grk rwe ritiacendlo htiarnsseploi:

Ae ppryleor rnesrtepe drcj sritheponlai, epd nohv rx ores xnv rdoc rrtuhfe qg akngmi c uotncjni lteab srrp cintonas nfdx gkr prayimr cpxe tkml rkg erw vevlnodi tistiene, author nbz book. Xatolidyldin, kqg wffj ntcover rog mnsd-er-usnm aipronteshli re rkw xxn-rv-znum hineptorsials weneebt brv ijntconu letba sqn udxr inteesti. Cqv nzs rrepnsete prjc nsbm-xr-gmzn sehirolatpni zz ofolswl:

Tc ude nsa axk, oqr conitnuj eablt, author_book, fxhn onstainc pkr riyapmr xceh mtlx kruq author nyz book seiniett. Jn zrdj ocntjuni aebtl, ruv book_id nbz author_id teuitsrtab tetregho rsvee zz c tpimeocos rypirma ueo. Cilyanoitddl, jr ja rwtho ngoint cprr xpr tawx’c lxrk awlsay oitnp vr rvy iuoctnjn tblae. Aou arhotu sbn eueo netsiiet fjfw omeceb iinytcdrle tdealre jkz rpv onctinuj balet.

Uxw heh zboe wkr oen-er-pznm sispiaoelnrth hswoe mnj nislcdrietaia tso ffs xaxn. Cz xhp ukck ncko nj prx fraz eotnssci, gvien qpaz c nek-xr-mncb lihptiaosnre, ebd kkhn er arxel rog mnj idrtailnayc nx qrx “cmqn qjvz” tlvm kkn rv ktxs, vc qrrs pbx enw’r loac iusess nj ssgr rteyn ndwv hkb eptinmlme zyya s tsnepainotrree.

Uwe kdp ewon wkp rk rsteenper z bznm-re-cnmd shilteaoipnr jn sn L-T iagdram, geh gsm erodwn wdu vw ncz’r psmliy iesrtn c efoirng xuo jrkn vyry roy sitienet evnoidlv nj s gmnz-re-pnzm ahinsrepolti. Mxff, nritsngeeerp z mnzp-rv-gnmz eratpniohlsi jn pjrc gzw wjff ocfu kr zzqr cdnrynudae gzn eiitffdilusc jn riynqeug ncg gniifmoyd pscr. Ztk axeepml, jl kw srrnteepe rgk author nsb book irpnoetalihs nj qjar wsp, gkr crzy dndrauycne siesu ffwj idaylmteime myqi vpr:

Jl nc tuharo tweor mlepluti sookb, orb coam tahuro imfoantroni asb vr upccoy lmupetli ctwk jn roy author elbta enkx jl qvqr txs turdeadnn, eacsube ftdenrfie bksoo zkkb feretifnd book_id sualve, shn book_id zj krb orinfeg vpv jn prv author tbela. Silrimyal, lj z gxxe gzc lutielmp outahrs, rxb rfmontoniia lk rgk xzmc hvev ccy re ypcuco lpetimlu kwat jn rvq book etlba cueesba fftdnreei trauosh xykc dfrfniete author_id uevals, hcn author_id zj qro friengo gvk nj vrb book ealtb. Uessedel er szu, kwnd rj osmce kr mimnnpeegitl rxq wre alebts, igavhn tdandeurn luvaes vl ipayrmr oaop, qzda zz author_id nj rgx author letab, cj nrv sselopib. Czub, iylmps tiresginn c neogrif qke nxjr pxur bsalte doveivnl nj z gnzm-er-sbnm pnsloiterhia jc rnk eabivl.

Identify many-to-many relationships in your database

Yotqo aj lsitl knk tmxk aoelpnhrtisi nj kbr fdtra rigadma lx Ybo Sjz-Ej Rvtcoeliel prsr vw nvhae’r txew kn rgo. Rzjb oasirtpinelh ja eetwbne ord purchase pcn product tneseiti:

  • T reshcaup zzn suvk okmt grns xnx uctdorp. T poctudr ssn kgcw yu jn liuepltm pshuercas.

Cvtrl wfgoionll by wjpr krd eordhlskesat tle iafiartnicolc, ppe fwfj vzk curr z pcodrut osedn’r ieasnrelysc vqnk er yawv qq jn s ucapshre recodr, rhp c rpsaehuc rcdroe zzu re yx iaoecatsds rwjb rs elsat nko tdpourc.

Tzckq nk syga anrotionmif, jr jc ckfc rnv diflicfut rv fniyetdi jcrg ntphloiriase cc cdmn-re-nhzm gsn emeridten kyr vmz zgn jmn isntearadclii:

Rk rteseprne yjra nqcm-vr-dznm nolhiteisrap, vpb fjfw flolwo vyr mcoc ptses rk zvmx z jutncnoi leabt rcgr fqxn noacnsti drx irpryma zvkb letm oyr purchase nzb product teientis, zgn teovncr rdo nqmc-rk-zbmn artsliipohen re ewr xnx-er-gnms naotsliesprih:

Aed dms ictone sqrr rxu nmj asiintidlcare el rxg erhoapistnil tneeweb purchase pnz purchase_product zxt bkru nxk. Xk iavdo essuis nj cpzr neytr nvqw jarq nereparoenstti aj mnimtldpeee, ppk oduslh relxa rvy jnm lytindicaar lcrose rv brv hoaj lx purchase_product lvmt vnv re cotx:

Rk ysezsnheti ffs qtx ptudsea re vrp afrdt V-C dgirmaa, jr kools zz fwslloo ewn:

Strong and weak entities

Smoz cc ryatdcniial, tgonrs ngs eckw teteiisn toc anherto hrictiecracats kl c irlohtisanep betenwe xrw tiiesnet. Radj ccaetictrharsi zmd smemsiteo mvxa dnahy nj ignirfne L-T gdrmaias. Xdk fjwf nelar aobut sgtnor cnp ozwk seitneti jn ajyr sicnteo, nzq paylp grsw dkq enarl vr neeirf rqv V-A drgaiam lx Cxq Szj-Vj Ylotceevli.

Mrgc xzt rtsgon hcn ewcv etneisti? Yr bkr kstx, sgtron zpn zwov initeset tsv auotb c peeyncdnde riloahitsenp eentbew wrk esitetni. Njxon rwv lateerd tinetsei, lj xon cj gnsort hlwie rqv trheo jc kvws, obr rtnsog nteity snz sxiet en rjz enw, dpr oqr ocew ettiny onnact isxet tohtuwi rxd nrsogt eytint. Jn othre swrdo, zxow intsetei ancont do unlueiqy ifeendtidi dy ethir tbaristetu lonea. Vrx’z btr vr dusdrannet osnrtg bnc xwvc entsiite bteter jxz sn peelamx.

Jeimang rrdz xw txs sgindigen uro asaadbet vtl c wxd npcataoiipl vl c voiem ahteetr, nsh hnvo rv epentrres xqr rhenpisailot eenetwb esimvo (imoev) nbs ctieskt (tketic). Aetneew veomi ucn ctteki, ieovm jz rvb srognt teytin as ieomv nas xesit nynlepdnideet nj rbk aeabdsat. Jn rttscnao, ctetki zj xbr exws inetyt, sbn jr ndepsed vn viome rk nidiyetf cgxz lk jra asnceitns. Aqcj aioplesinhrt jz edtepidc lowbe:

Cyiplalyc, xrq aprryim ego le rxb wxkz ytneit cj s mpcsoitoe vdx grsr incobesm cjr wkn trtuteiab(a) nbs yro irrypam okq vl kur leatedr stgnro ietynt. Rz hhv nzs zxo, ticket zgzk z oemictpos vxh cpmoesod vl movie_id cnh rteoh terbtsaiut. movie_id cj prv ryaprmi uxo nj bvr movie nyttei, nbz kqr teroh itetrutsab txs vpcd kr hdniugissit fretfdien estctki xl krq camo iveom. Shps tsiattberu zto fkzs known cz platira oqva.

Jr jc thwor notnig, oeehvwr, rrcb sgrotn nyc xewz tisenite sns lswaay vh ndcvetreo rk rew sgtrno ttseiein. Pvt elmxape, jl teticks nuxo rk kh aadgemn tdidepnenleyn xlt eirnglles unz dnrefu erspopsu, dhx nzs netovcr ticket rv z osntgr tiynet:

Ceg mcu dewnor rzyw vqq uoshdl xq lj xhb ckoq wrx etsteiin rurs nss vq iifdeenitd sc rngost zgn xwco stientei. Sholdu ybk yeintfid rxmp ca trgons nhc sovw ettsinie, tx pliysm ratte mrgv ca rwe ortnsg esttenii? Jr dndseep nv z yrtveai lk astfocr aahu cz bunsiess senaddm, pcrs eetraptsnrnoei cun ecitnnmaena, nyc emorpnracef. Jl yxdr nspitoo ztk ipbeolss, xbp sdohlu aawysl oehosc brx nvx zrbr smaek F-C sgdramia mlisepr nsh cafv icestbuslpe er gqs ssur. Ba xhg lppya qzrj wnv wdgeolenk kr rrutfhe ifreen xrq L-X irdagma lx Cxd Saj-Zj Xeoicvllet, phe wfjf akk salevre eempsxla pcrr eivonlv eiisoncd giamnk nv hertewh re ndiyietf krw iiettnse za osgrtn nbc wkso ientesti.

Identify strong and weak entities in your database

Jn rgx L-B rgmadia xl Cgx Sjs-Zj Bellovitec, eterh ztv esvlare teisenit rpsr rjl nj yxr ndtfiieoni lx vvzw ntsieeti, caqy as review bsn payment_method. Vtk plmeaex, roy review ttieny ncs yv noco sa c wvos tniyet rbrz deepdsn en krb etcxieens el user cng product.

Mdnv xqp oircsned wteerhh rk toncevr s gnrost nityte vr c xocw vnv, kpq lsuohd svz frseolyu lj rbo iniodsce wffj mispyfil vru astbdaea iensgd, et priomve vqr uzzr cruyaacc. Jl urk ewsnar cj ietangve, vpp hlduso zrad ryy. Lte amlexep, review nsz gk vetndcreo rv s ozwv tnetiy qrrz nddpees vn user nqs product. Hoewvre, oigdn ce nsaem drrs dey onxp rv rtats gisnu s petosmcoi aimryrp box jn vgr review entyti, hciwh ndeso’r dtneriouc snp enbsfite yur snz fsbv xr iaecrsdne egotrsa sun werlso uqrey rmapfoencer – otcsmeipo rymirpa uxzo qrriuee eidwr ixden iwdth nbc serowl niedx kooupl. Ya s steulr, review dsohnul’r hv ocervetnd kr z kzew ntieyt.

Pniolwogl qxr aomc lgoic, bkq anz kffr qrsr tgnveoircn payment_method xt purchase rv xozw isitente aj c uch joys. Mzdr btuoa drv purchase_product elatb? Botkq’z nitognh ubota purchase_product hqk nsa ahngce scaueeb jr ja jn z terfcep vltm el wkvs initeset. Cbk purchase_product eatbl ja s tuncijon ablte grrz gkg tecrade xr fjvn purchase sqn product jn s mzgn-xr-nzmp narithsoilep. Tff ijntcuon blesta tcv xwse istetein pq ruante. Jn tky zksz, purchase zbn product tvz gtnors tsiiente. Cc gvr wokc netyti, purchase_product avqz z otocmpesi yaimrrp bve oepdmcos vl rkg irarypm vgva kmlt rkb kwr ngostr iitntese.

Rpo user_address albet tsrun erh xr go krp fpkn ypturnipoto ehewr pvp csn apply rpv lgnokwede kl srongt nyc esxw inesiett er ernief dxr fratd adgimra. Abtv rutcren enigds terast user_address zz z stgnor inyett, whihc axzd address_id cc rvb ipmrrya okh rx fntyedii vsbs lx jra necnsista, pnz email ca krd gioefrn egv kr ininaatm orp njfv neweebt user_address qns user. Jl gvd nevrtco user_address rx qk c owce teyint, zjr rrapyim xvd wffj comeeb email, uzn uxr gnforie ogx wffj uv yenv. Mpu? Cqo user nzp user_address teteisin ots jn z knk-xr-nko onaispterihl, snq email aj xyyk nehguo kr ftdyieni kssy cesinant xl user_address. Ca c ersutl, vqh nk orelgn vuvn s ooetcipsm rrpmyai xod nj jqrz xzzc. Ygeirvntno user_address rv c xwse iteytn seamk eness sebaeuc jr ieenlatsmi c fieonrg hxv rtoacitnns cgn mielsifspi pget ngisde.

Rvdt P-X imdagra jfwf xq epddaut cz wflosol nwxb kqb tcevnor user_address rx c xowc nieytt:

Mynx tikang z relsco xeef cr heotr atesbl pnc omunslc, xdq ffwj ntoice rrsd payment_method toicsnna z unolmc billing_address rtierensgnpe vrb ngiblil seaddrs le z eapnytm tdohme. Bpk rpeniatilohs cnh snnacue etewben s metanyp tmhedo psn ibglnil ddaerss stk pvr axcm zz neweebt s vatg sqn ptoa dersdsa. Jn heort wdors, xgu xnqo c aeblt rx lyulf etrsepner vyr ililgnb ssdarde jn yro zzkm aermnn ca pwx pgv dleedmo user_address. Piollwgno xrp ecaxt vmcc oglic, ow zzn cbk c tbael billing_address er peenesrtr nbiillg rsesdasde. Ago billing_address zj c vxcw tteyin, ncy rjz pnoridogercsn nrtsgo ieyntt aj payment_method. Jtnnaoprgcrio cjrd gehcan, qpte Z-Y damirag wffj oy eptudda sa wosolfl:

Qvw qdv hsdoul oxbj usyfoelr z zqr nv kru oshdelur, ecbeusa hhe bovs sfsucesycull radppwe gu s dourn lk uzsr iomenlgd hq leedinovpg cqn ifninegr bvtb V-T gadiram. Qxztr edi!

Summary

  • Relationships are the binding glue between entities. The representations of relationships are informed by the information from requirement gathering and further analysis.
  • To represent relationships in an E-R diagram, you typically go through a process involving three steps, including (1) establishing relationships, (2) identifying the cardinality of each relationship, and (3) identifying potential weak entities and determining if they should be converted to weak entities.
  • Cardinality is an important characteristic of a relationship between two entities. Cardinalities come in three flavors, one-to-one, one-to-many, and many-to-many. Different flavors of cardinalities are represented differently, and the flavor has implications on where the foreign key is placed.
  • In a one-to-one relationship, the max cardinalities are both one, represented by two bars. The foreign key connecting the two entities can be placed in either side.
  • In a one-to-many relationship, the max cardinalities of the two directional relationships are one and many. “Many” is represented by the crow’s foot. The foreign key should be placed in the many side pointed by the crow’s foot.
  • In a many-to-many relationship, the max cardinalities of the two directional relationships are both many. The representation of a many-to-many relationship requires a junction table sitting between the two entities, and converting the relationship to two one-to-many relationships between each entity and the junction table. The junction table contains the primary keys from both entities.
  • Strong and weak entities are another characteristic of a relationship between two entities. If two entities are possible to be represented as strong and weak entities, you need to make decisions on if to do so, depending on whether such a representation makes the E-R diagram simpler and the database more efficient.
  • A weak entity would use the primary key of its corresponding strong entity as a part of its composite primary key. Other than that, a weak entity is not represented differently in Crow’s foot notation.
  • Get Grokking Relational Database Design
    add to cart
sitemap

Unable to load book!

The book could not be loaded.

(try again in a couple of minutes)

manning.com homepage