[TIL] Refining the ERD around the user flow

What I did today

• Write the final plan

  • Reflection of feedback from management

  • Reflection of our team’s thoughts on feedback

• Summary of ERD modifications

  • Reflection of feedback

  • Comparison with wireframe

• Reflection of ERD modifications

Edit ERD

• Service direction and ERD structure were reorganized based on the first planning feedback of the TodayPoor project.

• 1st revision

  

  Change Items	Existing structure	Change structure	Reason for change
  Change group terminology	GROUP, GROUP_MEMBER	CREW, CREW_MEMBER	Since the term “crew” is used in the front wireframe, the FE/BE/plan terminology was changed to unify it.
  Change group FK name	group_id	crew_id	Since the table name was changed to CREW, the FK name was also modified to be consistent.
  Add crew description	doesn’t exist	Add CREW.description	A description text is needed on the crew card in the wireframe, so add it to store crew introduction/description information.
  Remove POOR_TITLE	POOR_TITLE table exists	delete table	A separate title table is no longer needed because the title is not managed as a fixed value but is generated by AI each time.
  Add AI result fields	AI_RESULT.output_text centered	Add title, roast_message, mode	AI generates award titles and vitriol/feedback phrases together, so they are added to separate and save the results
  Change AI model name field name	model_name	model	Changed field names to make them more concise
  Token Usage Separation	token_usage	input_token, output_token	Changed to more accurately record AI usage by separating input and output tokens
  Add prompt version	doesn’t exist	Add prompt_version	Added to keep track of which version of the prompt resulted in the result, even if the prompt is managed in code.
  Add AI call status	doesn’t exist	Add status	Added to store AI call success/failure
  Add AI error code	doesn’t exist	Add error_code	Added to track and debug the cause when AI call fails
  Change OCR relationship	OCR_RESULT.expense_id	EXPENSE.ocr_result_id	The OCR results are generated first, and the final consumption data modified/confirmed by the user is stored in EXPENSE, so EXPENSE is changed to refer to the OCR results.
  Add OCR extracted value	Save only OCR_RESULT.raw_text	Add extracted_merchant, extracted_amount	Added to allow comparison between the merchant/amount initially recognized by OCR and the final value modified by the user
  Remove image URL	EXPENSE.image_url	delete	After OCR recognition, the original image is no longer needed, so it is removed to avoid saving it to the DB.
  Consumer-crew relationship becomes essential	crew_id is nullable	EXPENSE.crew_id NOT NULL	It is set as a required value because the policy is that users must register consumption records while joining the crew.
  Mandatory ranking-AI result relationship	ai_result_id is nullable	RANKING_RESULT.ai_result_id NOT NULL	Crew members’ MVP rankings are set to NOT NULL because they necessarily include AI results.
  Clarification of ranking cycle	Only ranking_date exists	Use ranking_date on a daily basis	Since the service provides MVP/ranking on a daily basis, ranking date is managed on a daily basis.

  • In this ERD revision, the front wireframe terminology and backend domain name are aligned, and POOR_TITLE is removed to match the structure in which AI generates titles and feedback.

  • In addition, the relationship between OCR_RESULT and EXPENSE was reorganized to separate the OCR initial recognition result and the final consumption data modified by the user.

  • Through this, we improved the data structure to be more suitable for MVP service flow.

• 2nd revision

  

  Change Items	Existing structure	Change structure	Reason for change
  Add ranking type	There is no ranking type distinction in RANKING_RESULT	Add ranking_type	Added to distinguish between date-based ranking results and random topic-based ranking results
  Ranking type enum definition	doesn’t exist	DAILY, RANDOM_TOPIC	Front wireframe shows both daily and random topic results to clearly distinguish between result types
  Add consent to member information	No consent to USER	Add is_agreeed	Added to store personal information and consent regarding service use when registering as a member
  Add AI mode for each crew	AI mode exists only in result table	Add CREW.ai_mode	Added to allow setting AI feedback mood/intensity for each crew
  Stay in AI Results Mode	AI_RESULT.mode exists	stay the same	Maintain a record of what mode the AI ​​results were created in
  Specify enum value	Displays only the type like enum provider and enum visibility	Specify enum candidate values ​​as strings in Mermaid	Added to help you understand which enum values ​​are used just by looking at the ERD

  • additionally
    • At first, since the AI mode is set at the CREW level, I was wondering whether AI_RESULT should also refer to CREW’s ai_mode like FK.

      • However, the roles of the two values were different.

      • CREW.ai_mode is the ‘AI mode value currently set by the crew’,

      • AI_RESULT.mode is ‘the mode value actually used when the AI result was generated’

      → In other words, the crew’s ai_mode may be changed later, but already generated AI results must remain in whatever mode they were created at the time of creation.

      • Therefore, rather than AI_RESULT directly referencing (FK) CREW.ai_mode, it was judged more appropriate to store the mode value used when generating AI results in AI_RESULT.

Lessons learned

• We found that a proper ERD can be created by not only drawing tables, but also considering actual user flow, FE screen structure, data responsibility, AI result management, operation and debugging, and future expansion possibilities.- At first, it seemed like the more tables you created, the better, but I felt that a clear structure with clear responsibility and flow of data was more important.

• Based on what we discussed today, we will have to consider connecting API design and actual service flow in the future.