Design Customer Satisfaction Database

⚙️ What This Mega-Prompt Does:

• Designs a normalized and optimized database schema for tracking customer satisfaction metrics.

• Includes specific columns, data types, constraints, and indexes as outlined in the criteria.

• Ensures the schema supports efficient querying and maintains data integrity.

❓Tips:

• Ensure that each column in your table schema not only meets the basic requirements but also anticipates future scalability needs. For instance, consider using scalable data types or adding additional columns that might become relevant as the database grows.

• Implement comprehensive testing on your schema by simulating different data loads and query scenarios to assess performance and identify potential bottlenecks or data integrity issues before going live.

• Document your schema design thoroughly, including the rationale behind choosing specific data types, constraints, and indexes. This documentation will be invaluable for future maintenance or when scaling the database.

❓ Database Schema Optimizer ChatGPT Mega Prompt

#CONTEXT:
You are an expert database designer tasked with creating an efficient table schema for tracking customer satisfaction metrics based on given requirements. The schema should be normalized and optimized for performance and data integrity.

#ROLE:
As an expert database designer, your role is to design a table structure with appropriate column names, data types, and any necessary constraints or indexes to effectively track customer satisfaction metrics.

#RESPONSE GUIDELINES:
- Provide a clear and concise table structure
- Use appropriate column names and data types
- Include necessary constraints and indexes for data integrity and performance
- Ensure the schema is normalized to avoid data redundancy and anomalies
- Optimize the schema for efficient querying and data retrieval

#TABLE SCHEMA CRITERIA:
1. Include columns for customer ID, customer name, last interaction date, satisfaction score, next step, and engagement status
2. Use appropriate data types for each column (e.g., INT for customer ID, VARCHAR for customer name)
3. Add a primary key constraint on the customer ID column
4. Include check constraints for satisfaction score (between 1 and 5) and engagement status (Active, Inactive, or Churned)
5. Create indexes on columns frequently used for querying (e.g., customer name, last interaction date, satisfaction score, engagement status)

#INFORMATION ABOUT ME:
- My database management system: [SPECIFY DATABASE MANAGEMENT SYSTEM]
- My specific requirements for the schema: [LIST ANY ADDITIONAL REQUIREMENTS]

#RESPONSE FORMAT:
CREATE TABLE customer_satisfaction (
  column_name data_type constraint,
  ...
);

CREATE INDEX index_name ON table_name (column_name);
...

❓How To Use The Prompt:

• Fill in the [SPECIFY DATABASE MANAGEMENT SYSTEM] and [LIST ANY ADDITIONAL REQUIREMENTS] placeholders with the specific database system you are using and any unique requirements you have for the schema. For example, if you are using MySQL and need the schema to support multi-language customer names, specify these details.

• Example: "My database management system is MySQL. My specific requirements for the schema include support for multi-language customer names and real-time data updates."

❓ Example Input:

#INFORMATION ABOUT ME:

• My database management system: MySQL
• My specific requirements for the schema: Ensure all date fields store both date and time, use UTF-8 encoding for all text fields to support multiple languages.

❓ Example Output:

❓Additional Tips:

• Consider denormalizing certain columns if it significantly improves query performance, but weigh the trade-offs carefully to ensure data integrity is maintained.

• Utilize database-specific features like stored procedures or triggers to automate routine tasks or enforce complex business rules directly within the database.

• Regularly monitor and optimize query performance by analyzing execution plans, indexing strategies, and database statistics to ensure efficient data retrieval.

• Implement data archiving strategies for historical customer satisfaction data to prevent database bloat and improve overall system performance.