[SQL] INSERT in twee afhankelijke tabellen

[Jarige]

Ik heb een probleemstuk waar ik zelf niet uit kom, aangezien ik nog redelijk nieuw ben met SQL.

Ik probeer een wegpagina te maken in PHP waar mensen nieuwsitems kunnen toevoegen, en daarbij bepaalde doelgroepen kunnen aangeven.
Ik heb hiervoor al twee tabellen gemaakt:

Tabel Nieuws:

Field	Type	Collation	Null	Extra
id	bigint(20)		No	auto_increment
titel	text	latin1_general_ci	No
nieuwsbericht	text	latin1_general_ci	No
datum	text	latin1_general_ci	No
schrijver	text	latin1_general_ci	No

En tabel nieuwsdoelgroepen:

Field	Type	Collation	Null
nieuwsId	Bigint(20)		No
doelgroep	varchar(20)	latin1_general_ci	No

Als er een nieuwsitem wordt toegevoegd met meerdere doelgroepen, moeten er dus een aantal dingen gebeuren:
- Alle dingen in tabel nieuws moeten worden toegevoegd (schrijver, titel, datum, nieuwsbericht)
- Van de dingen die zijn toegevoegd moet de nieuwsindex waarde worden genomen, vervolgens moet met deze waarde een aantal keer (afhankelijk van het aantal doelgroepen) een nieuwsindex waarde worden gemaakt in tabel nieuwsdoelgroepen en de daarbij horende doelgroepen.

Ik heb een aantal variabelen hiervoor:
$titel
$schrijver
$datum
$bericht
En een array (index is een integer):
$doelgroepen[]

Hoe moet de query er uit zien? Want ik neem aan dat dit wel in één query te doen is, toch? Ik had eerst meerdere queries, maar dan gaat het snel fout met de nieuwsindexes.

Ik hoop op jullie help!
Alvast bedankt

[Nick_S]

Dit kan niet in 1 query, dan zul je moeten gaan werken met transacties.

Verder je naamgeving, een index in databaseland is heel wat anders als een id. Volgens mij is je nieuwsindex je id en zou deze dus in je Nieuws tabel gewoon id moeten heten en in je nieuwsdoelgroep (enkelvoud!) nieuwsId of nieuws_id. Ook is het type van je id in de ene tabel anders als in je andere tabel.

Ook je datum heeft het verkeerde datatype. Ik neem aan dat je wilt gaan sorteren op datum, dus dat is makkelijker met een date als data type.

Schrijver zou bij mij ook een aparte tabel in je database worden, welke je dan aan een nieuwsbericht koppelt aan de hand van zijn id.

Je hebt het ook over bepaalde doelgroepen, deze zou ik dus ook in een aparte tabel stoppen en een koppeltabel maken tussen nieuws en doelgroep.

[ Voor 36% gewijzigd door Nick_S op 24-12-2009 13:53 ]

[Jarige]

Whow dat zijn nogal wat opmerkingen.

Ik wist dat de naamgeving hier en daar wat minder was, maar dat het zo erg zou zijn had ik niet verwacht. Ik heb wat dingen veranderd nu, en ga zometeen de eerste post aanpassen op de nieuwe naamgeving en types.

Ik ga de datum (nog) niet aanpassen, ik heb er bewust voor gekozen om het op deze manier te doen. Het hoeft niet zozeer gesorteerd te worden op datum namelijk, maar meer op doelgroepen. Bovendien heb ik totaal geen ervaring met de datum type, en hoe je daarmee moet werken.

Ik heb ook bewust gekozen voor de opzet tov de schrijver. Ik vond het niet nodig om telkens een koppeling te leggen tussen de andere tabellen, aangezien dat qua functionaliteit weinig toevoegd, en qua snelheid wel wat afsnoept.

De laatste opmerking snap ik niet, over de koppeltabel. En de eerste trouwens ook niet, met transacties.
Zou je beiden wat verder kunnen toelichten, en daarbij in je hoofd houden dat ik er niet zo goed mee ben?

[smesjz]

Je geeft aan dat je niet goed bent met SQL maar je wil niks weten van normaliseren en datatypes. Het is makkelijker om alles goed op te zetten dan straks er achter komen dat je keuzes niet handig zijn.

Lees eerst eens een SQL boek/tutorial voor dummies want zonder clue maar wat in elkaar frotten gaat nergens over.
Je krijgt dus gewoon meerdere queries om de tabellen te vullen. Voor ieder element in de array dus 1tje.

[lammert]

^^ ga alsjeblieft je datamodel normaliseren. Het kost nu heel even tijd, maar bespaart je straks veel meer tijd en frustratie. Zelfde geld voor data type "date".

Om maar een simpel voorbeeld te noemen, hoe ga je in je huidige model om met 2 schrijvers die allebei "piet jansen" heten?

[Nick_S]

De laatste eerst.

Tabel doelgroep:
id, unique, not-null
naam, unique, not-null

Tabel nieuws:
id, unique, not-null
titel, unique, not-null
etc...

Tabel doelgroepNieuws:
doelgroepId, not-null
nieuwsId, not-null
Combinatie nieuwsId + doelgroepId unique

Hiermee kun je dus makkelijk (voor jezelf en voor je database engine) alle doelgroepen selecteren (SELECT * FROM doelgroep), een naam wijzigen van een doelgroep, extra gegevens aan een doelgroep toevoegen, etc.

