Django cross table model structure

Question

I have a System model, and an Interface model. An Interface is the combination between two systems. Before, this interface was represented as an Excel sheet (cross table). Now I'd like to store it in a database.

I tried creation an Interface model, with two foreign keys to System. This doesn't work because :

• It creates two different reverse relationships on the target model
• It doesn't avoid having duplicates (first and second rel swapped)

I used this code :

class SystemInterface(Interface):
    assigned_to = models.ManyToManyField(User)
    first_system = models.ForeignKey(System)
    second_system = models.ForeignKey(System)

Isn't there a better way to do this ?

I'd need to have symmetrical relations : it should'nt matter is a System is the "first" or "second" in an Interface.

Answer 1

I think the best simpliest to represent those models would be like this:

class System(models.Model):
    pass

class Interface(models.Model):
    assigned_to = models.ManyToManyField(to=User)
    system = models.ForeignKey(System)

    @property
    def systems(self):
        Interface.objects.get(system=self.system).interfacedsystem_set.all()         

class InterfacedSystem(models.Model):
    interface = models.ForeignKey(Interface)
    system = models.ForeignKey(System)

The add/remove of interfaced system is obviously left as an exercise to the reader, put should be fairly easy.

Answer 2

You can use a many to many relationship with extra fields, but it can't be symetrical.

The table used for a many to many relation contain a row per relation between 2 models. The table used for a many to many relation from System to self, has one row per relation between two Systems. This is consistent with the fact that your model fits the structure of a model used for ManyToManyField.through.

Using an intermediary model allows to add fields like assigned_to to the many to many table.

It might be tricky to understand, but it should prevent the creation of SystemInterface(left_system=system_a, right_system=system_b). Note that I changed "first" by "left" and "second" by "right" for the purpose of representing a many to many relation row/instance, which has a "left" side and a "right" side.

Because they can't be symetrical, this won't solve the problem of having one SystemInterface(left_system=system_a, right_system=system_b) and one with SystemInterface(left_system=system_b, right_system=system_a). You should prevent that from happening in the clean() method of the SystemInterface - or any model used to represent the many to many table with a ManyToManyField.through model.

Answer 3

Since django doesn't support symmetrical many-to-many relationships with extra data, you probably need to enforce this yourself.

If you have a convenient immutable value in the system (e.g. system id), you can create a predictable algorithm for which system will be stored in which entry in your table. If the systems are always persistent by the time you create the Interface object, you can use the primary key.

Then, write a function to create the interface. For example:

class System(models.Model):
def addInterface(self, other_system, user):
    system_interface = SystemInterface()
    system_interface.assigned_to = user
    if other_system.id < self.id:
        system_interface.first_system = other_system
        system_interface.second_system = self
    else:
        system_interface.first_system = self
        system_interface.second_system = other_system
    system_interface.save()
    return system_interface

Using this design, you can do the usual validation, duplication detection, etc. on the SystemInterface object. The main point is that you enforce the constraint in your code rather than in the data model.

Does this make sense?