[SQL] Efficient informatie uit records halen

[Verwijderd]

Geachte lezers,

Ik heb een soort van probleem, of beter gezegd, ik heb een mogelijke oplossing, maar dat lijkt me niet de beste oplossing.

Situatie
Ik heb een tabel met hierin klantgegevens, heel toepasselijk heet deze tabel klanten. Hierin staat een record genaamd installaties, waarin de primaire sleutels staan naar de tabel installaties.

Omdat een klant meerdere installaties kan hebben, worden deze primaire sleutels opgeslagen gescheiden door komma's.

code:

Bijvoorbeeld:
-Tabel klant
id: 12345
naam: Jansen
Installaties: 1,5,6,29

-Tabel Installaties
id: 1
naam: naamvaninstallatie

Probleem
Oke, nu de situatie duidelijk is (hoop ik) zal ik uitleggen wat ik graag wil.

Zodra er een softwareupdate, of een nieuwe handleiding wordt gepost voor een installatie wil ik graag dat de klant hier een melding van krijgt. Nu moet ik dus bij elke klant zoeken of die installatie voorkomt ergens in het record klant.installatie

Mijn oplossing
Ik zat zelf te denken dit op te zoeken door naar ", $var," te zoeken, $var is hier het installatie nummer, en ik zoek dus met de komma's erbij.

PHP:

mysql_query("SELECT id FROM klanten WHERE installatie LIKE ', $id,'");

Nu heb ik dat nog niet geprobeerd, ik denk ook wel dat het een fout opleverd, maar dat was in ieder geval de denkwijze die ik nu heb.

Als iemand iets beters weet voor mijn probleem, heel graag een advies!

Alvast bedankt!

[Voutloos]

De oplossing
Geen comma separated values in 1 veldje proppen. Gebruik een koppeltabel.

[Verwijderd]

De zoveelste met hetzelfde probleem... Géén kommagescheiden waarden opslaan in 1 kolom! Goed normaliseren, en dat betekent in dit geval 1 tabel klanten, 1 tabel installaties, en 1 koppeltabel (met 1 foreign key naar klanten, 1 foreign key naar installaties, en een primary key op beide kolommen).

[Verwijderd]

Ik wist dat er ergens iets niet goed ging...heb het hele normalisatieproces wel gehad op school...maargoed, ik weet eerst genoeg, ik weet wat me te doen staat en ik dank jullie hartelijk!

Prettige avond verder

[Verwijderd]

Om toch nog even terug te komen, ik heb vanmorgen een aantal syllabi doorgebladerd van mijn vorige school...maar daar word ik niet veel wijzer van. Het zoeken met google levert ook niet al teveel op trouwens....

Wat ik nu in mij hoofd heb van een koppeltabel is dat ik bijvoorbeeld het volgend aantal records heb:

Klant	Installatie
1	1
1	5
1	29

Maar eerlijk gezegd denk ik dat ik er vreselijk naast zit (Want met 6000 klanten en meer dan 200 installaties wordt dit een flinke tabel)....ik heb net een halfuur met PHPMyAdmin zitten zoeken met het koppelen van tabellen, maar daar werd ik niet veel wijzer van, ook niet uit de handleiding.

Vraag
Zou iemand mij kunnen vertellen hoe deze 'koppeltabellen' maak met PHPMyAdmin?

[ Voor 4% gewijzigd door Verwijderd op 09-05-2007 08:40 ]

[Rac-On]

Verwijderd schreef op woensdag 09 mei 2007 @ 08:36:
Om toch nog even terug te komen, ik heb vanmorgen een aantal syllabi doorgebladerd van mijn vorige school...maar daar word ik niet veel wijzer van. Het zoeken met google levert ook niet al teveel op trouwens....

Wat ik nu in mij hoofd heb van een koppeltabel is dat ik bijvoorbeeld het volgend aantal records heb:

Klant	Installatie
1	1
1	5
1	29

