Database Design in SQL

 Database Design in SQL

Database design is the process of creating a schema for a database that is efficient, flexible, and easy to maintain. Good database design involves understanding the data requirements, normalizing the data, and optimizing the schema for performance. Here's an example:

sql

Copy code

CREATE TABLE orders (

    id INT PRIMARY KEY,

    customer_id INT NOT NULL,

    order_date DATE NOT NULL,

    total_amount DECIMAL(10,2) NOT NULL,

    FOREIGN KEY (customer_id) REFERENCES customers(id)

);

CREATE TABLE order_items (

    id INT PRIMARY KEY,

    order_id INT NOT NULL,

    product_id INT NOT NULL,

    quantity INT NOT NULL,

    price DECIMAL(10,2) NOT NULL,

    FOREIGN KEY (order_id) REFERENCES orders(id),

    FOREIGN KEY (product_id) REFERENCES products(id)

);

In this example, we're creating a schema for an order management system. The schema includes two tables: "orders" and "order_items". The "orders" table contains information about the orders, including the customer id, order date, and total amount. The "order_items" table contains information about the individual items in each order, including the product id, quantity, and price. The tables are linked together with foreign keys to ensure data consistency.