Keskustelu: Nettisivujen teko: MySQL: Millaista id:tä kannattaa käyttää?

Eli PHP ja muutama muu koodikieli on hyvin jo hallussa, mutta tietokantasuunnittelu on edelleen ihan lapsen kengissä. Tästä johtuen en ole mitään isompia projektejakaan koskaan uskaltanut käynnistää, joten päätimpä vihdoin tulla kysymään täältä tätä yhtä suurta kysymysmerkkiä.

Käytän MySQL:ää.

Kuvitteelliset tietokantataulut verkkokaupalle:

CREATE TABLE products (
	productID integer unsigned primary key auto_increment,
    photoID int not null,
    discountID int not null,
    reviewID int not null,
    availableID int not null,
    name varchar(128) default null,
    tags varchar(1000) default null,
    category varchar(128) default null,
    description varchar(2000) default null,
    price decimal() default null
);

CREATE TABLE products (
	ID integer unsigned primary key auto_increment,
    productID char(10) default null,
    name varchar(128) default null,
    tags varchar(1000) default null,
    category varchar(128) default null,
    description varchar(2000) default null,
    price decimal() default null
);

Eli kumpi tapa olisi parempi, kun yhden taulun tulisi olla kontaktissa useamman muun taulun kanssa? Ensimmäisessä siis viitataan muihin tietokantoihin noiden kyseisten tietokantojen "productID":tä vastaavalla itsestään luotavalla ID:llä. Jälkimmäisessä taas luodaan erillinen 10 merkin generoitu ID, joka esiintyy muissa tietokantatauluissa esim:

CREATE TABLE discounts (
    id integer unsigned primary key auto_increment,
    productID int not null,
    startDate
    expirationDate
    ...
);

Ja vielä samaan aiheeseen: Miksi joissakin verkkosovelluksissa on tosiaan erikseen miltein jokaisessa taulussa itsestään täyttyvä ID ja sen lisäksi vielä tuo erillinen "productID", joka on sitten erikseen generoitu merkkijono jolla pidetään yhteyttä eri taulujen välillä?

Onko suositeltavaa, että jokaisessa taulussa on erillinen itsestään kasvava ID, vai kannattaako se turhissa kohden jättää pois? Netissä on nähtävillä kaikenlaista tietokantaa ja nyt kaipaisin vähän faktaa siitä, mikä tosiaan on hyvä ja mikä ei.

Jos joku tietää niin voi vastauksen ohjessa toki laittaa linkin johonkin lyhyeeseen perusoppaaseen tietokantasuunnittelun perusteista (ei siis MySQL:stä).

Teen yleensä taulut jälkimmäisellä tavalla, tosin ilman productID-kenttää, koska pelkkä ID-kenttä riittää. Muista tauluista voi viitata suoraan ID-kenttään.

Toki jos haluat jostain syystä tuotteille erillisen käyttäjälle näkyvän productID:n, niin erillinen kenttä voi olla perusteltu.

Jälkimmäisen tavan etuna on, että toisessa taulussa voi olla monta riviä, jotka viittaavat products-tauluun. Esimerkiksi voisi olla, että tuotteeseen liittyy monta kuvaa, jolloin tulisi ongelmia yksittäisen photoID-kentän kanssa.

Automaattisesti kasvava ID-kenttä on kätevä, koska sen avulla voi yksilöidä jokaisen taulun rivin. Kentälle on usein jossain vaiheessa käyttöä, vaikka se tuntuisi ensin turhalta.

No tämä selvensi juuri sopivan verran. Kiitti vastauksesta. :)

Lisäys:

Tulipa mieleen vielä kysyä, että jos on kyse käyttäjätietokannasta niin kumpi on kannattavampaa:

Käyttää pelkää ID:tä ja käyttäjänimeä, vai eritellä ID, käyttäjänimi ja vielä erillinen userID. Tätä jälkimmäistä tapaa olen nähnyt eniten käytössä.

Jotenkin minusta tuntuu, että "olet nähnyt eniten käytössä" tarkoittaa yhtä ainoaa järjestelmää, johon olet tutustunut.

productID lienee silloin perusteltu, jos kaupassa halutaan käyttää ulkopuolisen rekisterin tuotenumeroita, esim. maahantuojan tuotenumeroita yms.

Tällöinkin käyttäisin skripteissa kaupan sisällä toiminnallisuuksiin aina silti tuota juoksevaa numeroa, mutta asiakkaille näytettäisiin nuo productID-tuotenumerot kaupan sivuilla, ostoskoreissa ja kuiteissa.

