Django: Need help with DB schema (ManyToMany, ForeignKey relations)

I'm new to both Django and MySQL, so I apologize if this ends up being a simple question.

I'm working on a project management site. Say we have a Program. Each program has some number of Milestones, which in turn have some number of Tasks that need to be completed for the Milestone to be reached. Milestones will be the same across different Programs, but there is no guarantee that the tasks required for each milestone will stay the same across Programs.

Here's an (abbreviated) models.py:

class Program(models.Model):
    # ...
    complete = models.BooleanField()
    milestones = models.ManyToManyField(Milestone, through='ProgramMilestone')

class Milestone(models.Model):
    # ...

class Task(models.Model):
    # ...
    complete = models.BooleanField()
    milestone = models.ForeignKey(Milestone)

class ProgramMilestone(models.Model):
    # ...
    complete = models.BooleanField()
    program = models.ForeignKey(Program)
    milestone = models.ForeignKey(Milestone)

I have ...through='ProgramMilestone' because Programs should be allowed to share Milestones but have a per-program completion status.

The problem with this schema is something like this:

• We have two programs, Program 1 and Program 2.
• Program 1 has a Milestone A, and Program 2 also has a Milestone A.
• Add a task to Program 1's Milestone A.
• Unwanted consequence: the task is now also attached to Program 2's Milestone A.

The solution might be to create two "Milestone A"s in the Milestone database: two distinct rows that share the name "Milestone A." However, considering they would only differ in id while being the same conceptual item, this seems like a waste.

Another idea might be to require both a ProgramID and a MilestoneID when adding a new Task, but then I'm not sure one might add new Tasks in the Django Admin - that is, how to pass the ProgramID of the Program that the user is currently looking at.

How might I adapt my models.py so that Programs can share Milestones while having a per-Program set of Tasks for each Milestone (i.e. avo开发者_开发问答iding the above scenario)?

---

Since you want Tasks to be related to Program's Milestone and not to Milestones, you could change class Task to:

class Task(models.Model):
    # ...
    complete = models.BooleanField()
    programMilestone = models.ForeignKey(ProgramMilestone)

---

From my (outside) perspective, the nomenclature is not really what you want to be defining your database schema on. Milestone A under Program 1 seems like, although it's named the same, can have a completely different set of Tasks as the "same" Milestone A under Program 2.

What I suggest you do is distinguish between a key name and a display name. For example, on the Milestone model you can have 2 names: MilestoneKey, which would be internally used and stored as something like "Program 1 Milestone A", and a MilestoneName, which would be something like "Milestone A".

In your application, users would only ever see the MilestoneName, whereas internally you can keep track of it as MilestoneKey.

Or, to avoid that complexity, just have the MilestoneName field and call Program 2's Milestone A becomes Milestone X, or Program 2 - Milestone A.