Database documentation

Smart Cooking Database Documentation

This documentation provides an overview of the MariaDB database schema for the recipe management system. The schema consists of various tables that handle different aspects of the system, including users, recipes, comments, tags, and more. Below is a detailed description of each table and its columns, along with justifications to help users understand the purpose of each column.

Tables

1. image

Stores information about images used in the system.

Column	Data Type	Description	Justification
image_id	INT AUTO_INCREMENT	Primary key.	Uniquely identifies each image.
image_path	VARCHAR(255)	Unique path to the image file.	Stores the file path to locate the image on the server.

2. locale

Stores information about different locales (languages and regions).

Column	Data Type	Description	Justification
locale_id	INT AUTO_INCREMENT	Primary key.	Uniquely identifies each locale.
locale_code	VARCHAR(10)	Unique code for the locale.	Standard code to identify the locale (e.g., 'en-US').
locale_name	VARCHAR(50)	Unique name for the locale.	Descriptive name of the locale (e.g., 'English (United States)').

3. person

Stores information about users of the system.

Column	Data Type	Description	Justification
person_id	INT AUTO_INCREMENT	Primary key.	Uniquely identifies each user.
name	VARCHAR(100)	Name of the user.	Stores the user's name for identification and personalization.
email	VARCHAR(100)	Unique email of the user.	Used for logging in and contacting the user.
password	VARBINARY(60)	Hashed password of the user.	Secures user authentication.
salt	VARBINARY(16)	Salt used for hashing the password.	Enhances password security.
registration_date	TIMESTAMP	Timestamp of the user registration. Defaults to current timestamp.	Records when the user registered, for audit and tracking purposes.
last_login	TIMESTAMP	Timestamp of the last login.	Tracks the user's last login time.
avatar_image_id	INT	Foreign key to image(image_id). Avatar image of the user.	Links to the user's profile picture.
locale_id	INT	Foreign key to locale(locale_id). Preferred locale of the user.	Customizes the user experience based on language and regional settings.

4. responsibility

Stores different roles or responsibilities that can be assigned to users.

Column	Data Type	Description	Justification
responsibility_id	INT AUTO_INCREMENT	Primary key.	Uniquely identifies each responsibility.
responsibility_name	VARCHAR(100)	Unique name of the responsibility.	Defines specific roles or duties within the system (e.g., 'Admin', 'Editor').

5. person_responsibility

Links users to their assigned responsibilities.

Column	Data Type	Description	Justification
person_id	INT	Foreign key to person(person_id).	Associates a user with a responsibility.
responsibility_id	INT	Foreign key to responsibility(responsibility_id).	Links a responsibility to a user.
Primary Key: (person_id, responsibility_id)			Ensures each user-responsibility pair is unique.

6. status

Stores the status of recipes, such as whether they are published, draft, etc.

Column	Data Type	Description	Justification
status_id	INT AUTO_INCREMENT	Primary key.	Uniquely identifies each status.
status_name	VARCHAR(50)	Unique name of the status.	Describes the status of a recipe (e.g., 'Published', 'Draft').

7. recipe

Stores information about recipes.

Column	Data Type	Description	Justification
recipe_id	INT AUTO_INCREMENT	Primary key.	Uniquely identifies each recipe.
author_id	INT	Foreign key to person(person_id). Author of the recipe.	Links the recipe to its creator.
publication_date	TIMESTAMP	Timestamp of publication. Defaults to current timestamp.	Records when the recipe was published.
modification_date	TIMESTAMP	Timestamp of the last modification.	Tracks changes to the recipe over time.
image_id	INT	Foreign key to image(image_id). Image associated with the recipe.	Links to an image that represents the recipe.
cook_time	INT UNSIGNED	Cook time in minutes.	Indicates how long it takes to cook the recipe.
difficulty_level	TINYINT	Difficulty level (1-3).	Rates the recipe's difficulty on a scale from 1 (easy) to 3 (hard).
number_of_reviews	INT	Number of reviews for the recipe.	Tracks the number of reviews to show its popularity.
nutritional_information	TEXT	Nutritional information of the recipe.	Provides health-related information about the recipe.
source	VARCHAR(255)	Source of the recipe.	Credits the original source or inspiration for the recipe.
video_url	VARCHAR(255)	URL to a video of the recipe.	Links to a video demonstrating the recipe.
status_id	INT	Foreign key to status(status_id). Status of the recipe.	Indicates the recipe's status (e.g., published, draft).

8. recipe_translation

Stores translations of recipes in different languages.

