How would you design a database?

Database design includes the following stages:

1. Requirements gathering and analysis: Understanding the subject area, business processes, and the information to be stored. Defining entities, their attributes, and relationships.
2. Conceptual design (ER diagram): Creating a high-level data model representing entities, attributes, and relationships independently of any specific DBMS. An ER diagram (Entity-Relationship Diagram) is used.
3. Logical design: Transforming the conceptual model into a relational schema.
   • Defining tables (entities).
   • Defining columns (attributes).
   • Defining primary keys.
   • Defining foreign keys to implement relationships (one-to-one, one-to-many, many-to-many). For many-to-many relationships, an intermediate table is created.
   • Normalization: Bringing the schema to normal forms (usually up to the third normal form, 3NF) to eliminate redundancy and anomalies in insert, update, and delete operations.
4. Physical design: Defining the specific implementation of the logical model in the chosen DBMS.
   • Choosing data types for columns.
   • Defining indexes to optimize queries.
   • Selecting storage mechanisms (e.g., MyISAM, InnoDB in MySQL).
   • Defining logical and physical object names.
   • Setting constraints (CHECK, UNIQUE, and others).
   • Planning partitioning if necessary for large tables.
5. Implementation: Creating the database and tables using DDL (Data Definition Language).
6. Testing and optimization: Checking query performance, identifying bottlenecks, and making adjustments to the schema or indexes.

Example of a partial schema for an online store:

Table users:

Column	Data type (example)	Description
id	INT (PRIMARY KEY)	Unique ID
username	VARCHAR(255)	User name
email	VARCHAR(255)	Email (UNIQUE)
password	VARCHAR(255)	Password hash
created_at	TIMESTAMP	Record creation date

Table products:

Column	Data type (example)	Description
id	INT (PRIMARY KEY)	Unique ID
name	VARCHAR(255)	Product name
description	TEXT	Description
price	DECIMAL(10, 2)	Price
category_id	INT (FOREIGN KEY)	Category ID

Table orders:

Column	Data type (example)	Description
id	INT (PRIMARY KEY)	Unique ID
user_id	INT (FOREIGN KEY)	Customer ID
total_price	DECIMAL(10, 2)	Total cost
created_at	TIMESTAMP	Order creation date

Table order_items (for many-to-many relationship between orders and products):

Column	Data type (example)	Description
order_id	INT (FOREIGN KEY)	Order ID
product_id	INT (FOREIGN KEY)	Product ID
quantity	INT	Quantity of the product in order
price	DECIMAL(10, 2)	Price per unit at order time

Example DDL for creating the users table:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);