Generalized database schema

[dongbohu]

Here is a generalized DB schema that takes advantage of registries in identifiers.org. I added some comments to explain the purpose.

from django.db import models

# Registry in identifiers.org
class Registry(models.Model):
    name = models.CharField()
    prefix = models.CharField()
    description = models.CharField()
    # and other attributes of a registry ...


# Entity includes common attributes of any entity (such as gene,
# publication, disease, tissue, etc)
class Entity(models.Model):
    accession = models.CharField(null=True)  # accession in identifiers.org
    registry = models.ForeignKey(Registry, null=True)
    # and other attributes shared by all entities ...


# "Gene" is one kind of entity
class Gene(models.Model):
    entity = models.OneToOneField(
        Entity,
        on_delete=models.CASCADE,
        primary_key=True,
    )
    # specific attributes for a gene
    scientific_name = models.CharField(max_length=32)
    systematic_name = models.CharField(max_length=32)
    organism = models.ForeignKey(Organism, ...)


# "Publication" is another kind of entity
class  Publication(models.Model):
    entity = models.OneToOneField(
        Entity,
        on_delete=models.CASCADE,
        primary_key=True,
    )
    # specific attributes for a publication
    pmid = models.IntegerField(null=True, unique=True, db_index=True)
    title = models.TextField()
    authors = models.TextField()
    date = models.DateField()
    journal = models.TextField()
    volume = models.TextField(blank=True, null=True)
    pages = models.TextField(blank=True, null=True)
    issue = models.TextField(blank=True, null=True)


# "Disease" is another kind of entity
class Disease(models.Model):
    entity = models.OneToOneField(
        Entity,
        on_delete=models.CASCADE,
        primary_key=True,
    )
    # and specific attributes for a disease ...


# "Entityset" includes common attributes for any kind of entity set.
# It may include different types of entities.
class Entityset(models.Model):
    creator = models.ForeignKey(User)
    title = models.TextField()
    abstract = models.TextField(null=True)
    slug = models.SlugField(help_text="Slugified title field", max_length=75)
    public = models.BooleanField(default=False)
    deleted = models.BooleanField(default=False)
    fork_of = models.ForeignKey('self', editable=False, null=True)
    tip_item_count = models.IntegerField(null=True)


# "Geneset" is one kind of "Entityset"
class Geneset(models.Model):
    entityset = models.OneToOneField(
        Entityset,
        on_delete=models.CASCADE,
        primary_key=True,
    )
    organism = models.ForeignKey(Organism)
    # and other attributes for a geneset


# Similar models can be defined for "Publicationset" or "Diseaseset" ...


# Version of an Entityset
class Version(models.Model):
    entityset = models.ForeignKey(entityset)
    creator = models.ForeignKey(User)
    ver_hash = models.CharField(db_index=True, max_length=40)
    description = models.TextField(null=True)
    commit_date = models.DateTimeField(auto_now_add=True)
    parent = models.ForeignKey('self', null=True)


# Annotations of entities
class Annotation(models.Model):
    version = models.ForeignKey(Version)
    primary_entity = models.ForeignKey(Entity)   # entity that is being annotated
    annotator_entity = models.ForeignKey(Entity) # entity that is the annotation

[cgreene]

After doing some more thinking on this, I am having trouble figuring out if this makes sense. We still have to explicitly support each type (i.e., by having a Gene model, a Geneset model). Is this costing us more in added complexity without an added benefit?

[dongbohu]

Yes, Gene and Geneset models should be still explicitly defined, because Entity or Entityset can't include gene-specific or geneset-specific attributes. The largest benefit is that if we want to add other types of entities later (for example, Disease), we don't have to change Version or Annotation model, which is related to Entityset or Entity directly.