[MySQL]koppeltabel en transacties

[Niakmo]

Voor een bepaalde website heeft elke gebruiker een eigen profel, de profiel gegevens zijn door de beheerder aan te passen. dit houd in dat ik 3 tabellen gebruik:

• profile (profile_id, en vaste profiel gegevens)
• profile_fields (field_id, field_name)
• profile_data (data_id, profile_id, field_id, content)

de laatste tabel is in feite de koppeltabel met alle profiel gegevens. Nu loop ik helaas tegen een aantal problemen aan.

Als een profiel word toegevoegd kan je ervoor kiezen om meteen alle velden in de koppeltabel te plaatsen, ookal zijn ze leeg, hierdoor krijg je heel veel lege rijen wat nutteloos is.

Als je alleen de ingevulde velden opslaat en de persoon update zijn profiel kun je geen simpele update query gebruiken maar moet je een mix van updates en inserts gebruiken waardoor het nodeloos ingewikkeld word en je grotere kans hebt op corrupte data.

Een andere optie is om gebruik te maken van transacties, waarbij je eerst alle data verwijderd en vervolgens insert, met als nadeel dat je gebruik moet maken InnoDB en je twee queries hebt, hierdoor worden dingen wederom langzamer.

Verder heb je soortgelijke problemen als de beheerder nieuwe velden toevoegd of verwijderd.

Wat is volgens jullie de gulden middenweg, of zijn er nog opties die ik over het hoofd zie?

[Verwijderd]

Niakmo schreef op donderdag 31 januari 2008 @ 14:48:

Een andere optie is om gebruik te maken van transacties, waarbij je eerst alle data verwijderd en vervolgens insert, met als nadeel dat je gebruik moet maken InnoDB en je twee queries hebt, hierdoor worden dingen wederom langzamer.

Ja, innodb is langzamer voor selects maar niet direct voor inserts. Innodb gebruikt geen tablelocks maar row locks waardoor bij veel inserts op een drukke site innodb sneller is. Overigens is de performance overhead van innodb ook niet heel veel, en gezien de voordelen van innodb zou ik voor innodb gaan.

Om je verder te helpen met je probleem. Ik zou toch voor de delete/insert oplossing gaan denk ik. Maar, zelf ben ik daarvan ook niet echt overtuigd... Lastig.

[T-MOB]

Volgens mij is twee queries hoe dan ook het minimum. De transactie heeft daarom hoe dan ook mijn voorkeur. Waarom InnoDB een nadeel is weet ik niet? Het voordeel is dat je relaties af kunt dwingen, dat je transacties hebt, zeg maar dat het meer database-functies heeft dan MyISAM...

De eerstgenoemde optie is trouwens vooral suf omdat je een datamodel gebruik om dynamische velden toe te kunnen voegen aan een profiel. Als je dan bij het ADD-en vastlegt welke velden er zijn kun je net zo goed de velden toevoegen aan de vaste profiel gegevens.

De tweede optie INSERT en UPDATE is inderdaad nodeloos complex (vergeet ook DELETE niet).

---
Ah, snelheid was het nadeel. Dat valt volgens mij reuze mee... in MyIsam moet je namelijk
1. Alle velden ophalen
2. Voor elk veld dat in de database staat
* Of een UPDATE uitvoeren, of niets bij geen verandering
3. Voor elk veld dat wel toegevoegd moet worden maar niet in de DB staat een INSERT
4. Een DELETE uitvoeren voor de velden die nog in de DB staan maar nu leeg zijn

Aangezien het waarschijnlijk is dat de code moet draien als er daadwerkelijk wijzigingen zijn voer je zonder transactie *meer* queries uit (en introduceer je een risico op corrupte data...)

[ Voor 31% gewijzigd door T-MOB op 31-01-2008 15:28 ]

[Swaptor]

Semi-gerelateerd: waarom is er in de databaseopzet gekozen voor een dergelijk genormaliseerd ontwerp? Zeker bij persoonlijke gegevens is het vaak het geval dat de typen niet veranderen.

Persoon heeft naam, geboortedatum, adres, voorkeur, geslacht, geboorteplaats, etc. Toevoegen van een extra karakteristiek is in principe voor iedereen gelijk (iedereen heeft gelijke typen karakteristieken) dus ik snap niet goed waarom je een systeem hebt voorgesteld wat ook voorziet in scenario's waarbij persoonA slechts 1 karakteristiek heeft, en persoonN 23.
(Koppeltabellen zijn voor m->n relaties, i.t.t. de 1->n relatie die je wil modelleren)

[Niakmo]

Swaptor schreef op donderdag 31 januari 2008 @ 15:29:
Semi-gerelateerd: waarom is er in de databaseopzet gekozen voor een dergelijk genormaliseerd ontwerp? Zeker bij persoonlijke gegevens is het vaak het geval dat de typen niet veranderen.