Maar eerlijk gezegd denk ik dat ik er vreselijk naast zit (Want met 6000 klanten en meer dan 200 installaties wordt dit een flinke tabel)....ik heb net een halfuur met PHPMyAdmin zitten zoeken met het koppelen van tabellen, maar daar werd ik niet veel wijzer van, ook niet uit de handleiding.

Vraag
Zou iemand mij kunnen vertellen hoe deze 'koppeltabellen' maak met PHPMyAdmin?

geen idee hoe je het doet met phpmyadmin, maar zo'n koppeltabel is inderdaad wel wat je wil. En geloof me, een tabel met 6000 records stelt echt niks voor.

Om hem aan te maken zou je wellicht eerst de tabel kunnen maken en vervolgens een stukje php de tabel laten vullen (1 voor 1 de klantrecords ophalen en dan voor iedere installatie een record inserted).

[Confusion]

Verwijderd schreef op woensdag 09 mei 2007 @ 08:36:
Maar eerlijk gezegd denk ik dat ik er vreselijk naast zit (Want met 6000 klanten en meer dan 200 installaties wordt dit een flinke tabel)....

Als je per klant 10 installaties heb, dan heb je een koppeltabel met 60000 rijen. Dat is peanuts.

[Verwijderd]

Dus dat betekend dat ik het wel zo moet doen?

[Voutloos]

In 4 van de 4 reacties wordt stellig gezegd dat een koppeltabel de oplossing is, dus ja.

[JQR]

Waarom zou je bij een "één-op-veel" relatie (1 klant <> meerdere installaties) een koppeltabel gebruiken? pas je installatietabel aan, zodat deze een foreign-key (verwijssleutel) krijgt naar de klantentabel:

installatie

installatieId	klantId	naam
1	12345	naamvaninstallatie

met phpMyAdmin kan je zondermeer rijen toevoegen. het hangt een beetje van de versie van mysql af of de foreign keys ook worden afgedwongen.

Voutloos schreef op woensdag 09 mei 2007 @ 08:56:
In 4 van de 4 reacties wordt stellig gezegd dat een koppeltabel de oplossing is, dus ja.

4 van de 5

o ja en queryen doe je met een join:

SQL:

select * from klant left join installaties on klant.klantid = installaties.klantid

suc6

[ Voor 23% gewijzigd door JQR op 09-05-2007 09:00 ]

[Verwijderd]

Bedankt voor de reacties...een beetje zekerheid is nooit weg

JQR, er staat nog meer informatie in de installatietabel dan alleen de naam, dus er is een grote kans op redundantie als ik het op jouw manier doe, maargoed, dat kon jij ook niet weten, dat heb ik niet verteld.

Ik ga het dus met die koppeltabel doen, bedankt en een prettige dag verder!

[Rac-On]

@JQR: ik neem aan dat een installatie bij meerdere klanten voor kan komen, dus een veel-op-veel relatie

[JortK]

1:N relatie heb je het over, dus gewoon een tussentabel gebruiken die je klanten aan de verschillende installaties koppelt

_{Let wel op dat je ook indexen aanmaakt}

[whoami]

JortK schreef op maandag 21 mei 2007 @ 14:08:
1:N relatie heb je het over, dus gewoon een tussentabel gebruiken die je klanten aan de verschillende installaties koppelt

_{Let wel op dat je ook indexen aanmaakt}

Tussentabellen gebruik je voor n:m relaties. Maar dit alles is reeds gezegd geweest, dus eigenlijk zie ik niet in waarom je een 2 weken oud topic kicked om nog eens hetzelfde te herhalen ?

[REDFISH]

Ach ja 'oud' topic en ik heb toch SQL oefening nodig en dit is dan een complete uitwerking voor een koppeltabel. Kunnen nog meer gotters op zoeken dan.

Ik zou het zo doen:

(pleur gewoon in het SQL venster van je Database in PHPmyadmin)

SQL:

