Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 2 down vote accepted

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
    }
}
share|improve this answer

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
share|improve this answer

Create a readonly property in the parent class, check whether the retrieved child id is nil den increment it or assign it from db.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.