Column	Data Type	Description	Justification
recipe_id	INT	Foreign key to recipe(recipe_id).	Associates a translation with a specific recipe.
locale_id	INT	Foreign key to locale(locale_id).	Specifies the language of the translation.
title	VARCHAR(255)	Title of the recipe in the specified locale.	Provides the title in the target language.
description	TEXT	Description of the recipe in the specified locale.	Provides the description in the target language.
ingredients	TEXT	Ingredients of the recipe in the specified locale.	Lists the ingredients in the target language.
preparation	TEXT	Preparation steps of the recipe in the specified locale.	Details the preparation steps in the target language.
Primary Key: (recipe_id, locale_id)			Ensures each recipe has unique translations per locale.

9. tag

Stores tags that can be associated with recipes.

Column	Data Type	Description	Justification
tag_id	INT AUTO_INCREMENT	Primary key.	Uniquely identifies each tag.
tag_name	VARCHAR(255)	Unique name of the tag.	Describes the tag, which can be used to categorize recipes (e.g., 'Vegan', 'Quick Meals').

10. comment

Stores comments on recipes made by users.

Column	Data Type	Description	Justification
comment_id	INT AUTO_INCREMENT	Primary key.	Uniquely identifies each comment.
person_id	INT	Foreign key to person(person_id).	Links the comment to the user who made it.
recipe_id	INT	Foreign key to recipe(recipe_id).	Associates the comment with a specific recipe.
comment	TEXT	Content of the comment.	Stores the text of the user's comment.
comment_date	TIMESTAMP	Timestamp of the comment. Defaults to current timestamp.	Records when the comment was made.

11. comment_like

Stores likes on comments, allowing users to appreciate others' comments.

Column	Data Type	Description	Justification
like_id	INT AUTO_INCREMENT	Primary key.	Uniquely identifies each like.
person_id	INT	Foreign key to person(person_id).	Links the like to the user who made it.
comment_id	INT	Foreign key to comment(comment_id).	Associates the like with a specific comment.

12. favorite

Stores users' favorite recipes, allowing them to easily find recipes they like.

Column	Data Type	Description	Justification
favorite_id	INT AUTO_INCREMENT	Primary key.	Uniquely identifies each favorite record.
person_id	INT	Foreign key to person(person_id).	Links the favorite to the user who favorited the recipe.
recipe_id	INT	Foreign key to recipe(recipe_id).	Associates the favorite with a specific recipe.
favorited_date	TIMESTAMP	Timestamp when the recipe was favorited. Defaults to current timestamp.	Records when the recipe was marked as favorite.
Unique Constraint: (person_id, recipe_id)			Ensures each user can favorite a recipe only once.

13. recipe_tag

Links recipes to tags, allowing categorization of recipes.

Column	Data Type	Description	Justification
recipe_id	INT	Foreign key to recipe(recipe_id).	Associates a tag with a specific recipe.
tag_id	INT	Foreign key to tag(tag_id).	Links a tag to a recipe for categorization.
Primary Key: (recipe_id, tag_id)			Ensures each recipe-tag pair is unique.

14. recipe_rating

Stores ratings given by users to recipes, allowing users to review recipes.

Column	Data Type	Description	Justification
rating_id	INT AUTO_INCREMENT	Primary key.	Uniquely identifies each rating.
person_id	INT	Foreign key to person(person_id).	Links the rating to the user who rated the recipe.
recipe_id	INT	Foreign key to recipe(recipe_id).	Associates the rating with a specific recipe.
rating	TINYINT	Rating value (1-4).	Stores the user's rating, ensuring it is within a valid range.
Unique Constraint: (person_id, recipe_id)			Ensures each user can rate a recipe only once.

Foreign Key Constraints

Foreign key constraints ensure referential integrity between the tables. When a referenced record is deleted, the foreign key constraints define the behavior:

• ON DELETE CASCADE: Deletes the related records in the child table. For example, if a user is deleted, their comments, ratings, and favorites are also deleted.
• ON DELETE SET NULL: Sets the foreign key column to NULL. For example, if an image is deleted, the avatar_image_id in the person table is set to NULL.

Notes

• All tables use the InnoDB storage engine, which supports transactions, foreign key constraints, and row-level locking.
• The CHECK constraints on difficulty_level and rating ensure that these columns only contain valid values within the specified ranges.

This schema design provides a comprehensive structure to manage users, recipes, comments, tags, and other related entities in a recipe management system, with justifications to help users understand the purpose of each column.