Diesel Database

The web application I am designing is intended to host different types of assets for table-top role-playing games such as books, maps, music and so on. I want to be able to represent different types of ownership in this schema. A user can be both an owner of assets and a creator of assets, but I do not want the information related to creators in the user table because most users will probably not also be creators. A simple boolean will not suffice because there would be no way to store creator-specific information. I can use the user.id in a creator table to represent an optional one-to-one relationship. A user is either a creator, or they are not, and if they are they can and should only be a creator once. Therefore the user.id foreign key can act as the primary key in the creator table. Each asset must have one and only one creator who is the sole source of ownership for the asset. This creator.id can then be stored in the asset table as a foreign key in a one-to-many relationship to represent creator ownership. User ownership, an individual who has purchased an asset, is represented in a separate linking table that can represent a single user owning many assets. This schema will allow for a single user to have multiple forms of ownership, a many-to-many relationship for purchased assets, and a one-to-many relationship for created assets, while keeping the data normalized. The below markup will create these tables in a PlantUML diagram:

' hide the spot
' hide circle

' avoid problems with angled crows feet
skinparam linetype ortho

entity user {
    * id <<PK>>
    --
    * username
    * email
}

entity creator {
    * id <<PK, FK>>
    --
    creator_name
}

entity user_asset {
    * user_id <<FK, PK>>
    * asset_id <<FK, PK>>
}

entity asset {
    * id <<PK>>
    * creator_id <<FK>>
    --
    * title
    * summary
    * image
}

This creates the four tables, but does nothing to link them up. The few lines at the top are used to correctly display an ERD diagram as it differs slightly from the default UML class implementation. The * creates a NOT NULL field and the -- is designed to indicate which of the fields are identifiers.

PlantUML uses a simple syntax in order to represent linkages. It is important to note that it is simply a drawing tool and will not prevent you from creating invalid or incorrect linkages between tables.

user     ||--o|  creator       : is
creator  ||--o{  asset         : creates
asset    ||--o{  user_asset    : is purchased in
user     ||-down--o{  user_asset    : purchases

Adding these lines below the table definitions will create linkages between the tables using a visually representative syntax that is very easy to remember. For example |o--|{ represents an optional one to mandatory many relationship and ||--|| would represent a mandatory one-to-one relationship. Detailed examples for all the possible combinations can be found in the ERD class documentation.

I didn’t exactly make a UML diagram because Rust uses structs and traits instead of classes and interfaces, but I created a diagram that was inspired by UML. To do this I had to add some custom symbols to correctly display Rust data structures like traits and enums. I started by adding classes for each table and then created structs that would work well with Diesel. Diesel is an ORM that uses Rust structs along with macros to dynamically create SQL queries that are safer from SQL injection or other attacks. It also speeds up development by creating a schema of your database in Rust so that you will get compile time and language server errors if you try to do something incompatible with your schema.

struct User {
        + id: i32
        + username: String
        + email: String
        + create(user_id: i32) Self
        + read(user_id: i32) Self
        + update(user: UserNew) usize
        + destroy(user_id: i32) usize
}

struct UserNew {
        + username: String
        + email: String
        + logo: String
        + create(user_id: i32) Self
}

struct Creator {
        + id: i32
        + first_name:  String
        + last_name: String
        + read(creator_id: i32) Self
        + update(user: UserNew) usize
        + destroy(user_id: i32) usize
}

struct CreatorNew {
        + first_name:  String
        + last_name: String
        + create(user_id: i32) Self
}

class AssetTrait << (T,#FF7700) >>  {
        read(asset_id: i32) Self
        update(self, conn) usize
        destroy(asset_id: i32) i32
        paginate(user_id) Page
}

struct Page {
        + thumb: String
        + display_name:String
        + main_image: String
}

struct Asset {
        + id: i32
        + creator_id: i32
        + title: String
        + summary: String
}

UserNew o-left- User
CreatorNew o-right- Creator
User *-- Creator
Creator o-- Asset
Asset <|.. AssetTrait
AssetTrait <|.. Page

Similar to the ERD diagram, you create classes, but instead of using * you would use + to show public methods and variables. In order to display the Asset trait I used the << (T,#FF7700) >> syntax, which allows you to control the icon and the icon color for any arbitrary data structure.