Database Design

High Level Database Model

• Purpose: The high-level database model provides a broad overview of the database structure, outlining the main entities and their relationships without going into much detail.
• Detail Level: This model includes entities and their relationships but avoids detailed implementation specifics.
• Abstraction Level: It is abstract, focusing on the big picture and the core components of the database system.

Example (High Level Database Model in dbdiagram.io)

Table users {
  id int [pk, increment]
  name varchar
  email varchar
}

Table orders {
  id int [pk, increment]
  user_id int [ref: > users.id]
  order_date date
}

Table products {
  id int [pk, increment]
  name varchar
  price decimal
}

Table order_items {
  id int [pk, increment]
  order_id int [ref: > orders.id]
  product_id int [ref: > products.id]
  quantity int
}

Entity Relationship Diagram (ERD)

• Purpose: ERDs are used to visualize the relationships between different entities in a database. They help in the conceptual design phase to understand how entities like tables will relate to each other.
• Detail Level: ERDs focus on entities (tables), their attributes (columns), and the relationships (foreign keys) between them. They don't typically include implementation details like specific data types or indexes.
• Abstraction Level: ERDs are more abstract and used for planning and communicating the database structure at a high level.

Example (ERD in dbdiagram.io)

Table users {
  id int [pk, increment]
  name varchar
  email varchar
  created_at timestamp
}

Table orders {
  id int [pk, increment]
  user_id int [ref: > users.id]
  order_date date
  status varchar
}

Table products {
  id int [pk, increment]
  name varchar
  description text
  price decimal
  stock_quantity int
}

Table order_items {
  id int [pk, increment]
  order_id int [ref: > orders.id]
  product_id int [ref: > products.id]
  quantity int
  price decimal
}

Ref: users.id < orders.user_id
Ref: orders.id < order_items.order_id
Ref: products.id < order_items.product_id

This ERD example shows the relationships between users, orders, products, and order_items tables, illustrating how they interact with each other in the database.

Note: Usualy they are very abstract, but can be similar to high level model.

Presentation Database Model

• Purpose: This model emphasizes how data is presented and used, including views, indexes, and other presentation elements that optimize querying and reporting.
• Detail Level: It includes additional details like views, derived data, and presentation-related constructs.
• Abstraction Level: Less abstract than the high-level model but still focused on how data is viewed and interacted with rather than the full technical implementation.

Example (Presentation Database Model in dbdiagram.io)

Table users {
  id int [pk, increment]
  name varchar
  email varchar
  created_at timestamp
}

Table orders {
  id int [pk, increment]
  user_id int [ref: > users.id]
  order_date date
  status varchar
}

Table products {
  id int [pk, increment]
  name varchar
  description text
  price decimal
  stock_quantity int
}

Table order_items {
  id int [pk, increment]
  order_id int [ref: > orders.id]
  product_id int [ref: > products.id]
  quantity int
  price decimal
}

Table user_order_summary_view {
  user_id int
  user_name varchar
  total_orders int
  total_spent decimal
}

Low Level Database Model

• Purpose: This model focuses on the detailed implementation of the database, including specific data types, constraints, indexes, and storage details. It is used for the actual creation and maintenance of the database.
• Detail Level: It is very detailed, covering all aspects needed for implementation.
• Abstraction Level: The least abstract, as it is concerned with the actual structure and behavior of the database.

Example (Low Level Database Model in PostgreSQL)

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    order_date DATE NOT NULL,
    status VARCHAR(50) NOT NULL
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT NOT NULL
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(id),
    product_id INT REFERENCES products(id),
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

CREATE VIEW user_order_summary AS
SELECT 
    u.id AS user_id,
    u.name AS user_name,
    COUNT(o.id) AS total_orders,
    SUM(oi.quantity * oi.price) AS total_spent
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
GROUP BY u.id, u.name;

MyShop Database model in dbdiagram.io

Table products {
	product_id bigint [primary key, increment]
	name varchar(50)
	quantity integer [default: 0]
}

Table categories {
	category_id bigint [primary key, increment]
	name varchar(50)
}

Table clients {
  client_id bigint [primary key, increment]
  name varchar(50)
}

Table orders {
	order_id bigint [primary key]
	client_id bigint
	order_date date [default: `now()`]
}

Table product_category {
	product_id bigint
	category_id bigint
  indexes {
    (product_id, category_id) [pk]
  }
}

Table ordered_products {
	product_id bigint
	order_id bigint
  quantity integer
  indexes {
    (product_id, order_id) [pk]
  }
}

Ref: orders.client_id > clients.client_id

Ref: product_category.category_id > categories.category_id
Ref: product_category.product_id > products.product_id

Ref: ordered_products.order_id > orders.order_id
Ref: ordered_products.product_id > products.product_id