Simple Database Schema Definition

A clear, concise description of the database structure, including tables and their basic fields.

Simple Database Schema Definition Template

(For each Table):

Table Name: [Name of the table (singular form, e.g., users, products, orders)]

• Description: [A brief explanation of what this table stores]
• Columns:
  • [Column Name]: [Data Type] [Constraints (e.g., PRIMARY KEY, FOREIGN KEY referencing other_table(column), NOT NULL, UNIQUE)] - [Brief Description/Purpose of this column]
  • [Another Column Name]: [Data Type] [Constraints] - [Brief Description]
  • (Add more columns as needed)
• Primary Key: [Name of the primary key column(s)]
• Foreign Keys:
  • [Column Name] referencing [Other Table Name(Column Name)]
  • (Add more foreign keys as needed)
• Relationships (Briefly describe how this table relates to others):
  • [e.g., users has a one-to-many relationship with orders (one user can have multiple orders)]

(Repeat the above structure for each table in your database)

Example using the template (for a simple e-commerce site)

Table Name: products

• Description: Stores information about the products available for sale.
• Columns:
  • product_id: INTEGER PRIMARY KEY AUTOINCREMENT - Unique identifier for each product.
  • name: VARCHAR(255) NOT NULL - Name of the product.
  • description: TEXT - Detailed description of the product.
  • price: DECIMAL(10, 2) NOT NULL 1 - Price of the product.
  • stock_quantity: INTEGER NOT NULL DEFAULT 0 - Number of units currently in stock.
• Primary Key: product_id
• Foreign Keys: None in this simple example.
• Relationships: products has a many-to-many relationship with orders (via an order_items table - defined below).

Table Name: orders

• Description: Stores information about customer orders.
• Columns:
  • order_id: INTEGER PRIMARY KEY AUTOINCREMENT - Unique identifier for each order.
  • user_id: INTEGER NOT NULL FOREIGN KEY referencing users(user_id) - ID of the user who placed the order.
  • order_date: TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP - Date and time the order was placed.
  • total_amount: DECIMAL(10, 2) NOT NULL - Total value of the order.
• Primary Key: order_id
• Foreign Keys: user_id referencing users(user_id)
• Relationships: orders has a one-to-many relationship with users. orders has a many-to-many relationship with products (via order_items).

Table Name: order_items

• Description: Stores the individual items included in each order.
• Columns:
  • item_id: INTEGER PRIMARY KEY AUTOINCREMENT - Unique identifier for each item in an order.
  • order_id: INTEGER NOT NULL FOREIGN KEY referencing orders(order_id) - ID of the order this item belongs to.
  • product_id: INTEGER NOT NULL FOREIGN KEY referencing products(product_id) - ID of the product in this item.
  • quantity: INTEGER NOT NULL DEFAULT 1 - Quantity of this product in the order.
  • item_price: DECIMAL(10, 2) NOT NULL - Price of the product at the time of the order.
• Primary Key: item_id
• Foreign Keys: order_id referencing orders(order_id), product_id referencing products(product_id)
• Relationships: This is a joining table establishing the many-to-many relationship between orders and products.