Basic understanding of models.py

[paddy-4483]

Hello, I have a basic question about dealing with the models. Let’s assume I have the following models:

Project
System
Department
Employee
Responsibility
Problem

• System includes the FK from Project.
• Employee has a many-to-many relationship with Department.
• A responsibility is a combination of the Project_ID, System_ID, Department_ID and Employee_ID fields.
• A problem belongs to a responsibility.

What should the model for the problem now look like? Is it enough to use the FK from Responsibility plus my new columns? I could already establish the relationship 100% like this in my opinion. Or do I take all the FK from the Responsibility Model plus the FK for the Responsibility Model itself and my new columns?

For example, I would like to be able to filter all problems for a project in my template. Does this work if you just take the FK out of the responsibility model?

The same applies to the responsibility model itself. The system depends on the project, so do I still need the FK for the project in the model?

Best regards
Patrick

[KenWhitesell]

I’m not sure I’m quite following the way you’re expressing usage of foreign keys here.

The common expression of the use of a foreign key is that a model may have a foreign key to another model, you don’t “get” a foreign key from a model.

@paddy-4483

Is it enough to use the FK from Responsibility plus my new columns?

If this is precisely accurate:

@paddy-4483

A problem belongs to a responsibility.

Then yes, having an FK to Responsibility in Problem would be appropriate and sufficient.

@paddy-4483

A responsibility is a combination of the Project_ID, System_ID, Department_ID and Employee_ID fields.

This could be created as a model where Responsibility has FKs to all of Project, System, Department, and Employee.

(Side question: Is it true that an Employee may be related to multiple Department? In the “common case”, an Employee is a member of (is related to) a single Department.)

[paddy-4483]

Hi Ken, thank you very much for your comments. I’ll try to explain it a little more precisely. This is my models.py file at the moment. I hope the names are comprehensible to you, they are in German. I can also adjust it if necessary.

from django.db import models


class Projekt(models.Model):
    name = models.CharField(max_length=100, blank=False, null=False, unique=True)
    kostentraeger = models.CharField(max_length=6, blank=False, null=False, unique=True)
    erstellt = models.DateTimeField(auto_now_add=True)
    aktualisiert = models.DateTimeField(auto_now=True)

    class Meta:
        verbose_name_plural = 'Projekte'

    def __str__(self):
        return f'{self.kostentraeger} {self.name}'
    
class Baugruppe(models.Model):
    projekt = models.ForeignKey(Projekt, on_delete=models.CASCADE)
    nummer = models.CharField(max_length=4, blank=False, null=False)
    name = models.CharField(max_length=100, blank=False, null=False)
    erstellt = models.DateTimeField(auto_now_add=True)
    aktualisiert = models.DateTimeField(auto_now=True)

    class Meta:
        verbose_name_plural = 'Baugruppen'
        constraints = [
            models.UniqueConstraint(fields=['projekt', 'nummer'], name='unique_system')
        ]

    def __str__(self):
        return f'{self.nummer} {self.name}'
    

class Abteilung(models.Model):
    name = models.CharField(max_length=10, blank=False, null=False, unique=True)
    erstellt = models.DateTimeField(auto_now_add=True)
    aktualisiert = models.DateTimeField(auto_now=True)

    class Meta:
        verbose_name_plural = 'Abteilungen'

    def __str__(self):
        return self.name
    

class Mitarbeiter(models.Model):
    abteilung = models.ManyToManyField(Abteilung)
    name = models.CharField(max_length=100, blank=False, null=False, unique=True)
    erstellt = models.DateTimeField(auto_now_add=True)
    aktualisiert = models.DateTimeField(auto_now=True)

    class Meta:
        verbose_name_plural = 'Mitarbeiter'

    def __str__(self):
        return self.name


class Verantwortlichkeit(models.Model):
    projekt = models.ForeignKey(Projekt, on_delete=models.CASCADE, blank=False, null=False)
    baugruppe = models.ForeignKey(Baugruppe, on_delete=models.CASCADE, blank=False, null=False)
    abteilung = models.ForeignKey(Abteilung, on_delete=models.CASCADE, blank=False, null=True)
    mitarbeiter = models.ForeignKey(Mitarbeiter, on_delete=models.SET_NULL, blank=True, null=True)
    erstellt = models.DateTimeField(auto_now_add=True)
    aktualisiert = models.DateTimeField(auto_now=True)

    class Meta:
        verbose_name_plural = 'Verantwortlichkeiten'
        constraints = [
            models.UniqueConstraint(fields=['projekt', 'baugruppe', 'abteilung', 'mitarbeiter'], name='unique_responsibility')
        ]

    def __str__(self):
        return f'{self.projekt} | {self.baugruppe} | {self.abteilung} | {self.mitarbeiter}'


class Problem(models.Model):
    verantwortlichkeit = models.ForeignKey(Verantwortlichkeit, on_delete=models.CASCADE, blank=False, null=False)
    beschreibung = models.TextField(max_length=2000, blank=False, null=False)
    erstellt = models.DateTimeField(auto_now_add=True)
    aktualisiert = models.DateTimeField(auto_now=True)

    class Meta:
        verbose_name_plural = 'Probleme'
    
    def __str__(self):
        return f'{self.verantwortlichkeit} | {self.beschreibung}'


class Loesung(models.Model):
    problem = models.ForeignKey(Problem, on_delete=models.CASCADE, blank=False, null=False)
    beschreibung = models.TextField(max_length=2000, blank=False, null=False)
    erstellt = models.DateTimeField(auto_now_add=True)
    aktualisiert = models.DateTimeField(auto_now=True)

    class Meta:
        verbose_name_plural = 'Loesungen'

    def __str__(self):
        return f'{self.problem} | {self.beschreibung}'
    

