Tag table designs

[onkoe]

We need to do another pass over our data structures to create the 'tag' and 'tag_section' tables.

Problem

Tag, as-is, holds all of its implied tags in-line. That's a LOT of duplication in the info table, and all of it's in JSON to boot...

Solution

We need to adjust our types.

pub struct Tag {
    pub id: Uuid,
    pub name: String,
    pub section: Uuid,
    pub implies: Json<Vec<Uuid>>
}

pub struct Media {
    // ...
    pub tags: Json<Vec<Uuid>>
}

pub struct TagSection {
    pub id: Uuid,
    pub name: String
}

Then, we can create some tables:

-- tag: holds the info on each tag
CREATE TABLE IF NOT EXISTS tag(
    id TEXT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL,
    section TEXT NOT NULL,
    implies TEXT NOT NULL, -- (list of uuids in json)

    -- and i think a relationship would be nice :D
    FOREIGN KEY(section) REFERENCES tag_section(id)
);

-- tag_section: categories of tags that really shouldn't be combined
CREATE TABLE IF NOT EXISTS tag_section(
    id TEXT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL
);

[onkoe]

I think I'll undo this UUID style and return to the name-based style. I can use name as the PRIMARY KEY and track TagSections within the database, but Tags are more ephemeral - they're stored in the media file directly.