如何规范调整ERD图？基于销售遴选专家场景的优化咨询

Hey Abdullah, let's tackle how to get your ERD properly structured for this expert selection workflow. Based on the scenario you described—sales teams filtering experts by their available daily/monthly time slots and employment type (full-time/part-time)—here's a clear breakdown of adjustments and entity relationships to add:

1. Core Entity Adjustments #

First, let's tweak your existing Expert entity to cover the employment type filter:

• Add a work_type attribute to the Expert table. Since this is a fixed set of options, use an enumerated type (e.g., ENUM('full_time', 'part_time')) to enforce consistency. If you ever need to expand this (like adding 'contract'), you could split this into a separate WorkType lookup table (with work_type_id as PK) and create a one-to-many relationship with Expert, but for a simple two-option scenario, an enum is cleaner.

2. Add a New Availability Entity for Time Slots #

Storing daily/monthly availability directly as attributes on Expert (e.g., daily_start_time, monthly_end_date) would lead to redundant, hard-to-maintain data. Instead, create a dedicated Availability entity to handle this structured, repeatable data:

Key Attributes for Availability: #

• availability_id (primary key, unique identifier for each time slot)
• expert_id (foreign key linking to Expert.expert_id—establishes a one-to-many relationship: one expert can have multiple availability slots)
• time_unit (enum: 'daily' or 'monthly' to distinguish slot types)
• start_time (time value, e.g., 09:00:00 for daily slots, or can pair with dates for monthly)
• end_time (corresponding end time for the slot)
• Conditional attributes (only required based on time_unit):
  • day_of_week (integer, 1=Monday to 7=Sunday) for daily recurring slots
  • month_start_day / month_end_day (integers 1-31) for monthly window slots (e.g., 1-15 for the first half of the month)

Example ERD Structure (Simplified): #

-- Expert Entity
CREATE TABLE Expert (
    expert_id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    work_type ENUM('full_time', 'part_time') NOT NULL,
    -- Add other expert-specific attributes (e.g., specialty, contact info)
);

-- Availability Entity
CREATE TABLE Availability (
    availability_id INT PRIMARY KEY AUTO_INCREMENT,
    expert_id INT NOT NULL,
    time_unit ENUM('daily', 'monthly') NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    day_of_week INT CHECK (day_of_week BETWEEN 1 AND 7), -- Only for daily slots
    month_start_day INT CHECK (month_start_day BETWEEN 1 AND 31), -- Only for monthly slots
    month_end_day INT CHECK (month_end_day BETWEEN 1 AND 31), -- Only for monthly slots
    FOREIGN KEY (expert_id) REFERENCES Expert(expert_id) ON DELETE CASCADE
);

3. Addressing Your Existing ERD's Marked Issues #

While I can't see the exact lines you marked, here are common fixes for this scenario:

• If your current ERD stores multiple availability slots as separate columns on Expert (e.g., daily_slot_1, daily_slot_2), replace that with the Availability entity to follow 1NF (no repeating groups).
• If you don't have a way to track employment type, add the work_type attribute or WorkType relationship as outlined above.
• Ensure the one-to-many link between Expert and Availability is clearly defined—this lets you query all slots for a single expert, or filter experts who have overlapping slots with a sales request.

Final Notes #

You won't need any additional core entities beyond these two (unless your workflow includes other unmentioned requirements like booking requests or sales lead tracking). This structure keeps your data normalized, easy to query, and scalable if you need to add more slot types or employment categories later.

内容的提问来源于stack exchange，提问作者Abdullah Alfozan