MySQL-tietokannan suunnittelu verkkosovelluksille

Kun kolme työntekijää varaa tavaraa rinnakkain aamulla, asiakas tarkistaa toimituksen tilan ja hallinto laatii laskun, sovelluksen laatu ei näy sen ulkoasusta. Se osoittautuu siitä, näkevätkö kaikki täsmälleen saman, oikean datan tilan. MySQL-tietokannan suunnittelu verkkosovellukselle ei siis tarkoita taulujen luomista mahdollisimman nopeasti. Se tarkoittaa todellisten työnkulkujen ymmärtämistä riittävän tarkasti, jotta data pysyy luotettavana myös kuormituksen alla, virhetilanteissa ja liiketoiminnan kasvaessa.

Erityisesti sisäisissä alustoissa, varasto- ja tilausprosesseissa tai asiakkaille suunnatuissa portaaleissa tietokantaa käsitellään usein liian myöhään. Ensin rakennetaan käyttöliittymä, sitten lisätään kenttiä, ja sen jälkeen poikkeuksia. Se toimii prototyypille. Tuotannossa tämä johtaa päällekkäisiin tietojoukkoihin, epäselviin tiloihin ja raportteihin, joihin kukaan ei enää täysin luota.

MySQL-tietokannan suunnittelu verkkosovelluksille: aloita työnkulusta

Ensimmäisen luonnoksen ei pitäisi alkaa sarakkeiden nimistä, vaan konkreettisesta työtilanteesta. Otetaan esimerkiksi tavaran vastaanotto: toimitus saapuu, se kohdistetaan toimittajaan ja tilaukseen, määrät tarkistetaan, varastopaikka osoitetaan, ja varasto muuttuu. Toiminnasta riippuen tämä prosessi vaatii lisäksi valokuvia, laaduntarkastuksen, pidätystilan tai jäljitettävän korjauksen. Tästä työnkulusta syntyvät toiminnalliset objektit. Tyypillisiä esimerkkejä ovat tuotteet, toimittajat, tilaukset, rivit, varastopaikat, varastoliikkeet, ja käyttäjät.

Ero objektin ja tapahtuman välillä on ratkaiseva. Tuote kuvaa, mikä jokin on. Varastoliike dokumentoi, että määrä muuttui tietyssä paikassa tiettynä ajankohtana. Molempien sekoittaminen yhteen tauluun johtaa nopeasti jäljitettävyyden menetykseen.

Muutama vaikea kysymys auttaa jokaisen objektin kohdalla: mikä on yksilöllinen identiteetti? Mikä tieto saa muuttua? Kuka saa muuttaa sitä? Mitkä tiedot on säilytettävä historiallisesti? Ja mitä sääntöjä sovelletaan, kun kaksi henkilöä työskentelee samanaikaisesti? Nämä kysymykset estävät myöhemmän improvisoinnin paremmin kuin pitkä lista väitetysti täydellisiä tietokantakenttiä.

Tietomallin tulisi ilmaista sääntöjä

Tietokanta ei ole pelkkä säilytyspaikka lomakesyötteille. Sen pitäisi itse valvoa keskeisiä sääntöjä. Jos jokaisen varastoliikkeen on kuuluttava täsmälleen yhteen tuotteeseen ja yhteen varastopaikkaan, viiteavaimet kuuluvat malliin. Jos ulkoinen tilausnumero saa esiintyä vain kerran per vuokralainen, tarvitaan yksilöllinen indeksi. Jos rivin ei koskaan pitäisi olla olemassa ilman otsikkotilausta, tämä suhde on mallinnettava selkeästi.

MySQL 8 InnoDB:llä tarjoaa tähän vankan perustan: transaktiot, viiteavaimet, lukitusmekanismit, ja johdonmukaiset muutokset useissa tauluissa. Kun kirjoitetaan liikettä, nykyistä varastoa, ja tarkastuslokia tavaran vastaanoton kirjauksen aikana, tämän pitäisi tapahtua yhtenäisenä transaktiona. Jos yksi vaihe epäonnistuu, mitään puolivalmista toimintoa ei saa jäädä jäljelle.

Kaikki säännöt eivät kuitenkaan kuulu tietokantaan. Hyväksynnät, monimutkainen hinnoittelulogiikka, tai roolikohtaiset prosessivaiheet sijoitetaan usein paremmin sovelluslogiikkaan, koska ne muuttuvat toiminnallisesti nopeammin. Raja on pragmaattinen: säännöt, joiden rikkominen vahingoittaa dataa pysyvästi, tulisi turvata mahdollisimman lähellä dataa. Säännöt, jotka muuttuvat usein tai riippuvat vahvasti kontekstista, vaativat hyvin testattua sovelluskoodia.

Älä sekoita historiaa nykyisiin arvoihin

