We have a problem in our schema design for our database that I would like to figure out the cleanest way to solve. The three models are defined in these simplified Django model classes:
class Animal(Model):
name = CharField()
infusion_rate = FloatField()
infusate = ForeignKey(Infusate) # but with specific tracer concentrations?
class Infusate(Model):
name = CharField()
tracers = ManyToManyForeignKey(Tracer)
class Tracer(Model):
name = CharField()
Currently, we have a through model for the Infusate:Tracer M:M relationship that adds a concentration float field:
class InfusateTracer(Model):
infusate = ForeignKey(Infusate)
tracer = ForeignKey(Tracer)
concentration = FloatField()
but we need to link the Animal model to a single infusate, but with specific concentrations for each of its multiple tracers. In other words, how do I relate an animal to a single infusate, but multiple tracers with specific concentrations belonging to that one infusate?
I was thinking that I could add a field to InfusateTracer and have Animal link to that instead. Let's call it animal, so:
class InfusateTracer(Model):
infusate = ForeignKey(Infusate)
tracer = ForeignKey(Tracer)
concentration = FloatField()
animal = IntegerField()
but how do I enforce that the relationship between infusate and animal fields in the InfusateTracer model is M:1 (i.e. InfusateTracer.animal never co-occurs with multiple different InfusateTracer.infusate values (but multiple InfusateTracer.tracer values))? Is there some sort of unique constraint I can use to enforce that?
From a different angle, I could create an AnimalTracer linking/through model, but then I would have the same difficulty of retrieving a single unique infusate name associated with the animal.
Trying to figure out the most elegant solution that maintains data integrity is melting my brain.
I was thinking about this again this morning and I had an alternate idea.
To think of the problem another way, I want to be able to link an animal to an infusate (which defines a set of tracers without concentrations). That is one link. When I have an animal, I want to essentially add an annotation to the InfusateTracer table (but I cannot put it directly in that table). It is as if I want to add a concentration field to the InfusateTracer table, so to do it without changing that table, I just have to link to it... So that's what I should do instead of independently linking the animal table to the tracer table to define the concentrations, I should link the animal table to the InfusateTracer table using a linking table (because it is a many to many relationship). In other words, I am annotating the links between infusate and tracer. The relationships would look like this:
class Animal(Model):
name = CharField()
infusion_rate = FloatField()
infusate = ForeignKey(Infusate)
class Infusate(Model):
name = CharField()
tracers = ManyToManyForeignKey(Tracer)
class Tracer(Model):
name = CharField()
class InfusateTracer(Model):
infusate = ForeignKey(Infusate)
tracer = ForeignKey(Tracer)
class AnimalInfusateTracer(Model):
animal = ForeignKey(Animal)
infusate_tracer = ForeignKey(InfusateTracer)
concentration = FloatField()
I think this is the answer because I don't have to worry about synchronizing two sets of multiple independent links from infusate to tracer and from animal to tracer (so that they each link to the same set).
When I am loading data, all I have to worry about is looking for infusatetracer records That have the correct infusate and correct tracer!