Database Structure

This document describes the database schema used by the Kantinator API service.

Overview

Kantinator uses MySQL as its database system with Entity Framework Core for data access. The database follows a normalized relational design with proper foreign key constraints and relationships.

Entity-Relationship Diagram

The following ER diagram shows the conceptual data model:

Database Schema

The complete database schema with all tables, columns, data types, and relationships:

Core Entities

User Management

Users

Stores user account information including credentials and profile data.

Related Entities:

• UserRole - Many-to-many relationship with roles
• RefreshToken - One-to-many relationship for authentication tokens
• Preorder - One-to-many relationship for meal preorders

Roles

Defines different user roles in the system (Admin, Manager, User).

Related Entities:

• UserRole - Many-to-many relationship with users
• RolePermission - Many-to-many relationship with permissions

Permissions

Granular permissions for fine-grained access control.

Related Entities:

• RolePermission - Many-to-many relationship with roles

Menu Management

WeeklyMenu

Represents a menu for a specific week (identified by calendar week and year).

Related Entities:

• Menu - One-to-many relationship with daily menus

Menu

Represents a menu for a specific date.

Related Entities:

• WeeklyMenu - Many-to-one relationship
• MenuDailyMenu - Many-to-many relationship with daily menus

DailyMenu

Represents a specific meal offering with its foods.

Related Entities:

• MenuDailyMenu - Many-to-many relationship with menus
• Food - Many-to-one relationship
• Status - Many-to-one relationship for availability status

Food

Represents individual food items with their properties.

Related Entities:

• FoodType - Many-to-one relationship
• DailyMenu - One-to-many relationship

FoodType

Categorizes foods (e.g., Main Course, Side Dish, Dessert).

Related Entities:

• Food - One-to-many relationship

Status

Tracks the availability status of menu items.

Related Entities:

• DailyMenu - One-to-many relationship

Orders

Preorder

Stores user meal preorders.

Related Entities:

• User - Many-to-one relationship
• DailyMenu - Many-to-one relationship

Authentication

RefreshToken

Stores refresh tokens for JWT authentication with token rotation support.

Related Entities:

• User - Many-to-one relationship

Key Relationships

Many-to-Many Relationships

1. Users ↔ Roles

   • Junction Table: UserRole
   • Allows users to have multiple roles

2. Roles ↔ Permissions

   • Junction Table: RolePermission
   • Allows roles to have multiple permissions

3. Menu ↔ DailyMenu

   • Junction Table: MenuDailyMenu
   • Allows a menu to have multiple daily menu options

One-to-Many Relationships

1. WeeklyMenu → Menu

   • A weekly menu contains multiple daily menus

2. FoodType → Food

   • Each food belongs to one food type

3. Food → DailyMenu

   • Each food can be part of multiple daily menus

4. Status → DailyMenu

   • Tracks availability of daily menu items

5. User → RefreshToken

   • Each user can have multiple refresh tokens

6. User → Preorder

   • Each user can have multiple preorders

Migrations

The database schema is managed through Entity Framework Core migrations located in:

Kantinator.ApiService/Migrations/

Migration History

• 20251127133329_Initial - Initial database schema
• 20251127140640_ChangedMenuFoods - Modified menu-food relationships
• 20251127145749_AddedCalenderWeek - Added calendar week tracking
• 20251127211529_RemovedWeekDay - Removed weekday enum
• 20251128080545_ExpandedMenuDailyMenu - Expanded menu daily menu junction
• 20251128184007_FixedDateTimeTypes - Fixed DateTime type handling
• 20251202130331_ChangedSomeDataTypes - Data type adjustments
• 20251207092948_UserAndRefreshTokenTables - Added user authentication tables
• 20251209134329_AddedUserRolesAndPermissions - Added role-based access control

Database Context

The database context is defined in: KantinatorDbContext

It includes:

• Entity configurations
• Relationship mappings
• Seed data setup
• Index definitions

Connection String

The application uses MySQL with connection strings configured in appsettings.json. The connection string format:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=kantinator;User=root;Password=***"
  }
}

Data Seeding

Initial data seeding is handled by: DatabaseSeeder

This includes:

• Default roles and permissions
• Sample food types
• Test users (in development mode)