CREATE TABLE  Klant --Kies naam Klant omdat 1 record één klant bevat en niet meer klanten
(klantnr NUMERIC(3) NOT NULL, 
naam VARCHAR(25) NOT NULL,

PRIMARY KEY (klantnr)
);

CREATE TABLE Installatie
(installatienr NUMERIC(3) NOT NULL,
naam VARCHAR(25) NOT NULL,

PRIMARY KEY (installatienr)
);

CREATE TABLE KlantInstallatie
(klantnr NUMERIC(3) NOT NULL,
installatienr NUMERIC(3) NOT NULL,

PRIMARY KEY (klantnr, installatienr),
FOREIGN KEY (klantnr) REFERENCES Klant(klantnr)
  ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (installatienr) REFERENCES Installatie(installatienr)
  ON DELETE CASCADE ON UPDATE CASCADE  
);

Zo heb je in de tabel klant alle gegevens maar 1 keer staan, dus als je een gegeven(naam, adres, postcode, telefoonnummer noem maar op) moet bijwerken dan hoef je dit maar in 1 rij te doen.

In KlantInstallatie staan dan de combinaties van de klant met 1 of meer installaties. Klantnr of installatienr zijn niet uniek, maar de combinatie van 1 klant met 1 installatie mag maar 1 keer voorkomen. Daarom is de primary key breder.

Zowel klantnr als installatienr refereren naar de kolomnaam uit de bijbehorende tabel.

De cascade delete en update zorgen ervoor dat als je een klantnr verwijderd of verandert deze in de klantinstallatie tabel ook meeveranderen. Dus als je klant 3 verwijdert, dan worden ook alle combinaties van klantnr met installatienr in Klantinstallatie verwijderd. Het is namelijk niet zinnig om klantnr zonder installatienr of andersom te behouden in de tabel.

Verder zijn door de NOT NULL voorwaarde de naam en installatienaam verplicht. Het is niet zo zinnig om een id zonder naam te hebben.

Wil je nu de klanten met een bepaalde installatie dan hoef je alleen maar :

SQL:

SELECT klantnr
FROM KlantInstallatie
WHERE installatienr = 1 --(of een ander installatieId)

Wil je klanten hebben minstens 1 van meer installaties hebben gebruik dan

SQL:

WHERE installatienr IN (1,3)

Denk eraan dat een koppeltabel veel beter is omdat je hierdoor redundantie uitsluit. Als je gegevens bij elkaar in 1 tabel combineert (bijvoorbeeld klantnr in installatie tabel) dan staat de naam van die installatie telkens dubbel in de tabel. Andersom als je installatie in klant zet staat de naam en alle andere velden telkens dubbel voor elke installatie. Dit is ongewenst(kost meer geheugen en lastig met updaten).

Als je het over normaliseren hebt, dan is installatienr Functioneel Afhankelijk van Klantnr (of andersom) en is het dus beter om deze combinatie een eigen tabel te geven.

Tevens is de query een stuk sneller omdat er geen JOIN tabel nodig is, je haalt de gevens gewoon uit 1 tabel.

[ Voor 38% gewijzigd door REDFISH op 13-06-2007 00:34 ]

[Mawlana]

REDFISH schreef op woensdag 13 juni 2007 @ 00:16:
Zo heb je in de tabel klant alle gegevens maar 1 keer staan, dus als je een gegeven(naam, adres, postcode, telefoonnummer noem maar op) moet bijwerken dan hoef je dit maar in 1 rij te doen.

Is dat wel wenselijk? Wanneer een klant is vehuisd (dus ander adres), dan is het aan de hand van deze database niet meer mogelijk om te achterhalen *waar* deze installatie heeft plaatsgevonden.

[REDFISH]

Nee klopt, maar dit was in de TS helemaal niet genoemd. Als je wilt weten welke installatie waar staat dan ga je ook geen tabel maken die klant en installatie koppelt, maar adressen en installaties.

Het ging hier om softwareupdates en softwareinstallaties.

[ Voor 13% gewijzigd door REDFISH op 14-06-2007 20:14 ]