Database Modeling Help - Relating Instance Fields to Instance Fields

[MattDale]

I'm working on an A/V Cable and Equipment management project and have run into an issue.  The application is functioning as designed, but I'd like to add another feature that keeps track of connections between equipment and cables.  Since the app is working as is, I'd like to fit this feature in the current schema.  

Here's a really basic models.py that I wrote to describe the problem.  (This is untested, I wrote this in order to ask the question in detail)

class Connection(models.Model):

    name = models.CharField(max_length=50)

    gender = models.CharField(max_length=20)

class Manufacturer(models.Model):

    name = models.CharField(max_length=200)

    # other fields describing the mfg location etc.

class Equipment(models.Model):

    """

    components in a rack or a speaker or a microphone; anything 

    that is connected with cables

    """

    name = models.CharField(max_length=100)

    manufacturer = models.ForeignKey(Manufacturer)

    # other fields describing the equipment type, etc.

class EquipmentConnection(models.Model):

    """

    this represents any connection on a piece of equipment
    there can be many connections per parentEquipment

    """

    parentEquipment = models.ForeignKey(Equipment)

    connection = models.ForeignKey(Connection)

    labelText = models.CharField(max_length=20)

    # other fields here relevant to labels

class Cable(models.Model):

    """

    This connects to equipment connections, OR connects to each other for extending

    OR connects to a BreakoutPair

    """

    name = models.CharField(max_length=100)

    origin_connection = models.ForeignKey(Connection, related_name="origin_connection")

    destination_connection = models.ForeignKey(Connection, related_name="destination_connection")

class CableBreakout(models.Model):

    """

    Some cables have "breakouts" or "tails" that are made up of 

    multiple channels.  

    Think of it like the 1/8" iPod cable that breaks out to the Red and White 

    RCA connectors.

    The two RCA connectors would be the breakout. This comparison is kinda crappy.

    Suffice it to say that this model needs to exist for the next model, BreakoutPair.

    """

    name = models.CharField(max_length=100)

    cable = models.ForeignKey(Cable)

    whichEndOfTheCable = models.CharField(max_length=20) # just to keep track of which side of the cable this breakout is on and to prevent duplicates

class BreakoutPair(models.Model):

    """

    there can be many of these related to each Cable Breakout

    """

    breakout = models.ForeignKey(CableBreakout)

    channel = models.CharField(max_length=20)

    labelText = models.CharField(max_length=40)

    connection = models.ForeignKey(Connection)

class PatchPoint(models.Model):

    """

    here's the question...

    I need to keep track of patch points.

    A patch point consists of two connections, A and B
    It represents the connection point between two items. 
    For example, plugging in an HDMI cable to a TV would be represented with this.
    The HDMI cable connector is pointA and the HDMI port on the TV is pointB

    The problem is that each connection can be any of the following:

    - Cable.origin_connection, Cable.destination_connection

    - BreakoutPair.connection

    - EquipmentConnection.connection

    I would like to have this as a database table so that I can check for errors

    such as "This cable connection is already patched into Equipment Connection X."

    and create reports detailing what connections are patched

    """

    pointA = ''# ? genericForeignKey? GenericForeignKey would only relate to a Cable, BreakoutPair, or EquipmentConnection instance\
    #              not a Cable, BreakoutPair or EquipmentConnection field

    pointB = ''# ? 

    # am I thinking about this wrong?  Is there a better way of handling this?

The problem comes with the PatchPoint model.  I can't devise a way to do this...

Is there a way to structure the database so that this relationship can work?  Would another database type help here, like Redis or something?

Normally, writing out the question leads me to a direction, but I'm stuck this time.

Thanks for any advice,

-Matt

[MattDale]

I figured it out, I think. 

If I create an intermediary table like this:

class PatchConnection(models.Model):
    connection = models.ForeignKey(Connection)
    content_type = models.ForeignKey(ContentType, on_delete=models.CASCADE)
    object_id = models.PositiveIntegerField()
    content_object = GenericForeignKey('content_type', 'object_id')
   

Then the PatchPoint model fields would be ForeignKey relations to PatchConnections.

I guess writing out the question helped me to figure it out.  

Thanks,