The Alchemist kirjoitti:

Jotenkin minusta tuntuu, että "olet nähnyt eniten käytössä" tarkoittaa yhtä ainoaa järjestelmää, johon olet tutustunut.

Varsin tyhjentävä vastaus, onnitteluni siitä!

problerino kirjoitti:

Tulipa mieleen vielä kysyä, että jos on kyse käyttäjätietokannasta niin kumpi on kannattavampaa:

Käyttää pelkää ID:tä ja käyttäjänimeä, vai eritellä ID, käyttäjänimi ja vielä erillinen userID. Tätä jälkimmäistä tapaa olen nähnyt eniten käytössä.

En tiedä tarkalleen, mitä tarkoitat erittelyllä, mutta samaan tauluun suosittelen laittamaan ID:n ja käyttäjänimen. Ei ole mitään hyötyä laittaa eri tauluihin(jos oikein ymmärsin) ID:tä ja käyttäjänimeä. KättäjäID:lle(userID) en löydä mieleni sopukoista yhtään perusteltua käyttötarkoitusta.

userID voisi olla siinä tilanteessa perusteltu, jos ID on jonkun muun palvelun ID esim. Facebook, Twitter. Jos siis palvelu olisi esim. foorumialusta, tuolla userID:llä saisi vaikka omaan profiiliin tietoja ulkoisesta palvelusta. Mielestäni kylläkin silloin kenttä voisi olla nimetty vaikka fbID tai twitterID yms.

The Alchemist kirjoitti:

Jotenkin minusta tuntuu, että "olet nähnyt eniten käytössä" tarkoittaa yhtä ainoaa järjestelmää, johon olet tutustunut.

Aijai, ajattelin liian aikaisin, että Ohjelmointiputkasta olisi tullut mukava paikka jossa olisi mukavia ihmisiä. ;D Olen tutustunut yli kymmeneen tietokantaratkaisuun. Se ei ole paljon, mutta mielestäni täysin riittävästi että voin käyttää kyseisenlaista ilmausta.

dartvaneri kirjoitti:

En tiedä tarkalleen, mitä tarkoitat erittelyllä, mutta samaan tauluun suosittelen laittamaan ID:n ja käyttäjänimen. Ei ole mitään hyötyä laittaa eri tauluihin(jos oikein ymmärsin) ID:tä ja käyttäjänimeä. KättäjäID:lle(userID) en löydä mieleni sopukoista yhtään perusteltua käyttötarkoitusta.

Ymmärsit väärin, mutta se johtunee epäselvästä selityksestä. Tarkoitin siis tätä:

CREATE TABLE users (
id, -- (yksilöi kyseisessä taulussa olevat rivit, liukunumero)
userID, -- (käytetään muissa tauluissa esiintyvissä viittauksissa)
username -- (ainoa joka näkyy sivustolla)
);

Näen itse tässä kaksi vaihtoehtoa: joko käyttää yllä olevaa jaottelua TAI jättää userID pois, jolloin sekä sivustolla että muissa tauluissa viittaus käyttäjään tapahtuu pelkän käyttäjänimen avulla. Jotenkin itsestä tuntuu, että olisi tyhmää käyttää käyttäjänimeä tunnisteena ja säilöä sitä monessa taulussa, mutta toisaalta userID on tällöin tavallaan "ylimääräinen" tunniste (rasite tietokannalle?), jota ilmankin (kai) pärjäisi?

Lisäys: Muitakin hyviä lisäyksiä joo tullut. :) Kiitos niistä kaikista.

Mod. korjasi SQL-kooditagit (ja kommentit)!

problerino kirjoitti:

Olen tutustunut yli kymmeneen tietokantaratkaisuun.

Ovatko ne laadukkaita järjestelmiä vai amatöörien viritelmiä? Oletko varmasti lukenut oikein? Itse en ainakaan muista nähneeni järjestelmiä, joissa luotaisiin jokaiselle käyttäjälle kaksi id:tä (id ja userID). Sehän olisi täydellisen typerää. Käyttäjän id voi kyllä olla nimeltään id tai userID tai user_id, ja tämä on vain tyylikysymys. Ainoa järkevä selitys monen id:n käytölle on tuo, jonka Lebe80 jo mainitsi: toinen id voi viitata johonkin ulkopuoliseen kohteeseen.

problerino kirjoitti:

id, userID, username

