商会联系人管理数据库设计需求及业务规则咨询

Hey noobcoder, let's work through designing this chamber of commerce contact management database together. Based on your requirements, here's a clear, normalized schema that covers all your needs and adheres to the business rules you mentioned:

1. Chamber Staff (Internal Users) #

This table stores your chamber's own employees and their roles within the organization:

• user_id (INT, PRIMARY KEY, AUTO_INCREMENT): Unique identifier for each staff member
• full_name (VARCHAR(100)): Staff member's full name
• email (VARCHAR(100), UNIQUE): Work email address
• phone (VARCHAR(20)): Work phone number
• role (VARCHAR(50)): Role in the chamber (e.g., 'Admin', 'Member Liaison', 'Data Coordinator')
• created_at (DATETIME): Timestamp when the user was added

2. Registered Enterprises #

This table tracks all businesses (both member and non-member) that are registered with the chamber:

• enterprise_id (INT, PRIMARY KEY, AUTO_INCREMENT): Unique identifier for each enterprise
• enterprise_name (VARCHAR(150)): Legal name of the business
• enterprise_type (VARCHAR(20)): Distinguishes member vs non-member (e.g., 'Member', 'Non-Member')
• registration_number (VARCHAR(50), UNIQUE): Official business registration number
• description (TEXT): Optional details about the business
• created_at (DATETIME): Timestamp when the enterprise was registered

3. External Contacts #

This table holds all non-chamber staff (people employed by registered enterprises). Aligns with your rule: one person works for exactly one enterprise, one enterprise can have multiple people:

• contact_id (INT, PRIMARY KEY, AUTO_INCREMENT): Unique identifier for each external contact
• full_name (VARCHAR(100)): Contact's full name
• email (VARCHAR(100)): Contact's work email
• phone (VARCHAR(20)): Contact's work phone
• position (VARCHAR(100)): Job title at the enterprise
• enterprise_id (INT, FOREIGN KEY REFERENCES Registered Enterprises(enterprise_id)): Links the contact to their employer
• created_at (DATETIME): Timestamp when the contact was added

4. Addresses #

Since both enterprises and contacts can have multiple addresses (e.g., business registration address, office location, home address), we use a single table with polymorphic association to handle this flexibility:

• address_id (INT, PRIMARY KEY, AUTO_INCREMENT): Unique identifier for each address
• address_line1 (VARCHAR(150)): Primary address line
• address_line2 (VARCHAR(150), NULL): Secondary address line (suite number, etc.)
• city (VARCHAR(100)): City name
• state (VARCHAR(50)): State/province
• postal_code (VARCHAR(20)): Postal/ZIP code
• country (VARCHAR(100)): Country name
• address_type (VARCHAR(50)): Type of address (e.g., 'Registration', 'Office', 'Home')
• related_id (INT): ID of the associated enterprise or contact
• related_type (VARCHAR(50)): Specifies if the address belongs to an 'Enterprise' or 'Contact'

5. Assigned Tasks #

This table tracks tasks that your chamber staff are responsible for, linked to relevant contacts or enterprises:

• task_id (INT, PRIMARY KEY, AUTO_INCREMENT): Unique identifier for each task
• task_name (VARCHAR(150)): Short title for the task
• task_description (TEXT): Detailed task instructions
• due_date (DATE, NULL): Deadline for the task
• status (VARCHAR(20)): Current task status (e.g., 'Pending', 'In Progress', 'Completed')
• assigned_to_user_id (INT, FOREIGN KEY REFERENCES Chamber Staff(user_id)): Links the task to the responsible chamber employee
• related_contact_id (INT, FOREIGN KEY REFERENCES External Contacts(contact_id), NULL): Optional link to a specific contact the task relates to
• related_enterprise_id (INT, FOREIGN KEY REFERENCES Registered Enterprises(enterprise_id), NULL): Optional link to a specific enterprise the task relates to
• created_at (DATETIME): Timestamp when the task was created

• Chamber Staff ↔ Tasks: One-to-many (a single staff member can manage multiple tasks)
• Registered Enterprises ↔ External Contacts: One-to-many (one business can have multiple employees)
• Registered Enterprises/External Contacts ↔ Addresses: One-to-many (each can have multiple addresses, handled via polymorphic association)
• Tasks ↔ Contacts/Enterprises: Optional many-to-one (a task can be tied to a contact, an enterprise, both, or neither depending on the task's focus)

• Add indexes to all foreign key fields (enterprise_id, assigned_to_user_id, etc.) to speed up join queries
• For fields like enterprise_type, role, and status, consider using ENUM types instead of VARCHAR if the options are fixed (this saves space and ensures data consistency)
• If you need to track task updates over time, add a task_history table linked to task_id to log status changes and notes

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