Trouble deciding schema of an SQL database in Django

I am building a database which has in it a bunch of molecules and a bunch of different attributes ( e.g. ionization energy levels ). Each attribute may have one or many values and I'm not sure what the best way to represent this is. I am very new to relational databases so I'm having trouble figuring out the best ACID compliant way of storing it. I am also very new to Django, though I have worked through the tutorial and some of the documentation.

I should mention as well that currently I'm using sqlite as my RDBMS, as the website will be read only.

My current conception is to have two models. One for describing the molecule, right now this is just its name, but it may end up including other descriptors as well. The second model would be for the attributes, its effectively just attribute name, molecule name, and a single value that the attribute holds. They have a one to many relationship from the molecule name to the attribute values.

But this means that the attribute name and molecule name will be repeated a lot. Is this ok? Is there a better method?

Eventually I'd like users to be able to filter to their specifications and download it as a csv. For instance if someone wanted all molecules with ionization levels over 10, they could just type that into the search and download the data. Because of this, I had decided not to store the values of an attribute as a csv style string.

Also, I can't just have a new table for every molecule b/c this would mean generating a bunch of classes in a loop and I feel like it'll lead to confusion as molecules and attributes get added down the line.

First rule, make it simple

from django.db import models

class Molecule(models.Model):
    name = models.CharField(max_length=200, unique=True)
    # Add more 

class Attribute(models.Model):
    name = models.CharField(max_length=200, unique=True)
    unit = models.CharField(max_length=50, blank=True, null=True) 

class AttributeValue(models.Model):
    molecule = models.ForeignKey(Molecule, on_delete=models.CASCADE, related_name="attributes")
    attribute = models.ForeignKey(Attribute, on_delete=models.CASCADE, related_name="values")
    value = models.FloatField()  # or CharField if sometimes not numeric
Вернуться на верх