MySQL database: tabellen opsplitsen
Een vraagje over MySQL tabellen. Ik heb ooit gelezen dat je als het even kan tabellen zou moeten opsplitsen in meerdere tabellen als een waarde verschillende keren voor komt. voorbeeld:
Code (php)
1
2
3
4
2
3
4
id plaats land
1 dorp nl
2 dorp2 nl
3 dorp3 gb
1 dorp nl
2 dorp2 nl
3 dorp3 gb
Als ik het goed begrijp zou je dan van land een nieuwe tabel kunnen/moeten maken en in deze tabel alleen een verwijzing maken naar de index van tabel land.
Mijn vraag is nu:
Wanneer, bij welke aantallen, heeft dit werkelijk nut?
Alvast bedankt,
Jop
Gewijzigd op 15/11/2015 10:50:29 door J opla
Maar stel dat je naast de landcode ook de volledige naam van het land in de tabel wilt opnemen of nog andere informatie over een land dan zou je naar een extra tabel moeten gaan en deze tabellen met elkaar moeten koppelen.
Twee tabellen koppelen:
- Define two tables (example A and B), with their own primary key
= Define a column in Table A as having a Foreign key relationship based on the primary key of Table B
This means that Table A can have one or more records relating to a single record in Table B.
If you already have the tables in place, use the ALTER TABLE statement to create the foreign key constraint:
Code (php)
1
ALTER TABLE A ADD CONSTRAINT fk_b FOREIGN KEY (b_id) references b(id)
fk_b: Name of the foreign key constraint, must be unique to the database
b_id: Name of column in Table A you are creating the foreign key relationship on
b: Name of table, in this case b
id: Name of column in Table B
Dank je voor je reactie.
Het gaat mij er om bij hoeveel rijen het nut heeft. Ik neem aan als ik een tabel heb met 3 rijen zoals nu aangegeven dat ik alle gegevens net zo goed in 1 tabel kan houden. En bij 1.000.000 rijen zal het wel verstandig zijn om op te splitsen. Maar waar ligt het omslagpunt? 10, 100, 1000, 10.000? Of nog hoger?
Jop
Is er nog een andere reden dan dat de tabel logischer is? Voor mijn code vind ik het alleen maar eenvoudiger om alles in 1 tabel te zetten. Geen ingewikkelde query.
Toevoeging op 15/11/2015 11:56:30:
@Frank
Die koppeling van tabellen, kan ik die ook in phpMyAdmin voor elkaar krijgen?
Databases zijn bedoeld om met je data te werken. Laat de database dat ook doen. Zoals het nu lijkt uit wat je van plan bent is PHP het overgrote deel van het rekenwerk te laten doen, en dat is gewoon niet handig.
Een ander bijkomend voordeel van de introductie van aparte tabellen/foreign keys is dat er echte "harde" verbanden tussen je tabellen, en tevens met de bijbehorende data, ontstaan. Dit waarborgt (beter) de correctheid van je totale data-set, of omgekeerd, het verkleint de kans op inconsistenties tussen data uit verschillende tabellen. Als bijvoorbeeld een land "in gebruik" is middels een foreign key in een andere tabel, kun je dit landenrecord niet zomaar verwijderen uit de landen-tabel als je je foreign keys goed hebt opgesteld.
Foreign keys kunnen je ook helpen om je database schoon te houden. Stel bijvoorbeeld dat je landen-tabel toonaangevend is in een of andere applicatie, bijvoorbeeld voor reisinformatie en je hebt hier dus al je data aan opgehangen (via foreign keys). Stel dat je op een gegeven moment besluit dat een land geen vakantiebestemming meer is en dat je alle informatie gerelateerd aan land X wilt verwijderen. Indien je geen foreign keys gebruikt (en al je data dus in feite als los zand aan elkaar hangt) zou je dus uit alle gerelateerde tabellen DELETE-queries moeten uitvoeren om deze data te verwijderen. Echter, had je gebruik gemaakt van een echte relationele database (engine, zoals InnoDB) dan had je kunnen volstaan met één DELETE query (DELETE FROM landen WHERE name = 'X'). De foreign keys hebben namelijk ook een "ON DELETE CASCADE" optie, dat wil zeggen, als het record waaraan gerefereerd wordt wordt dit record ook opgeschoond. Voor tabellen die weer aan zo'n tabel refereren geldt hetzelfde, en zo wordt de hele ketting van record in 1x verwijderd.
Dit principe van het gezond/consistent houden van je data wordt ook wel referentiële integriteit genoemd.
Het lijkt mij ook handig om de landen "metadata" op één plaats onder te brengen, deze kun je dan te allen tijde inhoudelijk aanpassen of uitbreiden zonder bestaande relaties tussen tabellen aan te tasten, dat wordt een ander verhaal indien je "nl" ooit besluit te veranderen :).
Als je database (van zichzelf al) meerdere tabellen bevat en hier relaties tussen bestaan ben je eigenlijk al min of meer verplicht om de InnoDB engine van MySQL te gebruiken, op die manier kun je op database-niveau relaties (en bijbehorende constraints) tussen tabellen afdwingen, een ander bijkomend voordeel is dat je toegang krijgt tot een andere functionaliteit: transacties.
Pipo Clown op 15/11/2015 13:30:35:
Ga jezelf eens inlezen op het onderwerp "normaliseren van databases"
antwoord:
Jopla op 15/11/2015 10:50:08:
Ik heb ooit gelezen dat ...
Ofwel: Ik heb het één en ander gelezen, maar niet de reden gelezen/begrepen waarom.
@ben
Ik begrijp graag de achterliggende reden. Als ik in het water moet springen, wil ik graag weten waarom. ;-)
@Thomas, Frank
Dank je.
Toevoeging op 15/11/2015 16:28:49:
ow...:
wat betekent deze foutmelding? Ik probeer met de code van Frank 2 tabellen aan elkaar te koppelen ...
#1170 - BLOB/TEXT column 'landcode' used in key specification without a key length
Gewijzigd op 15/11/2015 16:21:05 door j opla
De achterliggende reden is heel eenvoudig, zoals ik eerder ook al heb aangegeven. Het organiseren van je data zorgt dat je database er snel mee om kan gaan, en je applicatie dus eenvoudiger gegevens op een gestructureerde manier kan opvragen. Ook voorkomt normaliseren het maken van fouten. Om terug te komen op de landen, stel dat je een tabel hebt met 4 kolommen: een id, een naam van een persoon, naam van een land en een landcode. Het is nu heel eenvoudig om daar inconsistente data in te zetten, bijvoorbeeld 2x dezelfde landcode met een andere naam van een land. Als je normaliseert en de FK relaties goed legt voorkom je die problemen. Ja, je schrijft dat een JOIN, maar je kan die landen dan hergebruiken voor andere dingen. En een query schrijf je maar 1 keer, dus veel verschil maakt het niet in "eenvoud".