Näen itse tässä kaksi vaihtoehtoa: joko käyttää yllä olevaa jaottelua TAI jättää userID pois, jolloin sekä sivustolla että muissa tauluissa viittaus käyttäjään tapahtuu pelkän käyttäjänimen avulla.

Kummallinen tulkinta. Itse näen yhden järkevän vaihtoehdon: tauluun laitetaan vain id ja käyttäjänimi, muissa taulussa viittaukseen käytetään tätä id:tä, sivuston logiikassa myös käytetään id:tä, ja ainoastaan käyttäjälle näkyvissä kohdissa eli sivujen osoitteissa ja lomakkeissa esiintyy käyttäjänimi. Tekstidataa (kuten käyttäjänimeä) ei pidä käyttää viittauksiin taulujen välillä. Kahta uniikkia id:tä (id ja userID) ei yleensä pidä olla.

Laadultaan varmaan aikalailla laidasta laitaan. Suurin osa ehdottomasti jonkuntasoisten harrastelijoien luomuksia, ettei kenenkään ammattilaisten.

Kerta kahta ID:tä ei missään nimessä kannata luoda, onko jotakin perustelua sille, miksi joissakin ohjelmissa käytetään ID:nä generoitua merkkijonoa ja joissakin vain pelkkää liukunumeroa? Mikä käytännön ero näiden tapojen välillä on, vai onko kyseessä vain mielipideseikka?

Äkkiseltään tulee mieleen muutama perustelu

- jostain syystä halutaan piilottaa käyttäjän todellinen id. Julkinen juokseva numerohan paljastaa suoraan paljonko taulussa on rivejä ja kenties joskus on perustetta salata tämä. Ei ole kokemusta, mutta voisin kuvitella että esim verkkokauppasoftat piilottelisivat noita liukuvasti kasvavia lukuja, ettei paljasteta kilpailijoille ihan tosta vaan tietokannan rivimääriä.
- jo mainittu viittaus mahdolliseen ulkoiseen käyttäjä-Id:hen. Joko userId tuodaan ulkoisesta järjestelmästä, tai sille halutaan jättää softan puolesta optio.

Käytännössä kahden id:n pitämisellä ei ole varmaan paljonkaan vaikutusta mihinkään. Toki koodia tulee vähän enemmän, kun esim. lisätessä rivin tuollaiseen 2:n id:n tauluun joudut tarkistamaan onko generoitu userId uniikki. Tai vaihtoehtoisesti tekemään virheenkäsittelyn, jos rivin lisäys epäonnistuu johtuen dublikaattiarvosta, kun userId on kannassa määritetty uniikiksi.

problerino kirjoitti:

Miksi joissakin ohjelmissa käytetään ID:nä generoitua merkkijonoa?

Taulujen koon piilottaminen on ollut syynä parissa paikassa. Jotkut pelkäävät, että uskottavuus kärsii, jos linkeissä näkyy pieniä numeroita kuten "product/24/" tai "user/11/". Linkkien arvaaminen on myös helppoa, ja joku voi huvikseen selvittää vaikkapa kuinka suuri osa tuotteista on edelleen tarjolla kokeilemalla kaikkia numeroita järjestyksessä.

Toisin kuin kasvavat luvut, GUIDit ovat uniikkeja myös taulujen välillä. Tästä voi olla hyötyä, jos koodi käsittelee useammasta eri taulusta tulevia objekteja samana joukkona vaikkapa cachessa. GUIDien käyttö voi mahdollisesti suojella myös typoja vastaan monimutkaisissa queryissä, joissa esiintyy monen eri taulun id:itä, koska väärien arvojen vertailu ei ole koskaan vahingossa tosi.

Kolmas asia mikä tulee mieleen on replikointi, jota auto-increment voi hankaloittaa.

jlaire kirjoitti:

Taulujen koon piilottaminen on ollut syynä parissa paikassa. Jotkut pelkäävät, että uskottavuus kärsii, jos linkeissä näkyy pieniä numeroita kuten "product/24/" tai "user/11/". Linkkien arvaaminen on myös helppoa, ja joku voi huvikseen selvittää vaikkapa kuinka suuri osa tuotteista on edelleen tarjolla kokeilemalla kaikkia numeroita järjestyksessä.

Kumpikaan noista ei kelpaa syyksi. Myös tuon toisen id-tunnisteen täytyy olla uniikki, joten ei ole mitään järkeä käyttää sekventiaalista id:tä "päätunnisteena" ja sen jälkeen generoida pseudosatunnaista id:tä siihen oheen. Sillä sekventiaalisella id:llä ei yksinkertaisesti ole mitään sellaista käyttötarkoitusta, mihin tuo toinenkin tunnistei kelpaisi.