Persoon heeft naam, geboortedatum, adres, voorkeur, geslacht, geboorteplaats, etc. Toevoegen van een extra karakteristiek is in principe voor iedereen gelijk (iedereen heeft gelijke typen karakteristieken) dus ik snap niet goed waarom je een systeem hebt voorgesteld wat ook voorziet in scenario's waarbij persoonA slechts 1 karakteristiek heeft, en persoonN 23.
(Koppeltabellen zijn voor m->n relaties, i.t.t. de 1->n relatie die je wil modelleren)

Het is een website die in verschillende branches gebruikt gaat worden. Voorbeeld:

profielen van voetbalclub leden zullen andere informatie bevatten zoals elftal, links, rechts benig terwijl een bedrijf weer totaal andere ding wil. Verder wil de een 10 velden van zijn leden opslaan en een ander 50.


Inderdaad leek me de optie om transacties te gebruiken het best, alleen ik ben nu nog in mijn zoektoch "REPLACE INTO" en "UPDATE ON DUPLICATE KEY" tegengekomen, vooral deze laatste lijkt handig alleen ik wil niet zoeken naar een duplicate key maar naar een dublicate combinatie van field_id en profile_id

[Creepy]

Wat is er op tegen om tijdens de installatie van de site de DB op maat te maken zodat je die extra twee tabellen niet nodig hebt? Wijzigingen hierop in de toekomst komen zo vaak nu ook weer niet voor en een extra kolom toevoegen in je DB is zo spannend nu ook weer niet. Scheelt je weer gelijk alle problemen waar je nu tegenaan loopt
Of sla je alle gegevens van alle sites op in 1 DB?

[ Voor 7% gewijzigd door Creepy op 31-01-2008 15:40 ]

[Niakmo]

Creepy schreef op donderdag 31 januari 2008 @ 15:39:
Wat is er op tegen om tijdens de installatie van de site de DB op maat te maken zodat je die extra twee tabellen niet nodig hebt? Wijzigingen hierop in de toekomst komen zo vaak nu ook weer niet voor en een extra kolom toevoegen in je DB is zo spannend nu ook weer niet. Scheelt je weer gelijk alle problemen waar je nu tegenaan loopt
Of sla je alle gegevens van alle sites op in 1 DB?

Er is geen installatie va nde site want het komt op een plek te draaien met inderdaad alles in 1 db, verder heeft deze manier een hoop andere voordelen die nu niet relevant zijn, ik had inderdaad overwogen om realtime tabellen te wijzigen maar dat leverde nog meer problemen op.

[Voutloos]

Niakmo schreef op donderdag 31 januari 2008 @ 15:37:
Inderdaad leek me de optie om transacties te gebruiken het best, alleen ik ben nu nog in mijn zoektoch "REPLACE INTO" en "UPDATE ON DUPLICATE KEY" tegengekomen, vooral deze laatste lijkt handig alleen ik wil niet zoeken naar een duplicate key maar naar een dublicate combinatie van field_id en profile_id

'een key', dat kan dus ook een unique index zijn en niet per se de primary key.

Oftewel, kijk inderdaad naar REPLACE of INSERT .. ON DUPLICATE KEY UPDATE queries. Her en der heb je wel te maken met mysql-only features. Let op dat REPLACE altijd een delete+insert doet en dat je dus moet opletten met oa auto_increment.

Overigens is innodb ook niet altijd trager met selects, en als je gewoon transacties nodig hebt, tja, heb je het gewoon nodig. Het is zeer handig om iig kennis genomen te hebben van de hier genoemde query types en van de verschillen tussen de belangrijkste engines (met name myisam vs. innodb).

[ Voor 8% gewijzigd door Voutloos op 31-01-2008 15:49 ]

[Niakmo]

Voutloos schreef op donderdag 31 januari 2008 @ 15:47:
[...]
'een key', dat kan dus ook een unique index zijn en niet per se de primary key.

Oftewel, kijk inderdaad naar REPLACE of INSERT .. ON DUPLICATE KEY UPDATE queries. Her en der heb je wel te maken met mysql-only features. Let op dat REPLACE altijd een delete+insert doet en dat je dus moet opletten met oa auto_increment.

Overigens is innodb ook niet altijd trager met selects, en als je gewoon transacties nodig hebt, tja, heb je het gewoon nodig. Het is zeer handig om iig kennis genomen te hebben van de hier genoemde query types en van de verschillen tussen de belangrijkste engines (met name myisam vs. innodb).

kan je dan een unique index plaatsen op een combinatie van twee colommen? want in mijn geval mag er nooit twee keer een zelfde combinatie van field_id en profile_id zijn.

[Voutloos]

Een index kan sowieso over meerdere kolommen en bij unique heb je als extraatje een constraint erbij.