Transacties:

quote: http://nl.wikipedia.org/wiki/Transactie_%28dataopslag%29

Een transactie bestaat uit een aantal samenhangende wijzigingen in een database, waarbij ervoor gezorgd wordt dat deze wijzigingen ofwel allemaal plaatsvinden, ofwel geen van alle.

Hiermee voorkom je dus ook dat je id's scheef gaan lopen of dat er processen zijn die wel het nieuwsbericht kunnen zien, maar nog niet de doelgroepen. Het is een beetje afhankelijk van je database en je programmeertaal hoe je het beste transacties en het ophalen van het laatst toegevoegde nieuws id gaat werken.

[Simon Verhoeven]

En het is niet omdat je nu nog niet wilt sorteren op datum dat je later niet die functionaliteit gaat willen.
En dan is het wel wat meer werk dan als je het nu ineens juist maakt.

En wat meer normalisering is zeker geen ramp.

[cariolive23]

Jarige schreef op donderdag 24 december 2009 @ 14:06:
Ik ga de datum (nog) niet aanpassen, ik heb er bewust voor gekozen om het op deze manier te doen. Het hoeft niet zozeer gesorteerd te worden op datum namelijk, maar meer op doelgroepen. Bovendien heb ik totaal geen ervaring met de datum type, en hoe je daarmee moet werken.

Datums sla je op in ISO-formaat, dus yyyy-mm-dd, bv. 2009-12-24. Dat is alles wat je weten moet, zo simpel is het. Dit voorkomt dat je jezelf in de voet schiet, jouw "datums" zijn helemaal geen datums, is niet meer dan onbruikbare rommel. Doe jezelf dit niet aan, bezorgt je veel ellende.

Wil je datums weergeven in een ander formaat, kun je dat in de applicatie regelen op in MySQL met de functie DATE_FORMAT() aan de slag gaan.

[RobIII]

cariolive23 schreef op donderdag 24 december 2009 @ 15:41:
[...]

Datums sla je op in ISO-formaat, dus yyyy-mm-dd, bv. 2009-12-24.

Wait. Whut?
Datums sla je op in een Date(Time) veld. _{Enkele uitzonderingen die hier niet aan de orde zijn daargelaten}

De formattering van je datum is een klus voor je view/gui. (My)SQL heeft daar niets mee van doen. Je kunt het geformatteerd laten 'aanleveren' aan je view/gui vanuit MySQL maar wat is het nut?
ISO formaat heeft er ook niets mee te maken; dat is pas aan de orde als je d.m.v. string concatenation een query gaat bouwen (en dus expliciet niet hebt gekozen voor het betere alternatief: parameterized queries).

[ Voor 53% gewijzigd door RobIII op 24-12-2009 16:29 ]

[HuHu]

cariolive23 schreef op donderdag 24 december 2009 @ 15:41:
[...]

Datums sla je op in ISO-formaat, dus yyyy-mm-dd, bv. 2009-12-24. Dat is alles wat je weten moet, zo simpel is het. Dit voorkomt dat je jezelf in de voet schiet, jouw "datums" zijn helemaal geen datums, is niet meer dan onbruikbare rommel. Doe jezelf dit niet aan, bezorgt je veel ellende.

Wil je datums weergeven in een ander formaat, kun je dat in de applicatie regelen op in MySQL met de functie DATE_FORMAT() aan de slag gaan.

Welke ISO is dat? Ik ken eigenlijk alleen ISO 8601 en die ziet er zo uit: 2009-02-13T14:53:27+01:00

[RobIII]

HuHu schreef op donderdag 24 december 2009 @ 16:17:
[...]

Welke ISO is dat? Ik ken eigenlijk alleen ISO 8601 en die ziet er zo uit: 2009-02-13T14:53:27+01:00

Programming FAQ - Getallen en talstelsels - Datums
Wikipedia: ISO 8601

[ Voor 11% gewijzigd door RobIII op 24-12-2009 16:20 ]

[Jarige]

Hmm, na aanleiding van de vele reacties:

De doelgroep is geen naam, maar een soort code. Het is dus volgens mij niet nodig de doelgroepen ook een id te geven aangezien de doelgroepcode uit zichzelf uniek is.

Ik vroeg ook alleen om een query, in principe. Dat er zoveel reacties komen over normaliseren is leuk, maar ik heb maar één reactie gezien die zei wat het dan precies voor mij inhield (bedankt Nick_S). En dan nog weet ik niet of het me zoveel verder helpt. Ik heb tot nu toe nog geen voordelen gehoord behalve dat het tijd zou schelen in de toekomst. Hoe het tijd oplevert wordt achterwege gelaten.

Het spijt me als ik blind ben voor jullie reacties, maar ik kan gewoon even het nut er niet van in zien.

De datums ga ik nu wel veranderen trouwens.


Maar zou iemand mij een code kunnen geven die nieuws toevoegd, en eventueel een database model?

Ik denk dat het daarbij handig is om de doelgroep code uit te leggen. Ik maak een digitale leeromgeving met een vriend van me. De doelgroepen zijn clusters, en een doelgroepcode is zo opgebouwd: V.6.SK.1
Dit is dus VWO6 met scheikunde als vak, en cluster 1. De doelgroep kan ook gewoon H.5 zijn, dan is het heel Havo 5. En zo kan ook heel VWO de doelgroep zijn met als doelgroepcode 'V', en er zijn meer combinaties mogelijk. De punten zijn handig om met explode('.',$string); alles uit elkaar te halen en te analyseren. Ik heb op de nieuwspagina al een script dat alleen de nieuwsitems selecteerd en laat zien die van toepassing zijn op de ingelogde leerling. Als de leerling dus in VWO6 zit, maar geen scheikunde heeft, dan ziet die leerling het bericht niet. Selectie wordt gedaan op basis van een tabel met clustercodes.

