[[Image(RelationalModel-ProjectCode.svg)]] **Descriptive documentation and argumentation**\\ 1. User & Role Management (User, Roles, User_roles)\\ The User table is the central entity of the entire model. Rather than embedding a role directly into the User table as a simple string or enum (e.g. role VARCHAR(20)), the decision was made to create a separate Roles table and a User_roles junction table. **Why this way:** A simple enum column would restrict each user to exactly one role and make it difficult to add new roles in the future without altering the table structure. By using a junction table, the model supports many-to-many role assignment — a user could theoretically be both a nutritionist and an admin simultaneously. It also means new roles can be added simply by inserting a new row into Roles, with no schema changes required. This is a much more scalable and flexible design for a platform that may evolve over time. 2. Food Categorization (Category — self-referencing)\\ The Category table contains a self-referencing foreign key ParentCategory_id that points back to its own primary key. **Why this way:** Food categories in the real world are hierarchical. For example "Chicken Breast" belongs to "Poultry", which belongs to "Meat & Fish", which belongs to "Protein Sources". A flat single-level category system would not capture this hierarchy. By making Category self-referencing, the model supports unlimited nesting depth of categories without needing additional tables. The ParentCategory_id is nullable, meaning top-level categories simply have no parent. ON DELETE SET NULL ensures that if a parent category is deleted, its children are not deleted — they simply become top-level categories themselves 3. Nutritional Tracking (Food, Nutrient, Food_nutrients)\\ Rather than adding individual columns to the Food table for each nutrient (e.g. protein INT4, carbs INT4, fat INT4), the model uses a separate Nutrient table and a Food_nutrients junction table. **Why this way:** The number of nutrients tracked can vary enormously — from basic macros (protein, carbs, fat) to micronutrients (Vitamin C, Iron, Calcium, Omega-3 etc.). Hardcoding nutrient columns into Food would make the schema inflexible and would require an ALTER TABLE every time a new nutrient needs to be tracked. The junction table approach allows any number of nutrients to be associated with any food, and new nutrients can be added simply by inserting into the Nutrient table. 4. Serving Sizes (!ServingSize)\\ !ServingSize is modeled as a separate table linked to Food with a many-to-one relationship, rather than a single serving size column on Food. **Why this way:** A single food can have multiple valid serving sizes. For example "Oats" could be measured as "1 cup (90g)", "1 tablespoon (10g)", or "100g". If serving size were a single column on Food, users would be forced to always log in the same unit, which is unrealistic. The separate table allows the platform to offer users a dropdown of recognized serving sizes when logging food, improving accuracy and user experience. The amount and unit columns together define exactly how much that serving represents. 5. Exercise & Muscle Targeting (Exercise, Muscle, Exercise_muscles, Activity)\\ Exercises are organized under Activities and linked to Muscles through a junction table. **Why this way:** The Activity table (e.g. "Cardio", "Strength Training", "Flexibility") acts as a high-level grouping for exercises, allowing users to filter and browse exercises by type. This is kept separate from Exercise because one activity type contains many exercises. The Exercise_muscles junction table exists because a single exercise targets multiple muscles (e.g. a bench press works the chest, triceps, and shoulders simultaneously), and a single muscle is targeted by many exercises. This is a true many-to-many relationship that cannot be modeled with a simple FK column. By tracking this explicitly, the platform can show users a muscle map of which areas they are training, and generate reports like "you haven't trained your legs this week." 6. Diet Planning (Diet, Diet_foods, Diet_Exercise_Targets)\\ The Diet table acts as a plan template rather than a per-user record. Users are linked to diets through the User_Diets junction table. **Why this way:** If diet data were embedded directly into a user record, the same diet plan could not be shared across multiple users. By separating the Diet as its own entity, the same diet plan (e.g. "Keto Diet", "High Protein Plan") can be assigned to many users simultaneously. The company's nutritionists can create a library of diet plans that any user can be enrolled in. As well as giving users the option to create their own custom diets where they input their own foods and exercises in accordance to their needs. Diet_foods specifies exactly which foods are part of the diet and in what amounts, while Diet_Exercise_Targets specifies which exercises should be performed and how many times per week. Together these two tables fully define what a user on a given diet should eat and how they should exercise, giving the platform enough data to track compliance and progress. 7. Gamification (Streak, Streak_type, Achievements, User_achievements)\\ The gamification layer is modeled with full normalization rather than simple counters. **Why this way:** Streak_type is separated from Streak for the same reason Nutrient is separated from Food — there can be many types of streaks (logging streaks, calorie streaks, workout streaks) and new types should be addable without schema changes. Each user's Streak record tracks both current_count and longest_count, the latter being historically significant even when a streak is broken. The CHECK constraint longest_count >= current_count enforces this business rule at the database level. Achievements are similarly separated from User_achievements. The Achievements table defines the achievement criteria (what needs to be done, how many times, in what period), while User_achievements simply records when a specific user met those criteria. This means achievements are defined once and can be earned by any number of users, rather than duplicating achievement definitions per user. 8. Goals (Goal, User_goals)\\ Goals are modeled as a shared reference table linked to users through User_goals, with each goal tied to a specific diet plan. **Why this way:** A goal like "Lose Weight" or "Gain Weight" is a general concept that many users share. Rather than each user defining their own goal from scratch, goals are predefined and linked to appropriate diet plans. This means when a user selects a goal, the system can automatically suggest or assign the corresponding diet plan. The User_goals junction table adds start_date and end_date to give each user's goal a defined timeframe, making progress trackable and time-bound.