Probleem antwoorden opslaan in mysql

Ik zou het zo doen:

-- phpMyAdmin SQL Dump
-- version 3.5.2.2
-- http://www.phpmyadmin.net
--
-- Machine: 127.0.0.1
-- Genereertijd: 25 apr 2014 om 18:21
-- Serverversie: 5.5.27
-- PHP-versie: 5.4.7

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Databank: `quiz`
--

-- --------------------------------------------------------

--
-- Tabelstructuur voor tabel `antwoorden`
--

CREATE TABLE IF NOT EXISTS `antwoorden` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vraag_id` int(11) NOT NULL,
  `antwoord` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `volgorde` varchar(1) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `vraag_id` (`vraag_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=8 ;

--
-- Gegevens worden uitgevoerd voor tabel `antwoorden`
--

INSERT INTO `antwoorden` (`id`, `vraag_id`, `antwoord`, `volgorde`) VALUES
(1, 1, 'rood', 'a'),
(2, 1, 'groen', 'b'),
(3, 1, 'blauw', 'c'),
(4, 2, '25 km per week', 'a'),
(5, 2, '50 km per week', 'b'),
(6, 2, '75 km per week', 'c'),
(7, 2, '100 km per week', 'd');

-- --------------------------------------------------------

--
-- Tabelstructuur voor tabel `quizzen`
--

CREATE TABLE IF NOT EXISTS `quizzen` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `titel` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;

--
-- Gegevens worden uitgevoerd voor tabel `quizzen`
--

INSERT INTO `quizzen` (`id`, `titel`) VALUES
(1, 'Test Quiz');

-- --------------------------------------------------------

--
-- Tabelstructuur voor tabel `resultaten`
--

CREATE TABLE IF NOT EXISTS `resultaten` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `vraag_id` int(11) NOT NULL,
  `antwoord_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `vraag_id` (`vraag_id`),
  KEY `user_id` (`user_id`),
  KEY `antwoord_id` (`antwoord_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

--
-- Gegevens worden uitgevoerd voor tabel `resultaten`
--

INSERT INTO `resultaten` (`id`, `user_id`, `vraag_id`, `antwoord_id`) VALUES
(1, 1, 1, 1),
(2, 1, 2, 5);

-- --------------------------------------------------------

--
-- Tabelstructuur voor tabel `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `naam` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;

--
-- Gegevens worden uitgevoerd voor tabel `users`
--

INSERT INTO `users` (`id`, `naam`, `email`) VALUES
(1, 'Frank', '[email protected]');

-- --------------------------------------------------------

--
-- Tabelstructuur voor tabel `vragen`
--

CREATE TABLE IF NOT EXISTS `vragen` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `quiz_id` int(11) NOT NULL,
  `vraag` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `bijschrift` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `quiz_id` (`quiz_id`),
  KEY `vraag_id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

--
-- Gegevens worden uitgevoerd voor tabel `vragen`
--

INSERT INTO `vragen` (`id`, `quiz_id`, `vraag`, `bijschrift`) VALUES
(1, 1, 'Welke kleur heeft de auto?', NULL),
(2, 1, 'Hoeveel kilometer rijdt de auto per week?', NULL);

--
-- Beperkingen voor gedumpte tabellen
--

--
-- Beperkingen voor tabel `antwoorden`
--
ALTER TABLE `antwoorden`
  ADD CONSTRAINT `antwoorden_ibfk_1` FOREIGN KEY (`vraag_id`) REFERENCES `vragen` (`id`) ON DELETE CASCADE;

