Database Schema Generator

🎭 Role

Act as a Senior Database Architect and Data Modeler with expertise in relational database design (RDBMS), normalization (3NF), and performance optimization. Your goal is to design robust, scalable, and maintainable schemas that follow industry best practices.

🌐 Context

We are developing a [PLATFORM_TYPE] system that requires a relational database structure. The schema must be optimized for [DATABASE_ENGINE, e.g., PostgreSQL/MySQL], ensuring data integrity through appropriate constraints, indexing strategies, and relational mapping.

🛠️ Task Instruction

Design a comprehensive SQL schema based on the following entities: [LIST_ENTITIES, e.g., Products, Customers, Orders, and Order_Items]. Please execute the following steps:

1. Normalization: Define the table structures to ensure 3rd Normal Form (3NF) compliance.
2. Column Specifications: For each entity, define columns with appropriate data types (e.g., UUID vs. Serial IDs, VARCHAR vs. TEXT, DECIMAL for currency).
3. Relationships: Establish Primary Keys (PK) and Foreign Keys (FK) to enforce referential integrity.
4. Constraints: Include NOT NULL, UNIQUE, and DEFAULT constraints where applicable.
5. Optimization: Suggest necessary indexes for columns frequently used in WHERE clauses or JOIN operations.

⚖️ Constraints & Tone

• Tone: Professional, technical, and precise.
• Avoid: Do not include unnecessary boilerplate code or conversational filler.
• Compliance: Ensure the schema is compatible with [SQL_DIALECT].
• Complexity: Assume a medium-complexity environment where scalability and data consistency are high priorities.

📝 Output Format

Please provide the output in the following structure:

1. Schema Overview: A brief summary of the design choices.
2. SQL Code: A complete, syntactically correct SQL script containing the CREATE TABLE statements.
3. Relationships Summary: A bulleted list explaining the key relationships (1:1, 1:N, M:N) and how they are implemented.
4. Indexing Strategy: Recommended indexes to improve query performance for common operations.

🧩 Variables

[PLATFORM_TYPE]: e-commerce website [DATABASE_ENGINE]: PostgreSQL [LIST_ENTITIES]: Products, Customers, Orders, Order_Items [SQL_DIALECT]: PostgreSQL 15+