Say I’m developing a bug tracker, where a ticket belongs to at most one milestone, and a milestone can have many tickets. When a milestone is deleted (from the database), all tickets associated with that milestone must have their relationship unset (i.e. set to null). This is a simple relationship.
The classes are fairly simple, and map directly to a database:
public class Ticket {
public ObjectId Id { get; set; }
public string Title { get; set; }
public ObjectId MilestoneId { get; set; }
}
public class Milestone {
public ObjectId Id { get; set; }
public string Title { get; set; }
}
Now comes the tricky part: where and how do I reset the MilestoneId fields? One possible solution is to write a data access layer by creating one giant class with all data accessing methods for all kinds of objects, in this case tickets and milestones. Another possible solution is to write several classes (e.g. TicketProvider and MilestoneProvider), one for each kind of object, that provide data accessing methods such as Find, Save and Destroy.
The latter appeals more to me since I don’t like monsterous classes, but there is one caveat: the method that deletes milestones has to reset the MilestoneIds of all associated tickets to null. This means that MilestoneProvider, which is responsible for manipulating milestones, suddenly deals with tickets!
How are relationships commonly dealt with in data access layers, and how can I prevent violation of SRP? Should I put the entire DAL in one class, or should I separate it and if so, how can I best do that?
I would suggest you encapsulate that logic in the place where you can actually delete the milestone since the removal of its ID from the tickets is an implementation detail of the business logic at the point of deletion. The important thing is to do it in a single obvious place in a transaction so that either everything happens or nothing happend.
If you are using the repository pattern, you could do something along these lines:
void Delete(int milestoneId)
{
db.BeginTransaction();
db.Execute("UPDATE Tickets SET MilestoneID = null WHERE MilestoneID = @p0", milestoneId);
db.Execute("DELETE FROM Milestones WHERE MilestoneID = @p0", milestoneId);
db.Commit();
}
It’s subject to personal preference a little here as you could argue that the MilestoneRepository shouldn’t know about Tickets but depending on how many other requirements you have along these lines (e.g. if there aren’t many) it might be acceptable for now. The other option is to create a service which has this knowledge like this:
class MilestoneRepository
{
void Delete(int milestoneId)
{
db.Execute("DELETE FROM Milestones WHERE MilestoneID = @p0", milestoneId);
}
}
class TicketRepository
{
void DetachAllFromMilestone(int milestoneId)
{
db.Execute("UPDATE Tickets SET MilestoneID = null WHERE MilestoneID = @p0", milestoneId);
}
}
class MilestoneService
{
void Delete(Milestone milestone)
{
unitOfWork.Begin(); // start transaction
ticketRepository.DetachAllFromMilestone(milestone.Id);
milestoneRepository.Delete(milestone.Id);
unitOfWork.Complete(); // commit transaction
}
}
0
As an alternative to doing it in middle-tier business logic code, I offer up a Microsoft SQL Server trigger:
CREATE TRIGGER dbo.DeleteMilestone
ON dbo.Milestone
INSTEAD OF DELETE
AS
BEGIN TRANSACTION
UPDATE t
SET t.MilestoneId = NULL
FROM dbo.Ticket t
INNER JOIN DELETED d ON t.MilestoneId = d.Id;
DELETE m
FROM dbo.Milestone m
INNER JOIN DELETED d ON m.Id = d.Id;
COMMIT TRANSACTION
GO
Create a readonly property in the parent class, check whether the retrieved child id is nil den increment it or assign it from db.