[MSSQL] Beste opzet comments tabel / trigger probleem

[Verwijderd]

UPDATE: opgelost middels een andere opzet, zie laatste post.

Voor een project ben ik bezig om de database op te zetten. Het gaat om een soort app. In deze app zijn verschillende zaken waarop gereageerd kan gaan worden. Denk aan commentaar op muziektracks, commentaar op muziekalbums, commentaar op tweets (etecetra).

Nu lijkt het mij een goede zaak om 1 algemene comments tabel op te zetten voor al deze zaken. Alleen moet deze tabel wel relaties hebben met de verschillende andere tabellen. Ergens op het internet las ik een elegante manier om dit op te lossen, met een soort tussentabel. Intussen heb ik dat als volgt gemaakt:



Elke tabel waarop gereageerd kan worden (in dit geval tblTrack en tblAlbum), heeft een relatie met de tabel tblCommentRelation middels een Foreign Key (FK_CommentRelationID). De tussentabel (tblCommentRelation) heeft weer een directe relatie met tblComment. Het is de bedoeling dat er een nieuwe row wordt ge-insert bij tblCommentRelation voor elke insert in tblTrack en tblAlbum. Zodra iemand ergens daadwerkelijk op reageert, komt er een post bij in de tabel tblComment.

Dit lijkt mij de beste oplossing, hierbij is er immers sprake van echte relaties. Mocht iemand overigens een betere weten, dan hoor ik het graag...

Maar verder:
Middels trigger logica wil ik er dus voor zorgen dat er een nieuwe row in tblCommentRelation wordt gemaakt bij elke insert in tblAlbum of tblTrack.

Om de referential integrity te waarborgen, mogen de foreign keys in tblAlbum en tblTrack niet NULL zijn. Bij een insert in bijvoorbeeld tblTrack voer ik alleen de TrackName in. De triggercode moet vervolgens de row in tblCommentRelation aanmaken, de PK daarna ophalen en vervolgens deze vervolgens samen met mijn TrackName inserten...

Ik vind het moeilijk helder te omschrijven, hopelijk is het tot dusver duidelijk...

Volgens mij is de enige mogelijke oplossing voor dit probleem gebruik te maken van een INSTEAD OF insert. Probleem is namelijk dat de trigger nooit zal vuren als ik een FOR insert gebruik omdat ik geen NULL values mag inserten. De INSTEAD OF trigger moet deze NULL waarde dus gaan vullen met de PK uit tblCommentRelation, en daarna de werkelijke insert gaan doen.

Intussen heb ik deze code geschreven:

SQL:

