Foreign Key to unknown model tables

Using Django
1

I have various component models, all with unique attributes:

class Motor(models.Model):
power = IntegerField()

class Valve(models.Model):
diameter = IntegerField()

class Pump(models.Model):
flow_rate = exampleField()

I need to query these based on component requirements to select the suitable motor, valve, pump etc and then ‘assign’ these to a ‘configuration’:

class Assign(models.Model):
configuration = ForeignKey(Configuration)
component = ForeignKey(Motor/Valve/Pump … etc)
quantity = IntegerField()

class Configuration(models.Model):
pass

I later require to be able to access the component attributes from Assign. Example:-
assign = Assign.objects.get(id=1)
assign.component
[Motor 1]
assign.component.power
55

I also require to select an assign_set via configure and then access the component attributes via those assigns. In pseudo code:
configured = Configure.objects.get(id=1)
motor = configured.assign_set.filter(component__classtype = motor)
motor.power
50

My problem being that I cannot have one component foreign key to an unknown model class in assign.

I have tried generic foreign key … it’s messy and I don’t think it will scale well; I could be wrong on that. I’ve also create a component model and then motor, valve etc inherit from that … the foreign key to component allows me to get component, but not the subclass attributes like power for motor or diameter for valve.

I would really appreciate anyone being able to help me meet these requirements, or advising the best way to achieve something close. It would be great to keep the SQL behind any solution concise and fast because the intention is for this to scale

2

There are many different ways of solving issues like this, and there’s no one-size fits all.

Right off-hand:

  • Generic Foreign Keys (they actually work quite well and can be very performant. There are also some micro-optimizations that can be performed if you run into some bottlenecks.)
  • Key-Value storage. You have one table for all components, a column to identify the component type, and one or more columns for the data types needing to be stored.
  • One parent Component table, with the specific child component tables referenced through a one-to-one relationship with the parent
  • The Assign table containing a JSON object, where each key is the name of an individual component type and the value is the FK to it.
    … and there are others.

Each one of these has their own trade-offs, a set of advantages and disadvantages. None of them are a “perfect” solution in all contexts.

Data modelling is an art. Finding the “best” solution usually requires a very detailed understanding of the problem domain.

Identifying scalability issues will depend upon a large number of different factors, not all of which are easily identifiable at start. Some of those factors may include:

  • The number of component tables
  • The number of different fields in each component table
  • The nature of the queries to be performed on these configurations
  • The number of each type of component
  • The number of configurations
  • The number of motors
  • The number of components to be used by each motor (min/expected/max)
  • The number of each component type to be used by a motor (min/expected/max)
  • The rate at which components are added to the system
  • The rate at which new motors are created
  • The rate at which motor configurations are changed
    … again, these are just the first set that popped into my head. I’m sure there are more.

Hope this gives you some ideas to work from.