Hulp wordt natuurlijk op prijs gesteld, en tips ook.

[Nick_S]

Doelgroep wordt nu een stuk duidelijker, net zoals waar je de "fout" ingaat. Je probeert namelijk om het werk van je database engine te gaan doen in je programmatuur. Meestal als je nog een subselectie wil maken in je programmatuur, stel je je database engine de "verkeerde" vraag. Recent nog een voorbeeld gehad waar men 600k records uit de database ophaalde om er 9 te tonen! De database engine is hiervoor geoptimaliseerd om dit soort werk uit te voeren. Met een goed "genormaliseerd" datamodel kun je je database veel specifiekere vragen stellen en hoef je dit werk niet meer in je programmatuur uit te voeren.

Nog een voorbeeld: stel dat tweakers.net om de front pagina te tonen elke keer alle nieuwsberichten zou moeten ophalen, denk je dat er dan nog zoveel pageviews gegenereerd kunnen worden?

Vandaar dus het nut van normalisatie.

Verder op jouw probleem, of je hebt een geneste doelgroep structuur of een tag based structuur. (Wil je bijvoorbeeld ook al je nieuwsitems voor scheikunde op kunnen halen?

Je wilt je database vragen kunnen stellen als:

- Geef mij alle nieuwsberichten voor VWO.
- Geef mij alle nieuwsberichten voor VWO 6.
- Geef mij alle nieuwsberichten voor Scheikunde
- Geef mij alle nieuwsberichten van vandaag voor VWO 6.
etc.

Om het nog maar eens te benadrukken: Dit wil je niet in je programmatuur gaan uitzoeken!

Hier wordt het erg goed uitgelegd: http://www.pui.ch/phred/a...ags-database-schemas.html Hier ben ik dus voorstander van optie 3, de Toxi solution.

Dit heeft echter nog niks te maken met het inserten van je data, maar met het ophalen. (En wat gebeurt er vaker?)

[smesjz]

Je misbruik van doelgroepcode is vragen om problemen maar je ziet ongetwijfeld geen reden om het te veranderen
Om te bepalen of een ingelogde gebruiker nieuws mag zien moet je eerst alle records ophalen en vervolgens per record die explode te doen.
Voor ieder element in de array ga je vervolgens kijken of ie matcht waarbij je dus een X aantal vergelijkingen gaat doen. Voor VWO, leerjaar, vak en cluster.
En vervolgens ga je je afvragen waarom het zo lang duurt voordat die pagina er staat....

Zorg voor een beter datamodel en laat je database dat rotwerk doen en zorgen dat ie alleen de relevante rows teruggeeft.

Je zou bijvoorbeeld het advies kunnen opvolgen van mensen in dit topic die wel verstand hebben van datamodellen en normalisatie. Misschien geen directe oplossing voor je probleem maar op dit moment heb je totaal geen idee wat je aan het doen bent.

[Jarige]

smesjz, hoe fijn ik het ook vind dat je een reactie geeft, je reactie heeft voor mij geen waarde. Je geeft advies noch tips, en bent eigenlijk alleen maar aan het zeuren hoe slecht ik het doe. En zoals ik al zei, ik ben een beginner.

Verder, Nick_S, als ik het goed begrijp stel je dus alsnog voor om drie tabellen te maken.

Tabel nieuws:
id
titel
datum (als datum opgeslagen)
schrijver (een id van een schrijver, niet de naam)
bericht

Tabel nieuwsdoelgroep:
nieuwsId
doelgroepId

Tabel doelgroep:
Id
naam

Rest me nog steeds de vraag, hoe zorg ik er voor dat de id's voor één nieuwsitem gelijk blijven? Hoe moeten de queries er dan uit gaan zien? En nog een vraag die nu begint te spelen; hoe zorg ik er voor dat alleen de nieuwsitems geselecteerd worden die voor een bepaalde leerling gelden, zowel V.6 als V.6.SK.1?

[Nick_S]

Jarige schreef op donderdag 24 december 2009 @ 19:12:
smesjz, hoe fijn ik het ook vind dat je een reactie geeft, je reactie heeft voor mij geen waarde. Je geeft advies noch tips, en bent eigenlijk alleen maar aan het zeuren hoe slecht ik het doe. En zoals ik al zei, ik ben een beginner.

Hier ben ik het gedeeltelijk mee eens. Je stelt wel een vraag, maar je laat niet zien wat je zelf al geprobeerd hebt. Dat is wel een vereiste hier.

Rest me nog steeds de vraag, hoe zorg ik er voor dat de id's voor één nieuwsitem gelijk blijven? Hoe moeten de queries er dan uit gaan zien?

Dat is dus opzoekwerk. Ik heb je gezegd, dat je hiervoor transacties dient te gebruiken. Daarna heb je nog niet laten zien wat je met dit antwoord gedaan hebt. We zijn hier geen afhaalbalie, maar we proberen jou te helpen met jezelf helpen. Ga dus eens op zoek naar transacties, wat het zijn en hoe je ze kunt gebruiken. Kom je er daarna nog niet uit, laat dan hier zien wat je geprobeerd hebt, dan kunnen we je weer verder helpen.

En nog een vraag die nu begint te spelen; hoe zorg ik er voor dat alleen de nieuwsitems geselecteerd worden die voor een bepaalde leerling gelden, zowel V.6 als V.6.SK.1?

Welke van de queries zoals ze in dat artikel staan begrijp je niet en welke/wat heb je al geprobeerd?

[Jarige]

Vreemd, ik zou toch zweren dat ik hier al een keer een nieuwe reactie had toegevoegd, blijkbaar niet.

Nick_S, jouw reactie(s) is zeer behulpzaam. Bedankt.

Ik ga nu de database om te gooien, en ik probeer er zelf uit te komen. Ik weet nu hoe ik vragen hier moet stellen, en wat ik moet doen om de database een beetje gezond te houden.

Ik ga er trouwens ook van uit dat je het nieuwe databasemodel beter vindt, aangezien ik er geen negatieve reactie op zie.

Ik moet ook eerlijk bekennen dat ik nog niets geprobeert heb, omdat ik dacht een kant-en-klaar andwoord te krijgen. Maar je hebt gelijk, het is beter als ik mezelf help, dan leer ik het beter. Je hebt me in ieder geval een duw in de goede richting gegeven.

Bedankt

[Jarige]

Ik heb ff gezocht naar transacties, en ik begrijp er maar de helft van.
Ten eerste zijn zo'n beetje alle hits naar een aantal Microsoft SQL Server vragen en tutorials, en ik weet niet of dat dus mogelijk is met de SQL die ik gebruik (volgens mij MySQL).
Ten tweede is heb ik geen enkele goede tutorial gevonden. Er was er één in het Engels, maar die gebruikte zoveel onbekende termen (waarschijnlijk databasetermen) die ik niet begreep, zodat het redelijk moeilijk is te begrijpen. Of sommige geven alleen de syntax van BEGIN WORK, COMMIT, ROLLBACK en whatever.
Ik heb een (code)voorbeeld nodig wil ik dit soort dingen begrijpen. En die heb ik nog niet kunnen vinden.
Ik ben ook een beetje huiverig te beginnen aan code, aangezien ik bestaand werk niet wil vernietigen.
Ik ga ondertussen even bezig met het aanmaken van de tabellen.

[Woy]

Jarige schreef op maandag 28 december 2009 @ 12:04:
Ik heb ff gezocht naar transacties, en ik begrijp er maar de helft van.
Ten eerste zijn zo'n beetje alle hits naar een aantal Microsoft SQL Server vragen en tutorials, en ik weet niet of dat dus mogelijk is met de SQL die ik gebruik (volgens mij MySQL).
Ten tweede is heb ik geen enkele goede tutorial gevonden. Er was er één in het Engels, maar die gebruikte zoveel onbekende termen (waarschijnlijk databasetermen) die ik niet begreep, zodat het redelijk moeilijk is te begrijpen. Of sommige geven alleen de syntax van BEGIN WORK, COMMIT, ROLLBACK en whatever.
Ik heb een (code)voorbeeld nodig wil ik dit soort dingen begrijpen. En die heb ik nog niet kunnen vinden.
Ik ben ook een beetje huiverig te beginnen aan code, aangezien ik bestaand werk niet wil vernietigen.
Ik ga ondertussen even bezig met het aanmaken van de tabellen.

Dan zou ik voorstellen om een goed boek over databases te kopen. Het is belangrijk dat je eerst de theorie over transacties goed begrijpt voordat je begint met code schrijven, anders ga je zeker fouten maken.

Iets als transacties behoort gewoon in elk goed database boek behandeld te worden.

Verder zou je MS-SQL voorbeelden gewoon moeten kunnen interpreteren, en vertalen naar MySQL, aangezien de concepten gewoon hetzelfde zijn. Hooguit verschilt de syntax wat.

[cariolive23]

De SQL bestaat niet, SQL is een taal en geen database. Diverse databases hebben hun eigen dialect van deze taal geimplementeerd, MySQL heeft (imho) de meest beroerde versie daarvan aangemaakt. Desondanks kun je ook met MySQL nog redelijke standaard SQL kletsen.

Een transactie is in de basis redelijk simpel, alles lukt of alles wordt afgekeurd:
BEGIN;
INSERT INTO a(kolom_x) VALUES('a');
INSERT INTO b(kolom_y) VALUES('b');
INSERT INTO b(kolom_z) VALUES('c');
COMMIT;

Mocht bv. de 3e INSERT mislukken, doe je geen COMMIT maar een ROLLBACK en worden dus ook de eerste twee INSERT's afgekeurd.

In MySQL ben je verplicht om de innoDB-engine voor je tabellen te gebruiken, andere engines kennen geen transacties en slaat het gebruik van een transactie dus helemaal nergens op. Helaas geeft MySQL weer eens geen foutmelding wanneer je een transactie probeert uit te voeren op een tabel die helemaal geen transacties ondersteunt, het is jouw probleem dat jouw database corrupt wordt, niet het probleem van MySQL

Tip: Leer het gebruik van databases en SQL met een andere database dan MySQL, dan kun je later eenvoudig alsnog MySQL gaan gebruiken. Eerst MySQL leren kennen en de vele problemen doorgronden, kost meer tijd en frustratie...

Ps. MySQL kent nog zoiets "handigs" als auto_commit, iets wat je absoluut niet wilt hebben, zeker niet bij transacties. Jij bepaalt wanneer iets wordt gecommit, niet een vaag stuk ellende wat met behulp van een glazen bol bedenkt wanneer er een commit moet worden uitgevoerd.

[ Voor 10% gewijzigd door cariolive23 op 28-12-2009 12:19 . Reden: auto-commit ]

[Jarige]

Heb je een tip op bijvoorbeeld bol.com voor een goed boek? Ik weet dat in MySQL en PHP voor dummies niets staat over transacties. Ik hoop dat het boek niet zo duur is, ik heb wat boeken gezien die een vriend van me wou kopen. Allemaal €25 + en dat vond ik wel erg duur

[Kalentum]

cariolive23 schreef op maandag 28 december 2009 @ 12:16:
Ps. MySQL kent nog zoiets "handigs" als auto_commit, iets wat je absoluut niet wilt hebben, zeker niet bij transacties. Jij bepaalt wanneer iets wordt gecommit, niet een vaag stuk ellende wat met behulp van een glazen bol bedenkt wanneer er een commit moet worden uitgevoerd.

Niet alleen MySQL maar ook sqlite, postgresql en Microsoft SQL Server. Wellicht bij andere DMBS'en ook. Dit is op server niveau of op connectieniveau uit te schakelen.

Bij MySQL is er geen sprake van een glazen bol: bij autocommit wordt gewoon elk statement een transactie, tenzij je dit (zoals in dit topic genoemd) expliciet overruled.

[Jarige]

Ok, ik heb gewoon www.000webhost.com als webhost op dit moment. In de ontwerpfase hoeft er wat mij betreft geen goede server te zijn, en omdat mijn vriend deze server koos, zijn we gebonden aan MySQL.
Ik weet überhaupt niet of er een andere fase komt dan de ontwerpfase, hooguit een gesloten testfase. Dit is maar een schoolproject.
Goed, inhoudelijk even verder reageren.
cariolive23, ik verwacht niet dat er een eventuele Rollback nodig is, maar je weet maar nooit zeker natuurlijk.
Met het stukje code wat jij daar hebt, worden daar automatisch rollbacks mee uitgevoerd of moet je er zelf nog een IF oid aan toevoegen?

[Manuel]

Pas als je een foutieve bewerking hebt uitgevoerd of een query die maar halfgebakken is doorgekomen, dan heb je een rollback nodig, pas als jij de TRANSACTION beëindigt door middel van COMMIT wordt de query uitgevoerd. Als je gewoon wat normale query's schrijft en gaat kijken wat de gebruiker in voert door middel van PHP om maar even als voorbeeld te nemen ben je al klaar. (Het leukste zou dan natuurlijk prepared statements zijn, hoef je geen vrees meer te hebben voor SQL Injections).

Alles wat je hierover moet weten staat gewoon gedekt in de documentatie van MySQL zoals 000webhost gebruikt. Transactions

Nu even om antwoord te geven op je vraag:
Nee, als de transactie niet goed is dan stopt de query sowieso voor zover ik weet, je kan namelijk geen foutieve query doorvoeren aan MySQL zonder dat die een error terug geeft.

[ Voor 14% gewijzigd door Manuel op 28-12-2009 12:57 . Reden: * ]

[Jarige]

Manuel, bedankt voor je reactie. De link die je gaf zal waarschijnlijk erg nuttig blijken.
Nou heb ik na aanleiding van die link een vraag. Die autocommit moet bij andere query's uit zijn. Is het slim om hem aan het begin van een query aan te zetten, en aan het einde weer uit? Of is dit onnodig en is er een betere oplossing?

[cariolive23]

Manuel schreef op maandag 28 december 2009 @ 12:55:
pas als jij de TRANSACTION beëindigt door middel van COMMIT wordt de query uitgevoerd.

Dat is absoluut niet waar, alle queries binnen een transactie worden gewoon uitgevoerd. Dat is ook de reden dat je prima een SELECT kunt uitvoeren binnen een transactie, ook om te zien hoe jouw INSERT's de inhoud van een tabel hebben veranderd. Dat deze wijzigingen nog niet zichtbaar zijn voor andere gebruikers van de database, dat is weer een ander verhaal. De COMMIT zal de data definitief opslaan, een ROLLBACK zal de data definitief onzichtbaar maken voor alle gebruikers (a.k.a. verwijderen).

Nee, als de transactie niet goed is dan stopt de query sowieso voor zover ik weet, je kan namelijk geen foutieve query doorvoeren aan MySQL zonder dat die een error terug geeft.

Dit ligt helemaal aan jouw foutafhandeling, er mag best een query mislukken binnen een transactie, dat hoeft geen enkele invloed te hebben op de andere queries. Zo kun je er voor kiezen om bij een mislukte INSERT elders een UPDATE uit te laten voeren. Net wat jij nodig hebt, mogelijkheden genoeg.

[Jarige]

Oh, stom, een rechtstreeks antwoord stond gewoon in de link die Manuel me gaf:
"With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. "
Ok, bedankt, ik ga er even lekker mee stoeien

[Jarige]

Ik heb nog een vraagje over de ID's van de doelgroepen en nieuwsitems.
Auto_increment moet blijkbaar uit staan? Moet ik nou bij elk stukje nieuws manueel een ID toevoegen? Het lijkt me toch dat dat automatisch kan, want ik zou dus twee ID's manueel moeten toevoegen in het nieuwe databasemodel. Is er een goede mogelijkheid dit snel en gemakkelijk te doen?

[cariolive23]

Waarom zou je auto_increment uit willen zetten? Ik zou niet weten waarom je handmatig id's zou willen aanmaken, levert meer problemen dan oplossingen op.

Verder is de vraag niet duidelijk, de achterliggende gedachte ontbreekt.

[Manuel]

cariolive23 schreef op maandag 28 december 2009 @ 13:12:
[...]

Dat is absoluut niet waar, alle queries binnen een transactie worden gewoon uitgevoerd. Dat is ook de reden dat je prima een SELECT kunt uitvoeren binnen een transactie, ook om te zien hoe jouw INSERT's de inhoud van een tabel hebben veranderd. Dat deze wijzigingen nog niet zichtbaar zijn voor andere gebruikers van de database, dat is weer een ander verhaal. De COMMIT zal de data definitief opslaan, een ROLLBACK zal de data definitief onzichtbaar maken voor alle gebruikers (a.k.a. verwijderen).

Zou dat dan handig zijn in een productieomgeving? Om eerst te kijken of alle query's wel er doorkomen of zou je gewoon in het begin eerst goed over de query's moeten nadenken voordat ze worden uitgevoerd. Ik zie een transaction eigenlijk dan ook maar als een tijdelijke query, eerder om eerst van alles te testen in plaats van in een productieomgeving alles even door de transaction laten lopen om misschien te kijken of het fout gaat.

@off
Wat je eigenlijk dan ook kan zeggen is een combo maken van de query's en dan in een Stored Procedure kunt gooien..

[Jarige]

Ik wou het uitzetten omdat Nick_S er niet over sprak. Maar als ik het moet aanlaten is het ook goed.
Ik heb weer een vraag. Deze query werkt (vanzelfsprekend) niet.

SELECT titel,nieuwsbericht,datum,schrijver FROM nieuws WHERE id IN (
SELECT NieuwsId FROM nieuwsdoelgroep WHERE DoelgroepId IN (
SELECT Id from doelgroep WHERE doelgroep IN LIKE (
SELECT clusterId FROM vakkenpakket WHERE leerlingnummer = '$leerling')))

De fout is natuurlijk de LIKE op de 3e regel (in dit geval). Het probleem is, dat de 4e regel meerdere clusters teruggeeft, die hij dan moet gaan vergelijken met de doelgroep. Als doelgroep bijvoorbeeld V.6 is, en er zit een cluster van V.6.SK.1 in het vakkenpakket, dan moet het Id van de doelgroep ook genomen worden. Het lijkt me onnodig een aparte tabel te maken met alle leerlingen en hun doelgroepen, aangezien de doelgroepen afhankelijk zijn van het vakkenpakket. Als er dan een weiziging in het vakkenpakket wordt gedaan moet dat ook gedaan worden in de doelgroepen, en dat zo weer denormalisatie (of hoe je dat ook noemt) zijn.
In principe zou het dus 'doelgroep LIKE '%clusterId%' moeten zijn, dat kan dus niet.

[ Voor 4% gewijzigd door Jarige op 28-12-2009 14:01 ]

[Woy]

Het lijkt me persoonlijk handig dat je je eerst de basis van SQL en databases eigen maakt. Want de manier met een aantal geneste IN's kan misschien wel, maar het lijkt me dat je eigenlijk op zoek bent naar de verschillende vormen van JOIN's

Verder gaat het hier precies fout waar eerder in het topic al voor gewaarschuwd werd. Doordat je in je doelgroep V.6.SK.1 opslaat is het niet eenvoudig meer om daar op te selecteren, want je moet bijvoorbeeld met LIKE of SUBSTR gaan werken. Ik zou het dus nog wat verder normaliseren.

Samengestelde dingen in een enkel veld stoppen is meestal geen goed idee, zeker niet als je er nog op moet filteren.

[cariolive23]

Manuel schreef op maandag 28 december 2009 @ 13:49:
[...]

Zou dat dan handig zijn in een productieomgeving?

Veel keuze heb je niet, dit is de enige manier om veilig met data te werken.

Om eerst te kijken of alle query's wel er doorkomen of zou je gewoon in het begin eerst goed over de query's moeten nadenken voordat ze worden uitgevoerd.

Het "doorkomen van een query" en het mislukken van een query, dat zijn twee totaal verschillende dingen. Goed nadenken over je queries, wat imho normaal is, zal je hierbij ook niet helpen. Een query kan om diverse redenen mislukken, je kunt nooit garanderen dat een query technisch altijd lukt. Afhankelijk van de reden van het mislukken, zul je dan een andere query willen uitvoeren of juist alles ongedaan willen maken.

En zelfs wanneer een query technisch lukt, dan nog kan het zo zijn dat jij jouw transactie ongedaan wilt maken. Stel dat een SELECT binnen de transactie geen bruikbare data oplevert, dan wil je misschien de hele transactie (of slechts een deel van de transactie) ongedaan maken, het is allemaal mogelijk.

Ik zie een transaction eigenlijk dan ook maar als een tijdelijke query, eerder om eerst van alles te testen in plaats van in een productieomgeving alles even door de transaction laten lopen om misschien te kijken of het fout gaat.

Dat is wel een zeer beperkte scope, daarmee gooi je 95% van de toepassingen van transacties over boord. Waarom je het begrip "productieomgeving" hier bij zet, is mij helemaal een raadsel, transacties hebben daar niets mee te maken. Al is je data uiteraard wel een factor tig belangrijker op je productie dan op je test/ontwikkelomgeving.

Verder test je niks op je database met een transactie, je zorgt per definitie voor consistente data ook wanneer je niet via A naar B komt, maar via C naar B gaat. En wat dacht je van de zichtbaarheid van data? Dat kun je zonder transacties niet goed regelen, tenzij je met locks de performance van je database omlaag wilt gaan halen. Wat dus totaal overbodig is.

In PostgreSQL (andere db's wellicht ook) zou je e.e.a. ook in een stored procedure (function) kunnen zetten, sp's werken per definitie met een transactie.

[bindsa]

Wij hebben inmiddels het hele databasemodel maar eens geprobeerd te normaliseren, het staat in een spreadsheetje:

http://leeromgeving.comxa.com/Strokendiagram.xlsx

Sleutels zijn onderstreept en het datatype staat tussen haakjes.

Graag kritisch commentaar hierop

[Woy]

Een paar dingen die me opvallen:

• Waarom heb je een aparte tabel voor leerlinggegevens en personeelgegevens. Dit lijkt me perfect in een tabel te passen met eventueel een extra tabel voor specifieke gegevens. Je kunt dan een veld opnemen met het type persoon. Je kunt dan ook makkelijker met bijvoorbeeld de inlog gegevens koppelen.
• Waarom is de naamgeving van ID velden zo verschillend? ( id, index, {tabel}id, filekey )
• Ik zou overal als ID gewoon een auto-increment nemen ( Zolang je een record uniek moet kunnen identificeren, anders een samengestelde PK van andere FK's )

[ Voor 15% gewijzigd door Woy op 28-12-2009 15:37 ]

[Jarige]

Als ID hebben we het precies gedaan zoals je beschreef, Woy. Overal auto_increment behalve als hij niet uniek is.
Mijn vriend heeft de verkeerde versie geupload, zo te zien. Nieuwe versie hier:
http://leeromgeving.comxa.com/Strokendiagram-1.xls

Wijzigingen staan in het rood.

[Woy]

Maar dan blijft mijn opmerking staan waarom de naamgeving zo verschilt.

Verder snap ik ook niet helemaal waarom je bij bestanden weer een filekey gebruikt in plaats van een ID, en bij menu een item in plaats van een ID.

Verder zou ik een Persoon tabel maken, en daar de algemene persoonsgegeven in zetten. Dan kan je je inlog gegevens gewoon naar de Persoon tabel laten wijzigen. Dan kun je specifieke gegevens in een tabel Leerling, en Personeel stoppen.

Ook bij Personeelsgegevens heb je zo te zien geen auto-increment maar een string als id. Die code kun je natuurlijk best opslaan, echter zou ik hem niet als PK gebruiken, want misschien verander je deze nog wel een keer.

Verder zie ik 2 maal een kolom vak tegen. Is het niet handiger om een tabel Vak op te nemen in je data-model? Daar kun je dan mooi de code en de omschrijving ( en eventueel andere gegevens ) in stopen.

Verder snap ik het begrip cluster niet helemaal aan de hand van dit datamodel

[Jarige]

De keuze voor twee verschillende tabellen met persoongesgegevens komt voort uit onze keuze om leerlingen te laten inloggen met een leerlingnummer, maar leraren met een code. Jan van Steen zou als code SNJ hebben (SleeN Jan). Het gaat om het verschil tussen het getal en de tekst.

Het vak is een afkorting voor het vak. De reden dat je hem meerdere keren tegenkomt, is omdat hij de ene keer gebruikt wordt als doelgroep voor nieuws. Een nieuwsitem kan bijvoorbeeld alleen voor VWO 6 zijn, terwijl je geen VWO 6 als cluster hebt.

Verder, het begrip cluster is iets uit de middelbare school. Wij hebben er voor gekozen om een cluster op deze manier op te laten bouwen: V6SK2. Dit is een unieke code voor een les die je kan volgen. Je kan het zien als een soort klas, maar dan met mensen die voor Scheikunde gekozen hebben. Je bent toch wel naar een middelbare school geweest hoop ik
Als er geen keuzevakken zijn, dan is elk vak voor elke klas een cluster. Als er wel keuzevakken zijn, dan komen er mensen uit verschilllende klassen dezelfde les volgen, en daarom moet er een clusterId worden aangemaakt, die uniek is voor dat soort lessen. V6SK2 is dus een voorbeeld voor de 2e VWO6 klas die Scheikunde volgt. In de tabellen zie je dit apart gesorteerd als leerrichting, leerjaar, vak en clusternummer.

[Woy]

Jarige schreef op maandag 28 december 2009 @ 16:50:
De keuze voor twee verschillende tabellen met persoongesgegevens komt voort uit onze keuze om leerlingen te laten inloggen met een leerlingnummer, maar leraren met een code. Jan van Steen zou als code SNJ hebben (SleeN Jan). Het gaat om het verschil tussen het getal en de tekst.

Maar de inlogcode sla je gewoon op bij je inlog gegevens. Dus je hebt een tabel Persoon ( NAW, etc. ) een tabel Leerling ( Klas, Leerlingnummer, etc. ) en een tabel Medewerker ( Functie? etc. ). En een tabel Login ( LoginNaam, Password, Koppeling met Persoon ). Op die manier kun je ook zorgen dat een medwerker een andere loginnaam heeft.

Het vak is een afkorting voor het vak. De reden dat je hem meerdere keren tegenkomt, is omdat hij de ene keer gebruikt wordt als doelgroep voor nieuws. Een nieuwsitem kan bijvoorbeeld alleen voor VWO 6 zijn, terwijl je geen VWO 6 als cluster hebt.

Maar als je afkorting van een vak nu veranderd, moet je dat in 2 tabellen aanpassen. Als je een tabel vak hebt, dan hoef je het alleen in die tabel aan te passen.

[Jarige]

Afkortingen van vakken worden eigenlijk nooit veranderd. Zelfs als er een nieuw vak wordt toegevoegd hoeft dat nog steeds maar in één tabel. De mensen die nieuws kunnen toevoegen zullen dan automatisch voor dit doelgroep kunnen kiezen.

[Cartman!]

Ik krijg het gevoel dat je hulp zoekt maar als het geboden wordt je het niet wil accepteren. Serieus, een hoop hier weten echt waar ze het over hebben, neem dat dan ook aan van ze. Tot nu toe verdedig je naar mijn idee je eigen denkwijze terwijl je kwam om hulp te zoeken, ik snap het niet helemaal.

[Remus]

Jarige schreef op maandag 28 december 2009 @ 16:50:
Verder, het begrip cluster is iets uit de middelbare school. Wij hebben er voor gekozen om een cluster op deze manier op te laten bouwen: V6SK2. Dit is een unieke code voor een les die je kan volgen. Je kan het zien als een soort klas, maar dan met mensen die voor Scheikunde gekozen hebben. Je bent toch wel naar een middelbare school geweest hoop ik

Vroegâh, toen wij naar school gingen en er op middelbare school nog echt les werd gegeven had je nog geen clusters.

[EfBe]

Ik moet zeggen dat ik 41 posts in een thread over een simpele 1:n relatie toch wel schokkend vind worden. Je tabellen kunnen wel, maar je moet wel innodb gebruiken op mysql. dan eerst transaction starten met BEGIN TRANSACTION, daarna eerst insert doen in Nieuws, en daarna insert doen in NieuwsDoelgroepen. Je moet overigens een foreign key constraint leggen vanaf NieuwsDoelgroepen.NieuwsId naar Nieuws.Id. Om ervoor te zorgen dat je dezelfde Id insert in NieuwsDoelgroepen.NieuwsId als is geinsert in Nieuws.Id, moet je NA de insert in Nieuws de query: SELECT LAST_INSERT_ID(); uitvoeren en de value die je terugkrijgt als value inserten in NieuwsDoelgroepen.NieuwsId. Als je klaar bent met inserten, roep je COMMIT aan.

BigInt(20) is overigens veel te groot. Je kunt makkelijk met Int toe, immers ik denk niet dat jij 100000000000000000000-1 nieuwsitems verwacht. Althans, ik niet

[ Voor 3% gewijzigd door EfBe op 29-12-2009 09:10 ]

[Thomasje]

EfBe schreef op dinsdag 29 december 2009 @ 09:09:
BigInt(20) is overigens veel te groot. Je kunt makkelijk met Int toe, immers ik denk niet dat jij 100000000000000000000-1 nieuwsitems verwacht. Althans, ik niet

Dit is de range van een BigInt:
The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.


Over een goed boek gesproken, ben nu zelf met het volgende boek bezig:
http://www.bol.com/nl/p/e...01004002546185/index.html

[EfBe]

Thomasje schreef op dinsdag 29 december 2009 @ 09:29:
[...]
Dit is de range van een BigInt:
The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.

Ik wilde alleen aangeven hoe groot 20-precision ong. was.

[Jarige]

Ik krijg het gevoel dat je hulp zoekt maar als het geboden wordt je het niet wil accepteren. Serieus, een hoop hier weten echt waar ze het over hebben, neem dat dan ook aan van ze.

Ik probeer van alles wat ik doe het nut in te zien. De mensen die mij tips geven geven niet altijd een reden om die tips ook uit te voeren, en dat terwijl ik zelf wel dingen tegen die tips heb, zoals je ziet. Ik ga niet zomaar dingen omgooien omdat iemand op internet dat zegt, zonder dat hij er argumenten bij geeft. Daarbij komt dat ik al een hele hoop dingen heb veranderd omwille van de mensen hier.

EfBe, je reactie ziet er uit als abracadabra, maar ik zal proberen de moeilijke termen op te zoeken. Bedankt voor je reactie.

Woy, ik zal later nog kijken naar de persoonsgegevens. Je tips zijn niet onbruikbaar, dat is het niet waarom ik het steeds weerleg, het is dat het lijkt alsof je er geen argumenten voor hebt. Je hebt ze ontgetwijfeld wel, anders zou je het niet aanraden, maar die argumenten heb ik nodig om het nut er van in te zien. Anders heb ik het idee onnodig en overbodig werk uit te voeren.

En misschien is nieuwsId inderdaat wat groot als je het zo bekijkt Ik verander hem later wel. Het is nu nog niet van groot belang. Volgens mij zijn er overigens wel meer van dat soort grote Integers (clusterId, cijferId)