[Answered ]-Database design with 2 m2m relationships

I’m not entirely sure I follow, but you have a few options here.

The first is to manually create the intermediate table between Outcome and DiseaseState

class DiseaseStateOutcome(models.Model):
    unique_together = (('disease_state', 'outcome'),)

    disease_state = models.ForeignKey(DiseaseState)
    outcome = models.ForeignKey(Outcome)

    #option foreign key, or m2m relation to option here

Your other option is, if you just want a single option relating to a DiseaseState/Outcome pair, is to just put a ForeignKey from Outcome to Option.

class Outcome(models.Model):
    #Your other fields here
    option = models.ForeignKey(Option)

Since I can’t see your whole class structure (Disease?, State?) I can’t get too specific, but I’ll try to talk a little about DB design.

I have a table that holds people, and I want to know their names, and what kind of car(s) they drive:

tbl_person
id int(11)
name varchar(32)

Now I could put a car column in the person table, but some people drive more than one car. So I’ll make a table with cars in it, and connect (or relate) each car with a person.

tbl_car
id int(11)
car_name varchar(32)
person_id int(11)

That way each row in the car table would have a person.id. This is a Foreign Key relationship.

Now the problem with this is that I’m going to have a bunch of columns in my car table that have duplicate data, because there will be lots of Toyotas in there, for example.

It would be better if I had every car make in a table, and every person in another with a join table between them.

So I now have tbl_person_car:

id int(11)
car_id int(11)
person_id int(11)

Notice that each row in this table contains only two Foreign Keys or FKs.
It’s really important that this join table contain nothing but the FKs needed to perform the join. Failure to do so would jeopardize the referential integrity of the whole database.

Of course, Django builds this join table for you whenever you employ a ManyToMany field in your model. So you never have to worry about it (which is nice, because one wrong move can ruin everything).

OK, sorry if that was too elementary, but I hope it explains that if you think your join table needs more data in it, then you’ve probably got an issue with your design.

I would suggest really getting comfortable with what your models are doing, and don’t worry so much about the DB. When I started with Django, I was thinking from the DB side too much, and it caused a bit of pain.

So in your case, ask yourself, “What does a disease have?” You might say “a disease has an outcome.” The has a relationship is vital to understand in OO design.

What is it, for example that has an Option? Is it a Disease, Patient, Outcome? Whatever it is, then that is probably the model to which an Outcome belongs.

Is this making sense?
I hope it helps.