USE [TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER .[dbo].[BI_tblTrack_CommentRelation] 
ON  [dbo].[tblTrack] 
INSTEAD OF INSERT
AS 
BEGIN
  SET NOCOUNT ON;
  DECLARE @int int;
  Insert into tblCommentRelation (CommentRelationSource) VALUES ('tblTrack');
  SET @int = Scope_identity()
  PRINT @int;

  --inderdaad, de variable print heeft nu de scope_identity waarde...
END

Verder kom ik helaas niet. Ik mag de virtual INSERTED table niet wijzigen. Ik ga nog tientallen velden toevoegen, en wil niet bij elk nieuw veld de trigger moeten aanpassen. Dus indien mogelijk wil ik niet in code een table variable gaan aanmaken met velden (want deze zal ik dan dus bij elke wijziging moeten aanpassen). Bovendien ben ik bang dat deze code sowieso op zijn bek gaat bij een batch insert... Heb vele fora bezocht, maar heb niet een oplossing kunnen vinden. Kan iemand mij verder helpen?

[Hipska]

Ik zou net omgekeerd werken, want nu zijn je tabellen afhankelijk van die relatie tabel.

Ik zou in de relatie tabel een foreign key naar elke tabel waar op gereageerd mag worden leggen. Zo is je basis van het systeem niet afhankelijk van het comments systeem.

Als een gebruiker dan een comment plaatst op een track komt enkel in deze FK een waarde te staan, de andere zijn null.

[Verwijderd]

Bedankt.

Hmm ja, daar heb ik uiteraard ook aan gedacht, en het maakt alles wel een stuk eenvoudiger. Ergens heb ik echter het idee dat deze methode niet de meest nette is... alleen ben ik geen ervaren database designer, meer een soort prutser

Dus... zijn er nog mensen die hier iets inhoudelijks aan toe kunnen voegen, of zou ik gewoon de commentrelation table verwijderen en tblComments moeten voorzien van een aantal foreignkey relations?

[bindsa]

Even een nieuwsgierig vraagje: Wat voor programma gebruik je in je topicstart om het DB model mee te maken?

[Verwijderd]

L0calh0st schreef op zaterdag 12 juni 2010 @ 19:55:
Even een nieuwsgierig vraagje: Wat voor programma gebruik je in je topicstart om het DB model mee te maken?

Dat zit gewoon in Microsoft SQLServer ingebakken. Sinds versie 2000 (misschien al eerder) kan je een database diagram aanmaken en visueel bewerken. Deze feature zit volgens mij niet in de express edities. Overigens is Microsoft Visio ook wel een aardige tool om even snel een ontwerp te maken.

Nog wat vragen overigens over de suggestie van Hipska. Mijn kennis op triggercode gebied is te klein, en met de informatie die ik op het internet vind, kom ik er niet uit.

Als ik de volgende opzet zou maken:

tblComment
CommentID
ParentCommentID
FK_TrackID
FK_AlbumID

Zit ik nog met de volgende probleem:

Delete acties moeten gecascade worden. Delete ik een Track, dan moet bijbehorend commentaar verwijderd worden (CommentID's), inclusief childrows (ParentCommentID's)

Ik mag geen ON DELETE CASCADE doen op de relatie CommentID - ParentCommentID. Dit is een complexe query om met trigger logica op te lossen, omdat je recursief door je rows heen zou moeten lopen (denk ik). Mijn TSQL kennis is helaas nog te klein om dit zelfstandig op te lossen...

Iemand een suggestie?

[Hipska]

Simpel; gewoon bij die comment die een parent heeft ook het FK invullen zodat hij ook mee gedelete wordt?

En waarom zou je geen on delete cascade mogen doen bij een relatie naar een rij in dezelfde tabel?

[Voutloos]

Als je toch al een kolom per entity type waarop gereageerd kan worden maakt, waarom dan niet gewoon een comment tabel per entity?

Niet dat dat de heilige graal is, maar ik ben benieuwd hoe je over die aanpak denkt.

[Verwijderd]

Voutloos schreef op zaterdag 12 juni 2010 @ 21:08:
Als je toch al een kolom per entity type waarop gereageerd kan worden maakt, waarom dan niet gewoon een comment tabel per entity?

Niet dat dat de heilige graal is, maar ik ben benieuwd hoe je over die aanpak denkt.

Er zullen ongeveer 10 zaken zijn waarop gereageerd kan worden. Bijvoorbeeld op nieuws, tweets, muzieknummers, foto's etcetera.

Ik ga toch niet 10x voor deze zaken een commentstabel opzetten? Dat lijkt me niet de bedoeling

Hipska schreef op zaterdag 12 juni 2010 @ 21:02:
Simpel; gewoon bij die comment die een parent heeft ook het FK invullen zodat hij ook mee gedelete wordt?

En waarom zou je geen on delete cascade mogen doen bij een relatie naar een rij in dezelfde tabel?

Helaas, dat begrijp ik niet? Misschien heb ik het niet goed uitgelegd?

Stel, dit is de situatie:

tblTrack

TrackID  |  TrackName
01          Leuk nummer
02          Best lekker

tblComment

CommentID | ParentCommentID | FK_TrackID | Commentaar
01          NULL              01           Nou inderdaad
02          01                NULL         Denk je?
03          02                NULL         Valt wel mee hoor....

Als ik nu track met ID 1 verwijder, zou de database middels constraints of trigger logica automatisch de 3 commentrows mee moeten verwijderen.

En nee, in SQLServer is het niet mogelijk om een referential integrity binnen dezelfde tabel aan te leggen met de volgende reden:

SQL Server does support chained CASCADE updates, but does not allow one table to participate more that once in a chain (i. e. does not allow loops).

SQL Server, unlike most other engines, optimizes cascading DML operations to be set-based which requires building a cycle-free DML order (which you can observe in the execution plan). With the loops, that would not be possible.

[Hipska]

Verwijderd schreef op zaterdag 12 juni 2010 @ 21:33:
[...]


Helaas, dat begrijp ik niet? Misschien heb ik het niet goed uitgelegd?

Stel, dit is de situatie:

tblTrack

TrackID  |  TrackName
01          Leuk nummer
02          Best lekker

tblComment

CommentID | ParentCommentID | FK_TrackID | Commentaar
01          NULL              01           Nou inderdaad
02          01                NULL         Denk je?
03          02                NULL         Valt wel mee hoor....

Dan bedoel ik dit:

tblComment

CommentID | ParentCommentID | FK_TrackID | Commentaar
01          NULL              01           Nou inderdaad
02          01                01           Denk je?
03          02                01           Valt wel mee hoor....

Want het zijn natuurlijk alledrie comments over trackID 1

[Verwijderd]

Ok hipska, jouw oplossing zou inderdaad werken -mits- de trackID altijd keurig wordt ingevuld... Anyway ben intussen gelukkig alweer heel wat wijzer geworden, en de opzet wordt vermoedelijk geheel anders. Ben aan het testen en proberen.

Mocht ik in mijn opzet slagen, dan post ik mijn nieuwe opzet. Voor feedback, en om voor de mede-tweaker met vergelijkbare problemen in de toekomst hopelijk eenvoudiger te maken.

En mods: uiteraard is deze post niet met de intentie om mijn topic een refresh te geven, maar vond dat ik even een update moest geven.

[Verwijderd]

[edit: 2 foutjes gefixed]

Ok... Andere opzet. Werkt volgens mij prima. Enige "spannende" was een trigger schrijven om childrows uit tblComment te verwijderen, maar na lang zoeken heb ik iets vergelijkbaars in expert-exchange gevonden.

Goed, opzet zoals nu:



tblTest is in dit geval een tabel waarop je zou willen reageren. Het zou dus om tblNieuws kunnen gaan bijvoorbeeld, zolang de after_delete trigger maar wordt toegevoegd. Er is namelijk geen harde foreign-key relatie tussen tblTest en tblComment.

tblCommentType slaat het "type" comment op. In dit geval: 1=test

Ok, dit is de trigger op tblComment die ervoor zorgt dat childrows altijd netjes mee worden gedelete:

SQL:

USE [jouwdatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[ID_removeChildRecords] ON [dbo].[tblComment]  
INSTEAD OF DELETE AS

CREATE TABLE #tblRemove(
  CommentID INT
);

INSERT #tblRemove 
SELECT CommentID from DELETED

CREATE TABLE #tblRemoveOrder(
  CommentID INT,
  RemoveOrder INT
)

