Enhancements and Clarifications for Database Schema Design

[FPeng2288]

Issue Description:

Based on our ongoing development and understanding of the project's requirements, I have a few suggestions and clarifications needed regarding the database schema design. Below are the points organized by the relevant tables and fields, along with the rationale for each suggestion.

Consultations

1. Email Uniqueness

   • Rationale: To ensure the integrity and uniqueness of user identification through email.
   • Question: Should the email field in the users table be unique?

2. RequestStatus, RequestPriority, RequestType, RequestCategory Tables

   • Rationale: To simplify the schema and potentially reduce redundancy.
   • Question: Should the description field be removed from these tables?

3. submission_time Foreign Key Relationship

   • Rationale: To maintain data integrity and enforce relational constraints.
   • Question: Should submission_time have a foreign key relationship with RequestStatus?

4. leadVolunteerUserId

   • Rationale: To understand the relationships and data integrity constraints.
   • Question: What is the leadVolunteerUserId field? Should there be a volunteer table to relate this field to?

5. lastUpdateDate

   • Rationale: To ensure consistency and data accuracy in tracking updates.
   • Questions:
     • Should lastUpdateDate be linked to the most recent update time in RequestStatus, RequestPriority, RequestType, and RequestCategory?
     • Should submission_date, serviced_date, and last_update_date be non-nullable?
     • Should the submission_date, serviced_date and last_update_date be changed from submission_date to submission_time .etc?

6. User Name Fields

   • Rationale: To maintain logical consistency in the user data.
   • Questions:
     • Why is full_name non-nullable while last_name and first_name are nullable? Should full_name be removed and generated from first_name and last_name?

7. user_category

   • Rationale: To clarify the intended use and structure of the user category data.
   • Questions:
     • What are the specific enum values for user_category?
     • Should the description field be removed, or is it intended as a memo for admin use?

8. user_status

   • Rationale: To understand the role and necessity of user status.
   • Questions:
     • What is the purpose of user_status? How does it control or signify user actions in relation to the requests?

9. Inconsistent Naming Conventions

   • Rationale: To maintain a standard naming convention for clarity and consistency.
   • Question: Should we standardize table names to either all singular or all plural?

---

Thank you for considering these suggestions. I believe addressing these points will help in refining our database schema and ensuring it aligns with our project requirements and best practices.

[FPeng2288]

Issue: Database Design and Timestamp Recording Logic

Issues Identified:

Many-to-One Relationship Constraints:

• The tables request_status, request_category, request_type, and request_priority are designed with a Many-to-One relationship with the request table.
• This relationship implies that each of these tables holds values that are essentially enums, and their IDs are limited to the number of enum values. For instance, request_priority might only have LOW, MEDIUM, HIGH, resulting in a maximum of three IDs.

Timestamp Recording Issue:

• The last_updated_date in these tables records the timestamp of the most recent selection of the enum value, not the timestamp associated with a specific request (requestId).
• This design does not meet the requirement of associating timestamps with each requestId, which is crucial for accurate tracking of request updates.

Default Value Issue:

• The NOT NULL constraints on these fields necessitate pre-setting values in these tables before creating a request.
• A proposed solution of adding a DEFAULT enum value and assigning it initially creates additional logic to ensure these default values are replaced during request creation. This approach increases code complexity and potential for errors.

MVP Principle and Practicality:

• According to the MVP (Minimum Viable Product) principle, currently migrating these fields into the request table is a feasible approach with lower complexity.
• If future requirements still insist on decoupling these fields, the relationship between these four tables and the request aggregate root needs to be redesigned, as the current Many-to-One relationship conflicts with the project requirements.

Current Approach and Justification:

• To simplify the process and ensure accurate timestamp recording, the fields from these four tables have been migrated directly into the request table.
• This approach eliminates the need for managing default enum values and pre-setting values, thereby reducing the code complexity and the number of entities and repositories required.
• It allows for direct association of timestamps with requestId, ensuring accurate tracking of request updates without additional logic to manage default values.

Discussion:

• Is this approach aligned with best practices in database design?
• Are there alternative methods to address the identified issues without significant design changes?