Txawm koj tabtom ua haujlwm nrog lub database uas tuav cov ntaub ntawv lossis ntau pua ntawm cov ntaub ntawv khaws tseg, tsim cov qauv kev tsim txhua yam tseem ceeb. Tsis tas yuav nws ua kom rov qab tau cov ntaub ntawv ntau dua, nws tseem yuav ua kom yooj yim ntxiv mus zuj zus ntxiv cov ntaub ntawv database yav tom ntej. Hmoov tsis, nws yooj yim heev rau lub caij nplooj zeeg hauv ob peb lub cuav uas ua rau nyuaj rau yav tom ntej.
Muaj tag nrho cov phau ntawv sau rau hauv qhov kev kawm ntawm normalizing ib tug database, tab sis yog tias koj tsuas yog zam cov kev yuam kev, koj yuav tsum yog txoj kev khiav kom zoo database tsim.
Database yuam kev # 1: Rov ua teb nyob rau hauv ib lub rooj
Ib txoj cai ntawm tus ntiv tes xoo rau kev tsim cov qauv zoo yog tsim kom pom cov ntaub ntawv rov qab thiab muab cov kab rov qab tso rau hauv lawv lub rooj. Rov ua dua cov lus teb nyob rau hauv ib lub rooj yog ib qho rau cov neeg uas tau tawm ntawm lub ntiaj teb ntawm spreadsheets, tab sis thaum spreadsheets yuav ncaj nraim los ntawm kev tsim, databases yuav tsum paub. Nws zoo li mus ntawm 2D rau 3D.
Luckily, teb rov ua dua yog qhov yooj yim rau qhov chaw. Cia li saib cov lus qhia ntawm no:
| Kev txiav txim | Product1 | Product2 | Product3 |
| 1 | Teddy Bears | Jelly taum | |
| 2 | Jelly taum |
Yuav ua li cas thaum muaj kev txiav txim muaj plaub khoom? Peb yuav tsum ntxiv lwm daim teb rau lub rooj los txhawb ntau tshaj peb yam khoom. Thiab yog tias peb tau ua ib daim ntawv thov nyob rau hauv lub rooj los pab peb cov ntaub ntawv tawm tswv yim, peb yuav tsum hloov nws nrog cov khoom tshiab. Thiab ua li cas peb nrhiav tau tag nrho cov kev txiav txim nrog Jellybeans nyob rau hauv qhov kev txiav txim? Peb yuav raug yuam kom nug txhua yam khoom teb rau hauv rooj nrog SQL daim ntawv uas yuav zoo li: XAIV * LOS NTAWM COV KHOOM MUAJ CAI Product1 = 'Jelly Beans' los yog Product2 = 'Jelly Beans' OR Product3 = 'Jelly Beans'.
Tsis txhob muaj ib lub rooj uas siv tag nrho cov lus qhia ua ke, peb yuav tsum muaj peb lub rooj uas txhua tus tuav cov ntaub ntawv sib txawv. Hauv qhov ua piv txwv no, peb yuav xav tau Orders lub rooj nrog cov ntaub ntawv hais txog qhov kev txiav txim nws tus kheej, Cov Khoom Cov Khoom nrog tag nrho peb cov khoom thiab cov ntsiav tshuaj ProductOrders uas txuas nrog cov khoom.
| Kev txiav txim | CustomerID | Hais Hnub Tim | Tag nrho |
| 1 | 7 | 1/24/17 | 19.99 |
| 2 | 9 | 1/25/17 | 24.99 |
| ProductID | Khoom | Suav |
| 1 | Teddy Bears | 1 |
| 2 | Jelly taum | 100 |
| ProductOrderID | ProductID | Kev txiav txim |
| 101 | 1 | 1 |
| 102 | 2 | 1 |
Daim ntawv qhia tias txhua lub rooj muaj nws tus kheej daim ID cim. Qhov no yog thawj qhov tseem ceeb. Peb txuas cov ntxhuav los ntawm kev siv lub ntsiab tseem ceeb ua ib qho tseem ceeb txawv teb chaws hauv lwm lub rooj. Nyeem ntxiv txog thawj hauv lub lag luam thiab cov yuam sij txawv teb chaws.
Database yuam kev # 2: Embedding a Table in a Table
Qhov no yog lwm yam yuam kev, tab sis nws tsis tas sawv ntsug tawm ntau li ntau dua ua teb rov qab. Thaum tsim tus zauv database, koj xav ua kom tag nrho cov ntaub ntawv nyob hauv ib lub rooj qhia nws tus kheej. Nws zoo li tus me nyuam qhov kev ua si txog qhov nws txawv. Yog hais tias koj muaj txiv tsawb, ib cov pos nphuab, ib lub txiv duaj thiab ib lub TV, lub TV ntaus tej zaum yog lwm qhov chaw.
Ua ke tib cov kab, yog tias koj muaj ib lub rooj muag khoom tib neeg, tag nrho cov lus qhia hauv lub rooj ntawv yuav tsum txheeb xyuas tshwj xeeb rau tus neeg muag khoom. Cov ntaub ntawv ntxiv uas tsis yog tshwj xeeb rau tus neeg muag khoom yuav yog lwm qhov chaw hauv koj cov ntaub ntawv.
| SalesID | Ua ntej | Lub xeem | Chaw nyob | Xov tooj | Chaw ua haujlwm | OfficeNumber |
| 1 | Sam | Elliot | 118 Main St, Austin, TX | (215) 555-5858 | Austin Downtown | (212) 421-2412 |
| 2 | Alice | Smith | 504 2nd Street, New York, NY | (211) 122-1821 | New York (Sab hnub tuaj) | (211) 855-4541 |
| 3 | Joe | Parish | 428 Aker St, Austin, TX | (215) 545-5545 | Austin Downtown | (212) 421-2412 |
Thaum lub rooj ntawv no yuav zoo li nws muaj feem xyuam rau tus neeg muag khoom, nws yeej muaj ib lub rooj nyob hauv lub rooj. Daim ntawv ceeb toom ua cas Office thiab OfficeNumber rov qab nrog "Austin Downtown". Yuav ua li cas yog tias chaw lis dej num hloov chaw? Koj yuav tsum tau hloov cov ntaub ntawv tseem ceeb rau ib daim ntawv hloov pauv, uas tsis yog ib qho zoo tshaj plaws. Cov teb no yuav tsum tau hloov mus rau lawv lub rooj.
| SalesID | Ua ntej | Lub xeem | Chaw nyob | Xov tooj | OfficeID |
| 1 | Sam | Elliot | 118 Main St, Austin, TX | (215) 555-5858 | 1 |
| 2 | Alice | Smith | 504 2nd Street, New York, NY | (211) 122-1821 | 2 |
| 3 | Joe | Parish | 428 Aker St, Austin, TX | (215) 545-5545 | 1 |
| OfficeID | Chaw ua haujlwm | OfficeNumber |
| 1 | Austin Downtown | (212) 421-2412 |
| 2 | New York (Sab hnub tuaj) | (211) 855-4541 |
Hom kev tsim no kuj ua rau koj muaj peev xwm ntxiv cov ntaub ntawv ntxiv rau chav ua hauj lwm rooj sab laj tsis tsim ib qho kev npau suav phem ntawm tus neeg muag khoom rooj. Xav txog tias ntau npaum li cas ua haujlwm nws yuav ua raws nraim li txoj kev qhov chaw, lub nroog, lub xeev thiab tus zip code yog tias tag nrho cov ntaub ntawv ntawd nyob hauv tus neeg muag khoom rooj!
Database yuam kev # 3: Muab ob los sis ntau cov ntaub ntawv mus rau ib qho chaw hauv ib qho
Muab cov ntaub ntawv hauv lub rooj sib tham tso rau hauv tus neeg muag khoom rooj tsis yog qhov teeb meem nrog rau cov ntaub ntawv ntawd. Qhov chaw nyob muaj peb daim ntaub ntawv: qhov chaw nyob, lub nroog thiab lub xeev. Txhua lub teb nyob rau hauv cov ntaub ntawv yuav tsum tsuas muaj ib daim ntawv xwb. Thaum koj muaj ntau daim ntawm cov ntaub ntawv hauv ib thaj teb, nws tuaj yeem nyuaj rau kev nug cov ntaub ntawv database.
Piv txwv, yog tias peb xav khiav ib cov lus nug rau txhua tus neeg muag khoom los ntawm Austin? Peb yuav tsum tau mus tshawb hauv qhov chaw nyob, uas tsis yog tsuas yog muaj peev xwm, tab sis tuaj yeem xa cov ntaub ntawv tsis zoo. Tom qab tag nrho, ua li cas yog muaj neeg nyob hauv txoj kev Austin hauv Portland, Oregon?
Ntawm no yog qhov txheej txheem yuav tsum zoo li:
| SalesID | Ua ntej | Lub xeem | Chaw Nyob1 | Chaw Nyob2 | Nroog | Xeev | Zip | Xov tooj |
| 1 | Sam | Elliot | 118 Lub Ntiaj Teb St | Austin | TX | 78720 | 2155555858 | |
| 2 | Alice | Smith | 504 thib St | New York | NY | 10022 | 2111221821 | |
| 3 | Joe | Parish | 428 Aker St | Apt 304 | Austin | TX | 78716 | 2155455545 |
Muaj ob peb yam uas yuav tau los saib ntawm no. Ua ntej, "Address1" thiab "Address2" yuav zoo li poob rau hauv qhov ua yuam kev rov ua dua.
Txawm li cas los xij, hauv qhov no lawv xa mus rau lwm cov ntaub ntawv uas muaj feem xyuam ncaj qha mus rau tus neeg muag khoom es tsis yog ib pawg neeg cov ntaub ntawv uas yuav tsum tau ua hauv nws lub rooj.
Tsis tas li ntawd, raws li ib qho yuam kev lawm kom tsis txhob muaj, paub tias qhov formatting rau tus xov tooj tau muab tshem tawm ntawm lub rooj. Koj yuav tsum tsis txhob khaws cia cov hom teb thaum txhua qhov ua tau. Nyob rau hauv cov ntaub ntawv ntawm tus xov tooj, muaj ntau txoj kev uas tib neeg sau tus xov tooj: 215-555-5858 los sis (215) 555-5858. Qhov no yuav ua rau nrhiav tus neeg muag khoom los ntawm lawv tus xov tooj los yog ua ib qho kev tshawb nrhiav ntawm tib neeg muag hauv ib cheeb tsam ntawm txoj kev nyuaj.
Database yuam kev # 4: Tsis siv Qhov tseem ceeb hauv lub hauv paus
Feem ntau, koj yuav xav siv tus xovtooj lossis tus lej lossis lwm tus lej txiav rau koj tus thawj qhov tseem ceeb. Koj yuav tsum tsis txhob siv cov lus qhia tseeb rau thawj qhov tseem ceeb txawm hais tias nws zoo li nws yuav ua tus cim zoo.
Piv txwv li, peb txhua tus muaj peb tus kheej tus kheej social security number, yog li siv tus lej social security rau ib tus neeg ua hauj lwm tus neeg ua hauj lwm tej zaum yuav zoo li lub tswv yim zoo. Tab sis thaum tsawg, nws muaj peev xwm txawm tias tus social security txawm hloov, thiab peb yeej tsis xav kom peb thawj qhov tseem ceeb hloov.
Thiab qhov no yog qhov teeb meem nrog kev siv cov ntaub ntawv raws li tus nqi tseem ceeb. Nws tuaj yeem hloov tau.
Database yuam kev # 5: Tsis Siv Cov Lus Teb Naming
Qhov no tej zaum yuav tsis zoo li ib qho loj thaum koj pib pib tsim koj database, tab sis thaum koj tau txais cov lus nug tiv thaiv cov database kom muab cov ntaub ntawv, muaj ib lub npe ua npe yuav pab tau thaum koj cim npe cov npe.
Cia li xav txog tias ntau npaum li cas uas cov txheej txheem yuav yog cov npe tau muab npe ua FirstName, LastName nyob rau hauv ib lub rooj thiab first_name, last_name nyob rau lwm lub rooj.
Ob txoj cai nrov npe nrov tshaj plaws yog thawj thawj tsab ntawv ntawm txhua lo lus hauv thaj chaw los sis sib cais cov lus uas siv ib qho underscore. Koj kuj tuaj yeem pom qee cov neeg tsim kev lag luam capitalizing thawj tsab ntawv txhua lo lus tsuas yog thawj lo lus: firstName, lastName.
Koj tseem yuav xav txiav txim siab siv cov npe los yog cov npe plural ntau tshaj plaws. Nws puas yog ib qho rooj txiav txim los yog Orders rooj? Nws puas yog ib tus neeg tau txais rooj lossis cov qhua tuaj noj mov? Ntxiv dua thiab, koj tsis xav ua kom daig nrog lub rooj txiav nyiaj thiab cov qhua tuaj.
Lub npe uas koj xaiv ntawd tsis tseemceeb raws li txoj haujlwm uas tau xaiv thiab ua raws li lub rooj sablaj.
Database yuam kev # 6: Improper Indexing
Indexing yog ib yam ntawm cov yam khoom nyuab tshaj plaws, kom tshwj xeeb rau cov neeg tshiab ntawm cov qauv tsim. Txhua tus thawj tuav thiab txawv teb chaws yuav tsum tau muab khi. Cov no yog cov txuas cov ntxhuav ua ke, yog li tsis muaj qhov ntsuas, koj yuav pom kev tsis zoo heev ntawm koj cov ntaub ntawv.
Tab sis dab tsi dhau lawm ploj yog lwm teb. Cov no yog cov "THAUM" liaj teb. Yog tias koj nquag mus nqes koj qhov kev tshawb nrhiav los ntawm kev siv ib daim teb nyob rau hauv ib qho chaw twg, koj xav xav txog qhov muab qhov kev ntsuas rau ntawm qhov chaw ntawd. Txawm li cas los xij, koj tsis xav kom muaj kev ntxhov ntxhuav ntau tshaj qhov rooj, uas tuaj yeem ua rau kev ua haujlwm.
Yuav txiav txim siab li cas? Qhov no yog ib feem ntawm cov kos duab ntawm tus qauv tsim. Muaj tsis muaj kev txwv ntau npaum li cas koj yuav tsum tau muab tso rau hauv ib lub rooj. Feem ntau, koj xav tau los xyuas txhua lub teb uas nquag siv rau hauv ib qho chaw twg. Nyeem ntxiv txog kev txhim kho koj cov database kom zoo.