DECLARE @CommentID INT;
DECLARE @RemoveOrder INT;
DECLARE @RemoveID INT;
SET @RemoveID = 0; 

WHILE ((SELECT COUNT(*) FROM #tblRemove) > 0) --Table not empty yet?
BEGIN
      DECLARE crsRemove CURSOR FOR SELECT TOP 1 CommentID FROM #tblRemove;  
      OPEN crsRemove 
      FETCH NEXT FROM crsRemove INTO @CommentID;

            --PRINT 'crsRemove ' + CAST(@CommentID AS VARCHAR)
            DELETE FROM #tblRemove WHERE CommentID = @CommentID

            INSERT INTO #tblRemove(CommentID)
            SELECT CommentID FROM tblComment WHERE CommentParentID = @CommentID
            
            SET @RemoveID = @RemoveID + 1
            INSERT #tblRemoveOrder VALUES(@CommentID,@RemoveID);
      CLOSE crsRemove
      DEALLOCATE crsRemove
END

DECLARE crsRemoveMore CURSOR FOR SELECT CommentID,RemoveOrder
FROM #tblRemoveOrder ORDER BY RemoveOrder DESC;  

OPEN crsRemoveMore 
FETCH NEXT FROM crsRemoveMore INTO @CommentID,@RemoveOrder
WHILE (@@FETCH_Status = 0)
BEGIN 
      --PRINT 'REMOVE:' + CAST(@CommentID AS VARCHAR)-- + CAST(@RemoveOrder AS VARCHAR)

  DELETE FROM tblComment WHERE CommentID = @CommentID
  FETCH NEXT FROM crsRemoveMore INTO  @CommentID,@RemoveOrder
END
CLOSE crsRemoveMore
DEALLOCATE crsRemoveMore
DROP TABLE #tblRemoveOrder

NB: zet recursive triggers aan op de database:

SQL:

ALTER DATABASE jouwdatabase SET RECURSIVE_TRIGGERS ON

En dan nu op elke "tblTest" nog de volgende trigger toevoegen:

SQL:

USE [jouwdatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER .[dbo].[AD_tblTest_CommentRelation] 
ON  .[dbo].[tblTest] 
AFTER DELETE
AS 
BEGIN
    SET NOCOUNT ON;

    DELETE FROM tblComment
  Where FK_CommentTypeID = 1 AND CommentRelationID in (select TestID from deleted)

END

Uiteindelijk is dit volgens mij een erg nette en tevens schaalbare oplossing geworden. Maar ik houd me altijd aanbevolen voor suggesties of commentaar