Yleinen virhe on tallentaa vain nykyinen varasto tai nykyinen tila. Se riittää, kunnes joku kysyy, miksi määrä muuttui eilen tai kuka palautti tilauksen. Operatiivisille järjestelmille liikkeiden tai tapahtumien historia on usein arvokkaampi kuin yksi ylikirjoitettava kenttä.

Tämä ei tarkoita, että jokainen klikkausliike pitäisi kirjata pysyvästi. Liiketoiminnan kannalta olennaiset muutokset tulisi kirjata: tilamuutokset, määrämuutokset, korjaukset, hyväksynnät, ja osoitukset. Hyvä tarkastusmerkintä sisältää aikaleiman, käyttäjän tai järjestelmäprosessin, edellisen ja uuden arvon, sekä ymmärrettävän syyn, kun työnkulku sitä vaatii. Tämä mahdollistaa virheiden selvittämisen ilman sähköpostien, paperilistojen, tai tietokantavarmuuskopioiden läpikäymistä.

Valitse avaimet, tietotyypit, ja nimeämiskäytännöt tietoisesti

Tekniset päätökset vaikuttavat pieniltä, mutta muovaavat ylläpitoa ja integraatioita vuosien ajan. Sisäisille perusavaimille automaattisesti osoitetut BIGINT-arvot ovat usein maltillinen, helposti hallittava valinta. UUID:t voivat olla järkeviä, kun data syntyy offline-tilassa, useat järjestelmät kirjoittavat itsenäisesti, tai ulkoisten rajapintojen ei pitäisi paljastaa peräkkäisiä tunnisteita. Ne kuitenkin vievät enemmän tallennustilaa ja vaativat hieman enemmän huomiota indeksien ja lajittelun kanssa.

Rahamäärät tulisi tallentaa DECIMAL-muodossa, ei FLOAT- tai DOUBLE-muodossa. Määrät tarvitsevat myös toiminnallisesti sopivan tarkkuuden: tuotemäärät ovat usein kokonaislukuja, kun taas painot ja pituudet eivät ole. Aikaleimoja tulisi käsitellä yhtenäisesti, mieluiten sisäisesti UTC-ajassa, kun taas käyttöliittymä näyttää toiminnon paikallisen aikavyöhykkeen. Erityisesti vuoronvaihdoissa ja kesäajassa tämä estää vaikeasti havaittavia poikkeamia.

Nimien tulisi myös olla tylsiä ja yksiselitteisiä. order_items tai inventory_movements ovat hyödyllisempiä kuin luovat lyhenteet, jotka vain alkuperäinen projektitiimi ymmärtää. Johdonmukaiset yksikkö- tai monikkomuodot ovat vähemmän tärkeitä kuin johdonmukaisuus itsessään. Yhtä järkeviä ovat kentät kuten created_at, updated_at, ja tarvittaessa deleted_at. Pehmeä poisto ei kuitenkaan ole vakiovelvoite. Oikeudellisesti tai toiminnallisesti relevanttien tietueiden kohdalla siisti peruutus on yleensä parempi kuin näkymättömästi poistettu tietojoukko.

Indeksit seuraavat todellisia kyselyitä, eivät arvailua

Indeksi voi nopeuttaa hakua valtavasti, mutta tekee kirjoitustoiminnoista monimutkaisempia ja kuluttaa tallennustilaa. Siksi "indeksi joka kenttään" ei ole strategia. Tärkeimmät kyselyt tulisi määrittää varhain: asiakkaan avoimet tilaukset, tuotteen liikkeet tietyllä ajanjaksolla, varasto varastopaikoittain, tai äskettäin muutetut tietueet käyttöliittymälle.

Yhdistettyjen indeksien järjestyksellä on tässä merkitystä. Jos sovellus hakee säännöllisesti tenant_id:n, status:n, ja created_at:n perusteella, yhdistetty indeksi juuri tässä järjestyksessä on usein järkevä. Sopiiko se todella, näkyy suoritussuunnitelmasta EXPLAIN:n avulla, ei mutu-tuntumasta. Tietokannoista ei tehdä nopeita näyttävillä tempuilla, vaan havainnoitavilla kyselyillä, sopivilla indekseillä, ja realistisesti testatuilla datamäärillä.

Kasvaville tauluille kannattaa selkeä säilytysstrategia. Täytyykö teknisten lokien olla ensisijaisessa tuotantotietokannassa viisi vuotta? Ei välttämättä. Liiketoimintatietueet, liikkeet, ja tarkastustodisteet vaativat eri säilytysajat kuin virheenkorjaustiedot. Arkistointi ei ole merkki heikosta järjestelmästä, vaan tietoinen operatiivinen päätös.

Monikäyttäjätoiminta vaatii transaktioita ja selkeitä tiloja