class Status(models.Model):
    projekt = models.ForeignKey(Projekt, on_delete=models.CASCADE, blank=False, null=False)
    baugruppe = models.ForeignKey(Baugruppe, on_delete=models.CASCADE, blank=False, null=False)
    abteilung = models.ForeignKey(Abteilung, on_delete=models.CASCADE, blank=False, null=True)
    mitarbeiter = models.ForeignKey(Mitarbeiter, on_delete=models.SET_NULL, blank=True, null=True)
    FARBEN = (
        ('Rot', 'Rot'),
        ('Gelb', 'Gelb'),
        ('Grün', 'Grün'),
    )
    ampel = models.CharField(max_length=4, choices=FARBEN, blank=False, null=False, default='Grün')
    erstellt = models.DateTimeField(auto_now_add=True)
    aktualisiert = models.DateTimeField(auto_now=True)

    class Meta:
        verbose_name_plural = 'Status'

    def __str__(self):
        return self.ampel

Should the model for the status look more like this? Actually, the status also depends on responsibility.

class Status(models.Model):
    verantwortlichkeit = models.ForeignKey(Verantwortlichkeit, on_delete=models.CASCADE, blank=False, null=False)
    #projekt = models.ForeignKey(Projekt, on_delete=models.CASCADE, blank=False, null=False)
    #baugruppe = models.ForeignKey(Baugruppe, on_delete=models.CASCADE, blank=False, null=False)
    #abteilung = models.ForeignKey(Abteilung, on_delete=models.CASCADE, blank=False, null=True)
    #mitarbeiter = models.ForeignKey(Mitarbeiter, on_delete=models.SET_NULL, blank=True, null=True)
    FARBEN = (
        ('Rot', 'Rot'),
        ('Gelb', 'Gelb'),
        ('Grün', 'Grün'),
    )
    ampel = models.CharField(max_length=4, choices=FARBEN, blank=False, null=False, default='Grün')
    erstellt = models.DateTimeField(auto_now_add=True)
    aktualisiert = models.DateTimeField(auto_now=True)

The question is whether my models are so right and efficient or whether you would do it differently. With the foreign keys, I put it somewhat colloquially.

You’re actually right about the department. The requesters call it a department, but it’s actually more of a mix of departments and teams. Therefore, an employee can have multiple entries.

Best regards
Patrick

[KenWhitesell]

Side note: The German names are fine, as long as you’re consistent with using them. (For example, I don’t know how “Loesung” translates properly to English - Google tells me “Solution”, but I might guess that “Answer” may also be appropriate. Anyway, as long as you refer to that model as Loesung and not “Solution” or “Answer”, I can follow along.)

Following the chain of dependencies here, you have:

Baugruppe -> Project
Mitarbeiter <=> Abteilung
Verantworthlichkeit -> Project
                    -> Baugruppe
                    -> Abteilung
                    -> Mitarbeiter
Problem -> Verantworthlichkeit
Loesung -> Problem

So coming back to your question:

@paddy-4483

The question is whether my models are so right and efficient or whether you would do it differently.

This isn’t sufficient information yet to answer that question.

Specifically, you show a foreign key from Status to both Projekt and Baugruppe, while Baugruppe also has a foreign key to Projekt. Are these two references to Projekt always going to be a reference to the same Projekt?

Logically, the answer can be either “yes” or “no” - this is a “Business rule” decision.

If the answer is “yes”, the Projekt being referenced by a Status must always be the same Projekt that is referenced by the Baugruppe that the Status is related to, then you do not want a foreign key in Status to Projekt - you would access the Projekt through the reference to Baugruppe.

What you don’t want to do is have multiple paths through your relationships between any two entities, where there’s the possibility of having inconsistencies in your data. (You don’t want to have Status refer to “Project A”, if that same Status is referring to “Baugruppe B” that refers to “Project B”, when those references should be the same.)

In other words, if the obvious transitive relationships are defining a consistent tree structure, the only foreign keys needed in status are to Baugruppe and Mitarbeiter, since you can retrieve the Projekt from Baugruppe and the Abteilung from the related Mitarbeiter.

On the other hand, if the answer is “no”, and the transitive relationships do not form a consistent tree structure, then you do need all those foreign keys to define the proper relationships.

Either answer is valid, but the answer depends upon your project’s requirements.

[paddy-4483]

Hi Ken, thank you very much for your comments. I think you understood my problem 100% and answered it. I’m now adjusting my models accordingly and removing a few unnecessary foreign keys. You’re right about the status, for example, it’s always the same project.

Now I just have one additional question. If I now want to render data from the status in the template and also output information from the project and assembly, how do I do that?

For example, the data set should look like this:

Status = ‘Grün’ (coming from the Status model)
Nummer = ‘0170’ (coming from the Baugruppe model)
Kostentraeger = ‘154789’ (coming from the Projekt model)

Do you create a queryset for this? If so, what would an example of something like this look like?

Best regards
Patrick

[KenWhitesell]

If you have an instance of Status called a_status
e.g. a_status = Status.objects.get(id=1)
then the related Baugruppe is accessed as a_status.baugruppe, and the Projekt related to that Baugruppe would be a_status.baugruppe.projekt.

Following that same pattern, the nummer field in that Baugruppe would be accessed as a_status.baugruppe.nummer, and the kostentraeger in Projekt would then be a_status.baugruppe.projekt.kostentraeger

[paddy-4483]

OK, I understand. Now I’m a lot further along in my understanding of Django, I think. Many thanks!