--
-- Beperkingen voor tabel `resultaten`
--
ALTER TABLE `resultaten`
  ADD CONSTRAINT `resultaten_ibfk_3` FOREIGN KEY (`antwoord_id`) REFERENCES `antwoorden` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `resultaten_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `resultaten_ibfk_2` FOREIGN KEY (`vraag_id`) REFERENCES `vragen` (`id`) ON DELETE CASCADE;

--
-- Beperkingen voor tabel `vragen`
--
ALTER TABLE `vragen`
  ADD CONSTRAINT `vragen_ibfk_1` FOREIGN KEY (`quiz_id`) REFERENCES `quizzen` (`id`) ON DELETE CASCADE;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

dan gelijk maar de query om de vragen voor één quiz op het scherm te krijgen:

select
	v.vraag, v.bijschrift, a.volgorde, a.antwoord
from
	vragen v
left join
	antwoorden a
on
	v.id=a.vraag_id
where
	v.quiz_id=1
order by
	v.id, a.volgorde

en de query om de gegeven antwoorden te krijgen voor één gebruiker en één quiz:

select
	v.vraag, a.volgorde, a.antwoord
from
	resultaten  r
left join
	antwoorden a
on
	r.antwoord_id=a.id
left join
	vragen v
on
	a.vraag_id=v.id
where
	r.user_id=1 and v.quiz_id=1
order by
	v.id, a.volgorde

Frank, je weet dat er ook een INNER JOIN bestaat?
En waarom geef je in alle tabellen de id kolommen dezelfde naam?
In resultaten is die (AI)kolom volledig overbodig.

Ik had hem zo:

CREATE TABLE quizzen (
	quiz_id INT NOT NULL AUTO_INCREMENT,
	quiz_naam VARCHAR(75) NOT NULL,
	PRIMARY KEY (quiz_id)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE vragen (
	vraag_id INT NOT NULL AUTO_INCREMENT,
	quiz_id INT NOT NULL,
	vraag VARCHAR(255) NOT NULL,
	bijschrift VARCHAR(255),
	goed_antwoord INT NOT NULL,
	PRIMARY KEY (vraag_id),
	INDEX idx_quiz (quiz_id),
	CONSTRAINT fk_quiz FOREIGN KEY (quiz_id)
		REFERENCES quizzen(quiz_id)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE antwoorden (
	antwoord_id INT NOT NULL AUTO_INCREMENT,
	vraag_id INT NOT NULL,
	antwoord VARCHAR(255) NOT NULL,
	volgorde CHAR(1) NOT NULL,
	PRIMARY KEY (antwoord_id),
	INDEX idx_vraag_antwoord (vraag_id),
	CONSTRAINT fk_vraag_antwoord FOREIGN KEY (vraag_id)
		REFERENCES vragen(vraag_id)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE gebruikers (
	gebruiker_id INT NOT NULL AUTO_INCREMENT,
	gebruiker_naam VARCHAR(75) NOT NULL,
	gebruiker_email VARCHAR(150) NOT NULL,
	PRIMARY KEY (gebruiker_id)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE resultaten (
	gebruiker_id INT NOT NULL,
	vraag_id INT NOT NULL,
	antwoord_id INT NOT NULL,
	PRIMARY KEY (gebruiker_id, vraag_id),
	INDEX idx_vraag_resultaat (vraag_id),
	INDEX idx_antw_resultaat (antwoord_id),
	CONSTRAINT fk_vraag_resultaat FOREIGN KEY (vraag_id, antwoord_id)
		REFERENCES antwoorden(vraag_id, antwoord_id),
	INDEX idx_gebr_antwoord (gebruiker_id),
	CONSTRAINT fk_gebr_antwoord FOREIGN KEY (gebruiker_id)
		REFERENCES gebruikers(gebruiker_id)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Ger van Steenderen op 25/04/2014 19:17:52

Frank, je weet dat er ook een INNER JOIN bestaat?
En waarom geef je in alle tabellen de id kolommen dezelfde naam?
In resultaten is die (AI)kolom volledig overbodig.

Hoi Ger,

Ja ik weet dat er een inner join bestaat. Maar wellicht is het soms moeilijk om een keus te maken tussen left en inner. Misschien wil je hierover nog iets kwijt? Ik weet wel dat bij een innerjoin alleen de records opgehaald worden als er in beiden tabellen een record gevonden wordt. (zeg ik het zo goed?)

Ik geef er de voorkeur aan om de primary keys gewoonweg 'id' te noemen omdat
a. doctrine dit ook doet. Ik werk veel met doctrine
b. ik dan direct weet dat het om de primary key gaat.
c. omdat het bij een resultset met meerdere id's ook geen probleem is maar dan moet je hem even een alias geven.

De auto-increament in resultaten is inderdaad overbodig dat had ik over het hoofd gezien :-)

Frank Nietbelangrijk op 25/04/2014 21:11:23

Ja ik weet dat er een inner join bestaat. Maar wellicht is het soms moeilijk om een keus te maken tussen left en inner. Misschien wil je hierover nog iets kwijt? Ik weet wel dat bij een innerjoin alleen de records opgehaald worden als er in beiden tabellen een record gevonden wordt. (zeg ik het zo goed?)

Je zegt het goed, bij en left join kan alles wat voor de join staat worden meegenomen, dit kan een (groot) verschil maken in de totale rijen die afgelopen moeten worden.
In dit specifieke geval:
- Zonder quiz bestaat er geen vraag.
- Zonder vraag bestaat er geen antwoord
- Een antwoord wat niet bestaat kan niet gegeven worden

Een left join kan onverwachte resultaten opleveren, maar een inner join niet en die is daarbij vaak sneller.

Ik geef er de voorkeur aan om de primary keys gewoonweg 'id' te noemen omdat
a. doctrine dit ook doet. Ik werk veel met doctrine
b. ik dan direct weet dat het om de primary key gaat.
c. omdat het bij een resultset met meerdere id's ook geen probleem is maar dan moet je hem even een alias geven.

Is wat Docrtine doet heilig?
Ik vind het persoonlijk een slecht gebruik om indenty kolommen een zelfde naam te geven (sterker nog, als ik het voor het zeggen heb verbied ik het)

Thanks Ger, duidelijke antwoorden. Ben wel blij dat je het niet allemaal voor t zeggen hebt :-)

Super jullie hebben me echt geholpen.
Begrijp het ook steeds beter.
Ik wil iedereen bedanken voor de hulp :-)

Ik heb een aantal aanpassingen gedaan, en nog een aantal vragen..
Ik heb ten eerste in tabel vragen goed_antwoord op NULL gezet aangezien het niet altijd voorkomt dat er een goed antwoord is. maar meer een soort van enquete, en mijn vraag is als er bijvoorbeeld meerdere antwoorden mogelijk zijn, dus als checkbox ipv radio. hoe kan ik dit het beste opslaan, gewoon dubbel in de antwoorden zetten, dan zit ik namelijk dat een gebruiker_id er 2 x inkomt voor 1 vraag. gaat dit geen problemen veroorzaken?

Goed als we willen dat sommige antwoorden als checkbox worden weergegeven dan moet je programma dit ook kunnen weten. Het meest handig lijkt mij om een kolom aan de tabel 'antwoorden' toe te voegen zodat je per antwoord kunt bepalen of dit een checkbox moet zijn ja dan nee. Zo zouden vragen zelfs antwoorden kunnen hebben met een checkox en radiobutton door elkaar heen.. Als je denkt dat dit NOOIT zal voorkomen kun je er voor kiezen om die extra kolom aan de tabel 'vragen' toe te voegen waardoor deze betrekking heeft op ALLE antwoorden van die vraag.

ALTER TABLE antwoorden ADD checkbox BOOLEAN NOT NULL DEFAULT FALSE AFTER antwoord

Nu moeten we nog even naar de resultaten kijken.
In de oude situatie was er altijd één antwoord. Indien het nu allemaal checkboxen zijn dan kan het zijn dat er geen antwoord is gegeven (alle checkboxen uit), dat er één of meerdere checkboxen zijn aangevinkt.

Je kunt dan inderdaad in de tabel resultaten één record schrijven per aangevinkte checkbox. Wel moet je even in je achterhoofd onthouden dat het ook kan zijn dat een gebruiker helemaal geen checkbox selecteert en er in dat geval helemaal geen records toegevoegd (hoeven te) worden.

Persoonlijk zou ik dat niet met dezelfde tabellen oplossen.
Een quiz en een enquete zijn verschillende entiteiten, ze hebben wel overeenkomsten maar daar houdt het mee op.

Doe je het wel met één verzameling van tabellen kan je in de resultaten tabel geen primary key hebben over vraag_id en gebruiker_id, maar moet je daar het antwoord_id bij toevoegen.