Idealized DB Schema

This is a workspace to design a perfect Roundware Server database data schema. It might not ever be fully implemented, but we will see what happens.

This uses Django abstract and multi-table models: https://docs.djangoproject.com/en/dev/topics/db/models/#multi-table-inheritance

FileData(Model):
    fid = AutoField(primary_key=True)
    pid = ForeignKey(Project)
    uid = ForeignKey(User)
    mimetype
    filesize
    created
    changed
    lang # The file/image language, defaults to NULL for undefined language.
    class Meta:
        abstract = True

File(FileData):
    file = FileField

Image(FileData):
    file = ImageField

Category(Model):
    cid = AutoField(primary_key=True)
    pid = ForeignKey(Project)
    title # Translatable
    label_create # "Who are you?"
    label_view # "Select gender(s)"
    default = ForeignKey(Tag)
    select_type
    enabled
    weight

Tag(Model):
    tid = AutoField(primary_key=True)
    cid = ForeignKey(Category)
    title # Translatable
    description # Translatable
    enabled
    weight
    related_tags = ManyToMany(Tag) # Used to create user flows aka choose your own adventure.

Project(Model):
    # [...] todo

Asset(Model):
    aid = AutoField(primary_key=True)
    uid = ForeignKey(User)
    pid = ForeignKey(Project)
    asset_type
    created
    changed
    title # Translatable and optional
    description # For admin use only.
    published # Submitted? Enabled?
    latitude
    longitude
    tags = ManyToMany(Tag)
    physical_objs = ManyToMany(PhysicalObj)

AssetAudio(Asset):
    duration
    # An asset will generally have one file, but optionally allow many to support
    # multiple language and formats. At least one File is required.
    files = ManyToMany(File)

# Different from AudioAsset because it plays in the audio streams.
AssetRecording(AssetAudio):
    pass

AssetSpeaker(Asset):
    maxdistance
    mindistance
    maxvolume
    minvolume
    stream_uri # A stream URI or file is required.
    stream_uri_backup
    files = ManyToMany(File)
    duration
    # Maybe we have a table of Speaker start times, so the loops can be synced across devices?

AssetImage(Asset):
    files = ManyToMany(Image)
    # Note: height/width is stored in the image file field.

AssetVideo(Asset):
    files = ManyToMany(File)
    height
    width
    fps
    duration
    format

AssetText(Asset):
    text # Translatable (How?)
    html # Translatable

PhysicalObj(Model):
    lid = AutoField(primary_key=True)
    pid = ForeignKey(Project)
    tags = ManyToMany(Tag)
    latitude
    longitude
    title # Translatable
    obj_type # painting, sculpture, tree, fire exit, etc.

###Mini-Summary: Assets are all forms of media (images, photos, video, and text) allowing multiple files to support multiple languages. Tags are simple text words/phrases grouped in TagCategories to describe Assets and PhysicalObjs. PhysicalObjs represent real world GPS (or otherwise) located objects. Assets point to PhysicalObjs.

###Playing GPS located audio and loading associated assets.

• Get all AssetRecording located within the Project's "Nearby AssetRecording Distance" tagged with the allowed Tags.
• Choose an AssetRecording to play.
• Get the Tag(s) related Assets and/or PhysicalObjs such as Artist photo or Artist video
• Get the PhysicalObj(s) related to the AssetRecording; "Which object are you talking about?"
• Get the AssetImage(s) related to the PhysicalObj(s) such as the Primary photo.

Not the easiest method, but it seems extremely extensible.

Incomplete!!!