CODEWITHSUNDEEP
djangoormmany-to-one
user2782711
user2782711

Reputation: 51

One to many relationships in Django ORM

I have built several database systems for managing parts databases in small electronics manufacturing setups. I am repeating the process using Django5 and MySQL. I'm a noob with Django but have used MySQL a lot in Java and Python.

Consider the tables


    class Bom(models.Model):
        bom = models.AutoField(primary_key=True);
        bompart = models.ForeignKey(Part, on_delete=models.CASCADE)
        revision = models.CharField(max_length=1)

    class BomLine(models.Model):
        bom = models.ForeignKey(Bom, on_delete=models.CASCADE)
        part = models.ForeignKey(Part, on_delete=models.CASCADE)
        designator = models.CharField(max_length=10, primary_key=True)
        ordinal = models.IntegerField()
        qty = models.DecimalField(max_digits=11, decimal_places=4)
    
        class Meta:
            constraints = [
                models.UniqueConstraint(fields=['bom', 'designator'], name='bomdesignator')]

    class Part(models.Model):
        groupid = models.ForeignKey(PartGroup, on_delete=models.CASCADE)
        partno = models.AutoField(primary_key=True)
        partdescription = models.CharField(max_length=100)

(I've omitted the PartGroup table as it is unrelated to my question). The idea is that a bill-of-materials is defined in the Bom table. The system automatically assigns an integer id, and a part is nominated as the 'parent' of this BOM - ie the BOM is the materials list need to make the nominated part.

Each BOM refers to BomLines. We can have many lines in a BOM for each component. Since there may be many of the same type of component in a BOM, a 'designator' is used to state exactly where in the assembly the part belongs. (The 'ordinal' field is only used for sorting purposes and can be ignored).

Normally I would make a combined key for Bom and Designator in my MySQL table. However the Django ORM objects to this. To try and get around the multiple fields as keys restriction, the UniqueConstraint was added to the BomLine class. When I add a designator 'U1' to bom 1, then add 'U1' to bom 2, there is an issue because 'U1' is already used as a key. If I take away the primary_key=True parameter to designator, Django adds an AutoField id to the table. I thought this should work, except that I have to nominate a default value for the ID. The MySQL engine does not allow the default value and this will not work either.

It seems to me that either my thinking is wrong (I have a way of structuring the relationship that is in some way "not normal") - which is implied by the way Django is making me do things. Alternatively, the MySQL backend is not suited for this type of situation and perhaps I should use something else?

Upvotes: 2

Views: 69

Answers (1)

user2782711
user2782711

Reputation: 51

The comments started to deviate from the problem, so I took another look. There are a number of SO posts about the problems of using Django models with more than one primary key. This remains a bewilderment to me as it is a database construct I have frequently seen and used. I don't think there is any database theory that prevents it, and certainly my experience with MySQL and PostgreSQL allow me to create such tables. They have real use. Django can't model them, maybe someone could clarify why this has to be?

The solution I employed and will continue to use as a pattern is to create an 'id' column:

class bomline(models.Model):
    bomline_id = models.UUIDField(primary_key=True, null=False, default=uuid.uuid1, editable=False, serialize=True)
    bom = models.ForeignKey(Bom, on_delete=models.CASCADE)
    part = models.ForeignKey(Part, on_delete=models.CASCADE)
    designator = models.CharField(max_length=10)
    ordinal = models.IntegerField()
    qty = models.DecimalField(max_digits=11, decimal_places=4)

    class Meta:
        constraints = [
            models.UniqueConstraint(fields=['bom', 'designator'], name='bomdesignator')]

Using a uuid ensures that a unique value is used for the key, while the two foreign keys ensure that the parts and bom entries exist. Then a UniqueConstraint prevents double entry.

It seems long winded. Upon reflection, the foreign keys manage situations where bom lines could be orphaned by deletions in parts or bom tables. Maybe it is a better way.

Upvotes: 0

Related Questions