Changes between Version 2 and Version 3 of RelationalModel

Timestamp:

04/20/26 08:34:14 (12 days ago)

Author:

231550

Comment:

--

RelationalModel

@@ -1 +1 @@
 [[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.
+
+
+