Redo database structure

[pavelskipenes]

Proposal

Current database structure is not relational and issues arises higher in the stack due to bad database design. It makes no sense to solve issues in the stack before core problems are addressed. This is a proposal of a new database design from ground up. When it is implemented we can use frameworks like Laravel to create database abstractions, api and so on instead of writing them from scratch as are being done now.

Does this design look good enough for our use case? Would be better to change it now than later.

Notable changes from current design:

• structure are now split into admin, store and members from just members and web.
• split members table into members and membership request.
• use relational mapping between keys
• image references are stored in the database. When ref count hist zero the images can be deleted automatically and thus reducing management.
• add product variants. Allows purchase of product with some variations like size and or color
• add password salt and 2fa secret
• admin.users have more personal information. The idea is that users can log in and change their description through a profile menu or something similar.

Todo:

• bearer token

Attached design does not fully support ERD patterns like many-to-many and zero-or-many etc... So some relations might not be correct in the diagram.
new_database_structure.pdf

This is database markup language that can be used to generate diagrams like above and sql files for import. You can paste this into dbdiagram.io and interactively view it. Note: it does not work well in Brave browser just in Chrome 😞

Project ntnui_swimming_database{
    database_type: 'mySQL'
    
    Note: 'storage for ecomerce, members and login credentials for managers' 
}

Table admin.users{
    id                      integer         [pk, unique, increment, not null]
    name                    text            [not null]
    title                   text            [not null]
    description             text            [not null]
    email                   text            [not null]
    username                text            [not null]
    password_hash           text            [unique, not null]
    password_salt           text            [not null]
    twofa_secret            text    
    img_id                  integer         [not null, ref: > images.id]
    
    Note: 'login credentials for managers'
}

Table admin.resources{
    id                      integer         [pk, unique, increment, not null]
    name                    text            [not null]
    Note: 'list of unique resources'
}

enum admin.access{
    read
    modify
}

Table admin.access{
    id                      integer         [pk, unique, increment, not null]
    user_id                 integer         [not null, ref: > admin.users.id]
    resource_id             integer         [not null, ref: > admin.resources.id]
    access                  admin.access    [not null]

    Note: 'rules of what resources each user can access'
}

enum members.gender{
    male
    female
}

Table members.requests{
    id                      integer         [pk, unique, increment, not null]
    first_name              text            [not null]
    surname                 text            [not null]
    birth_date              date            [not null]
    gender                  members.gender  [not null]
    phone                   varchar(15)     [not null, unique]
    email                   text            [not null]
    zip                     integer(4)      [not null]
    licensee                text            [null]
    registration_date       date            [not null]
    approved_date           date            [null]
    
    Note: 'membership requests'
}

Table members.members{
    id                      integer         [pk, unique, increment, not null]
    request_id              integer         [not null, unique, ref: - members.requests.id] 
    cin_id                  integer         [null, unique, ref: - members.cin.id]
    cin_forwarded           date            [null]

    Note: 'approved membership requests. Gets deleted once a year'
}

Table members.cin{
    id                      integer         [pk, unique, increment, not null]
    hash                    varchar(64)     [unique]
    cin                     integer(20)     [not null, unique]
    expiration_date         date            [not null]

    Note: 'customer identification number. each row expires after 5 years of inactivity'
}

Table store.product_groups{
    id                      integer         [pk, unique, increment, not null]
    name                    text            [not null]

    Note: 'product groups like clothes or food'
}

Table store.products{
    id                      integer         [pk, unique, increment, not null]
    product_group_id        integer         [not null, ref: > store.product_groups.id]
    image_id                integer         [not null, ref: - images.id]
    name                    text            [not null]
    description             text    
    availability_start      datetime        [null]
    availability_end        datetime        [null]
    req_phone               tinyint         [not null]
    req_email               tinyint         [not null]
    req_comment             tinyint         [not null]
    req_membership          tinyint         [not null]
    hidden                  tinyint         [not null]
    disabled                tinyint         [not null]
    
    Note: 'list of products'
}

Table store.product_variants{
    id                      integer         [pk, unique, increment, not null]
    product_id              integer         [not null, ref: < store.products.id]
    price                   integer         [not null]
    price_member            integer         [null, note: 'no membership discount if null']
    description             text            [not null]
    amount_available        integer         [not null, note: 'unlimited if null']
    max_orders_per_customer integer         [null, note: 'unlimited if null']

    Note: 'product variants like size or colour'
}

Table store.orders{
    id                      integer         [pk, unique, increment, not null]
    product_variant_id      integer         [not null, ref: < store.product_variants.id]
    stripe_id               text            [not null, unique, note: "order details stored on stripe servers"] 
    comment                 text            [default: '']

    Note: 'orders'
}

Table images{
    id                      integer         [pk, unique, increment, not null]
    path                    text            [not null]
    ref_count               integer         
    license                 text            [not null]

    Note: 'images of products'
}

[pavelskipenes]

@Everaert-K