Jos ainoa syy olisi pienten numeroiden piilottaminen, niin kertokaa herraisä tällaiselle ihmiselle id-laskurin oletusarvon muuttamisesta ykkösestä vaikka lukuun 512356!

Useimmissa palveluissa on nykyään siirrytty jo selkokielisiin url-tunnisteisiin ("slugit"), jolloin numeerisia id-tunnisteita ei välttämättä näy käyttäjälle yhtään missään.

Kysymys oli "miksi joissakin ohjelmissa käytetään ID:nä generoitua merkkijonoa". Aiemmissa töissä on joskus näin tehty (siis GUID luvun sijaan, ei sen lisäksi), ja päätin kertoa miten valintaa oli minulle perusteltu.

Alkemistimaisempi ja kieltämättä paljon parempi vastaus olisi toki ollut "ei missään järkevässä paikassa, tyhmä kysymys, lolnoob!!!!!".

Laskurin aloittaminen mielivaltaisesta paikasta on aika läpinäkyvä purkkaviritelmä. Jos haluaa, etteivät id:t anna mitään tietoa, niiden pitää olla satunnaisia ja riippumattomia; ja jos sille linjalle lähtee, voi aivan hyvin käyttää vaikkapa niitä GUIDeja lukujen sijaan.

problerino kirjoitti:

Mikä käytännön ero näiden tapojen [juoksevan numeroinnin ja muun tunnisteen] välillä on, vai onko kyseessä vain mielipideseikka?

Eräs käytännön ero on siinä, että tietokanta luo id:n järjestyksessä automaattisesti, kun taas muun tunnisteen generointiin tarvitaan ylimääräistä koodia ja varmistuksia törmäysten estämiseksi. Lisäksi tavallisen kokonaisluvun käsitteleminen on hieman nopeampaa kuin pidempien binääritietojen (puhumattakaan merkkijonoista).

Älä viitsi puhua liukunumerosta, ei sellaista termiä olekaan. Aiheutat vain hämmennystä, nimittäin liukuluku tarkoittaa ihan muuta. Yleensä puhutaan juoksevasta numeroinnista.

Se, tarvitaanko esim muun tunnisteen generointiin ylimääräistä koodia, on enemmänkin kieliriippuvainen asia kuin fakta. Esim C#:ssa GUID generoituu halutessa automaattisesti uuden rivin id:ksi (entity framework, nHibernate).

Ja tuskin tuota satunnaista id:n generointia tarvitsee tehdä kuin pelkästään siinä vaiheessa, kun lisätään tietokantaan uusia rivejä. Jos kyseessä on tuote, tapahtuu tuo generointi tuotteen lisäyksen yhteydessä. Jos taas käyttäjätunnus, niin generointi tapahtuu rekisteröityessä. Jos siinä kuluu sekuntikin (todellisuudessa puhutaan sen murto-osista) lisäaikaa ei tuokaan viive tunnu vielä missään.

groovyb kirjoitti:

Se, tarvitaanko esim muun tunnisteen generointiin ylimääräistä koodia

Aika triviaali se koodi on varmaan millä vaan kielellä, mutta kuitenkin lisää suoritettavaa tavaraa. Useimmissa tapauksissa sillä ei toki ole merkitystä.

Nyt en pysynyt perässä. Tarkoitatko suoritettavalla tavaralla lisää ajettavaa SQL:ää vai suoritettavaa as in runtime?

jos jälkimmäinen, niin ei tuosta tule yhtään itsekirjoitettua riviä lisää. id:n generointimääritykset tehdään ormeissa mäppäysten yhteydessä (fluent nHibernate, EF 7:kin taitaa olla jo pelkästään fluent -pohjainen), oli sitten autoincrement tai autogenerated guid id:nä.

mitä taas tulee tuohon suoritettavaan tavaraan sql:n päässä, id:n generoinnilla tuskin on missään merkitystä, harvemmin kantoja optimoidaan insert -lausekkeiden avulla, vaan keskitytään paremminkin sinne kyselypuolelle. ja varsinkin jos käytössä on guid, ei paljon tarvitse kannasta olemassaolevia id arvoja etukäteen kysellä. jos kyseessä on taas jokin ihan manuaalinen arvo, tuokin hoitunee unique constraintilla.

Puhuin suoritettavasta tavarasta. Suorittamisessa kestää ihan yhtä kauan, oli se koodarin kirjoittamaa tai kirjastoon sisältyvää koodia.