如何创建支持月度数据插入及计算的数据库表？高尔夫球场维护评估需求

Hey there! Let’s walk through building this golf course maintenance scoring system step by step—this kind of tool is perfect for keeping track of upkeep quality across individual holes. Here’s how to approach the key parts:

Your core fields make sense, but splitting out reusable data (job categories, specific work items, frequencies) into separate tables will make updates easier and avoid duplicate entries. Here’s a recommended setup:

Core Score Table (maintenance_scores) #

• score_id (Primary Key, auto-increment): Unique ID for each submission
• employee_id (Foreign Key to an employees table): Tracks who submitted the scores
• submission_month (DATE or VARCHAR in YYYY-MM format): Filters scores by month
• job_category_id (Foreign Key to job_categories): Links to the work category
• job_of_work_id (Foreign Key to job_of_work): Links to the specific task
• frequency_id (Foreign Key to frequencies): Links to how often the task is done
• hole1/hole2/hole3/hole4 (TINYINT with CHECK constraint): Ensures scores stay between 1-5

Supporting Tables (for editable categories) #

• job_categories: category_id (PK), category_name (VARCHAR, unique) – e.g., "Turf Care", "Bunker Maintenance"
• job_of_work: job_id (PK), job_name (VARCHAR, unique), category_id (FK to job_categories) – e.g., "Mow Fairway" under "Turf Care"
• frequencies: frequency_id (PK), frequency_label (VARCHAR) – e.g., "Daily", "Weekly", "Monthly"

This setup lets users add/update categories, tasks, and frequencies without messing with the core score data.

Adding and updating these reusable fields is straightforward with basic SQL or backend endpoints. Here are quick examples:

Add a New Job Category #

INSERT INTO job_categories (category_name) VALUES ('Irrigation System Checks') 
ON DUPLICATE KEY UPDATE category_name = VALUES(category_name);

The ON DUPLICATE KEY prevents duplicate entries if someone tries to add a category that already exists.

Update an Existing Frequency #

UPDATE frequencies SET frequency_label = 'Bi-Weekly' WHERE frequency_id = 3;

On the frontend, build simple forms for these actions (with validation to ensure names aren’t empty or duplicates) and connect them to RESTful endpoints (e.g., POST /api/categories for adds, PUT /api/frequencies/{id} for updates).

Since scores are 1-5, the simplest way to convert a score to a completion percentage is to treat 5 as 100% and scale down from there:
Completion % = (Score / 5) * 100

Example Queries #

Get Average Completion per Hole for a Month

SELECT
  submission_month,
  ROUND(AVG((hole1 / 5) * 100), 2) AS hole1_avg_completion,
  ROUND(AVG((hole2 / 5) * 100), 2) AS hole2_avg_completion,
  ROUND(AVG((hole3 / 5) * 100), 2) AS hole3_avg_completion,
  ROUND(AVG((hole4 / 5) * 100), 2) AS hole4_avg_completion
FROM maintenance_scores
WHERE submission_month = '2024-09'
GROUP BY submission_month;

Get Completion for a Specific Task Across All Holes

SELECT
  c.category_name,
  j.job_name,
  f.frequency_label,
  ROUND((hole1 / 5) * 100, 2) AS hole1_completion,
  ROUND((hole2 / 5) * 100, 2) AS hole2_completion,
  ROUND((hole3 / 5) * 100, 2) AS hole3_completion,
  ROUND((hole4 / 5) * 100, 2) AS hole4_completion
FROM maintenance_scores s
JOIN job_categories c ON s.job_category_id = c.category_id
JOIN job_of_work j ON s.job_of_work_id = j.job_id
JOIN frequencies f ON s.frequency_id = f.frequency_id
WHERE s.score_id = 45; -- Replace with your score ID

• Frontend Validation: Restrict score inputs to 1-5 (use dropdowns or input boxes with min/max rules) to prevent bad data from being submitted.
• Backend Checks: Add constraints in your database (like the CHECK for scores) and validate inputs in your backend code (e.g., using @Min(1) and @Max(5) in Spring, or Django’s MinValueValidator/MaxValueValidator).
• Visualize Data: Use charts (bar charts work great) to display completion percentages per hole or task—this makes it way easier for teams to spot problem areas.
• Track History: Keep all monthly submissions in the database so you can compare maintenance quality over time.

内容的提问来源于stack exchange，提问作者J. Lee