Verkkosovelluksessa useat pyynnöt käyttävät samaa dataa samanaikaisesti. Tämä on normaalia päivittäisessä varastotoiminnassa, ei poikkeus. Kaksi työntekijää voi varata saman varaston, kun tuonti luo uusia tilauksia. Ilman transaktioita ja kohdennettua lukitusta on riski kadonneista muutoksista tai negatiivisista varastoista, jotka tulevat ilmi vasta viikkoja myöhemmin.

Kriittisissä toiminnoissa tulisi olla selvää, mitä dataa luetaan ja kirjoitetaan transaktion sisällä. Joskus atominen päivitys riittää, kuten varasto, joka muuttuu vain, jos saatavilla oleva määrä on riittävä. Muissa tapauksissa rivilukko on järkevä, jotta toiminto voi tarkistaa datan tilan hallitusti ja muokata sitä sen jälkeen. Pitkät transaktiot ovat sen sijaan ongelmallisia: ne estävät muuta työtä ja lisäävät konfliktien riskiä.

Yhtä tärkeä on rajattu joukko toiminnallisia tiloja. Tilauksen ei pitäisi olla samanaikaisesti "avoin", "osittain toimitettu", ja "manuaalisesti käsitelty" ristiriitaisten kenttien ylläpidon vuoksi. Määritellyt tilasiirtymät yksinkertaistavat käyttöliittymiä, raportteja, ja automaatioita. Poikkeuksia voidaan sallia, mutta ne tulisi nimetä ja dokumentoida.

Suunnittele tietoturva, vuokralaiset, ja toiminta alusta alkaen

Sovelluksen tulisi käyttää MySQL:lle omistettua tietokantakäyttäjää, jolla on minimaaliset oikeudet. Kirjoitusoikeus verkkosovellukselle ei tarkoita, että tämän käyttäjän täytyy pystyä poistamaan tauluja tai muuttamaan käyttöoikeuksia. Ylläpitotilit eivät kuulu tuotannon konfiguraatiotiedostoihin eivätkä koskaan repositorioon.

Kun useat asiakkaat, toimipisteet, tai yritykset työskentelevät yhden sovelluksen sisällä, vuokralaiseristys on arkkitehtoninen päätös, ei jälkikäteen lisätty suodatusehto. Jaettu tietokanta, jossa on tenant_id, voi olla tehokas ja helposti ylläpidettävä, mutta vaatii johdonmukaiset tarkistukset jokaisessa kyselyssä ja selkeät säännöt indekseille. Erilliset tietokannat tarjoavat vahvemman eristyksen, mutta lisäävät työtä päivityksissä, arvioinneissa, ja toiminnassa. Mikä vaihtoehto sopii, riippuu tietosuojavaatimuksista, datamäärästä, ja liiketoimintamallista.

Varmuuskopiot ovat varmuuskopioita vasta, kun palautus on testattu. Tarvitaan määritelty rytmi varmuuskopioinnille, säilytykselle, ja palautukselle. Samoin tallennustilan, hitaiden kyselyiden, ja epäonnistuneiden töiden valvonta yhdessä dokumentoitujen päivitysten kanssa kuuluu järjestelmään. MySQL 8, PHP 8.4, ja moderneja verkkosovelluksia voidaan käyttää hyvin pitkällä aikavälillä, jos riippuvuudet, käyttöoikeustiedot, ja käyttöönottovaiheet eivät ole vain yhden kehittäjän päässä.

Järkevä suunnitelma ennen ensimmäistä tuotantopäivää

Ennen toteutusta pitäisi olla olemassa kompakti tietomalli esimerkkityönkuluilla. Tämä sisältää keskeiset taulut ja suhteet, tilasäännöt, käyttöoikeudet, odotetut kyselyt, rajapinnat, ja konseptin varmuuskopioille ja tarkastuslokeille. Tämän suunnitelman ei tarvitse olla sata sivua pitkä. Sen on vangittava päätökset, joiden korjaaminen myöhemmin olisi kallista.

softify.pro:ssa tietokannan suunnittelu alkaa siksi ihmisistä, jotka varaavat, tarkistavat, keräilevät, tai ratkaisevat poikkeuksia. Jos olemassa oleva taulukkolaskenta kuvaa luotettavasti hallittavaa prosessia, se voi pysyä oikeana ratkaisuna. Jos useat ihmiset työskentelevät samanaikaisesti, tietueita syntyy, ja virheiden on oltava jäljitettäviä, tietokanta ansaitsee sen sijaan saman suunnitteluvaivan kuin käyttöliittymä. Paras arkkitehtuuri on lopulta se, joka yksinkertaistaa työpäivää ja jota voidaan edelleen muuttaa läpinäkyvästi kahden vuoden kuluttua.