Database examples

Ten complete, production-shaped database schemas — each with its ER diagram, table structure, relationships and the full SQL. Pick the one closest to your project, study it, then open it in the editor and make it yours.

E-commerce Database ER Diagram

ecommerce database

Customers, products, categories, orders, payments and reviews — the classic online store schema.

1:N 1:N 1:N 1:N 1:N 1:N 1:1 1:N 1:N N:M customers PKid integer Uemail varchar(255) full_name varchar(120) phone varchar(30) created_at timestamp addresses PKid integer FKcustomer_id integer line1 varchar(200) line2 varchar(200) city varchar(80) postal_code varchar(20) country varchar(60) categories PKid integer Uname varchar(80) FKparent_id integer products PKid integer FKcategory_id integer name varchar(150) description text price decimal(10,2) stock integer created_at timestamp tags PKid integer Ulabel varchar(40) product_tags PKproduct_id integer PKtag_id integer orders PKid integer FKcustomer_id integer status varchar(20) total decimal(10,2) created_at timestamp order_items PKid integer FKorder_id integer FKproduct_id integer quantity integer unit_price decimal(10,2) payments PKid integer FKorder_id integer amount decimal(10,2) method varchar(30) paid_at timestamp reviews PKid integer FKproduct_id integer FKcustomer_id integer rating integer comment text created_at timestamp
View details & SQL

SaaS Database ER Diagram

saas database

Users, workspaces, projects, tasks and billing — the backbone of a subscription SaaS.

1:N 1:1 1:1 1:1 1:N 1:N 1:N 1:N 1:N 1:N users PKid integer Uemail varchar(255) full_name varchar(120) password_hash varchar(255) created_at timestamp workspaces PKid integer name varchar(120) FKowner_id integer created_at timestamp workspace_members PKworkspace_id integer PKuser_id integer role varchar(20) subscriptions PKid integer FKworkspace_id integer plan varchar(30) status varchar(20) current_period_end timestamp invoices PKid integer FKsubscription_id integer amount decimal(10,2) status varchar(20) issued_at timestamp projects PKid integer FKworkspace_id integer name varchar(120) description text created_at timestamp tasks PKid integer FKproject_id integer FKassignee_id integer title varchar(200) status varchar(20) due_date date created_at timestamp comments PKid integer FKtask_id integer FKauthor_id integer body text created_at timestamp
View details & SQL

Blog Database ER Diagram

blog database

Users, posts, categories, comments and tags — a content platform schema with many-to-many tagging.

1:N 1:N 1:N 1:N N:M users PKid integer Uemail varchar(255) display_name varchar(80) role varchar(20) created_at timestamp categories PKid integer Uname varchar(80) Uslug varchar(100) posts PKid integer FKauthor_id integer FKcategory_id integer title varchar(200) Uslug varchar(220) content text published boolean published_at timestamp created_at timestamp tags PKid integer Ulabel varchar(40) post_tags PKpost_id integer PKtag_id integer comments PKid integer FKpost_id integer FKauthor_id integer body text created_at timestamp
View details & SQL

CRM Database ER Diagram

crm database

Companies, contacts, deals and activities — how a sales pipeline is modeled.

1:N 1:N 1:N 1:N 1:N 1:N 1:N 1:N 1:N 1:N 1:N 1:N companies PKid integer name varchar(150) website varchar(200) industry varchar(80) created_at timestamp contacts PKid integer FKcompany_id integer first_name varchar(60) last_name varchar(60) Uemail varchar(255) phone varchar(30) users PKid integer Uemail varchar(255) full_name varchar(120) deals PKid integer FKcompany_id integer FKcontact_id integer FKowner_id integer title varchar(150) amount decimal(12,2) stage varchar(30) expected_close date created_at timestamp activities PKid integer FKcontact_id integer FKdeal_id integer FKuser_id integer type varchar(20) summary varchar(255) occurred_at timestamp notes PKid integer FKcompany_id integer FKcontact_id integer FKuser_id integer body text created_at timestamp tasks PKid integer FKdeal_id integer FKassignee_id integer title varchar(200) due_date date done boolean
View details & SQL

School Management ER Diagram

school management database

Students, teachers, courses, classes, enrollments and attendance — a school in seven tables.

1:N 1:N 1:N 1:N 1:1 1:1 1:N 1:N departments PKid integer Uname varchar(100) teachers PKid integer FKdepartment_id integer full_name varchar(120) Uemail varchar(255) courses PKid integer FKdepartment_id integer Ucode varchar(15) title varchar(150) credits integer students PKid integer full_name varchar(120) Uemail varchar(255) enrolled_at date classes PKid integer FKcourse_id integer FKteacher_id integer semester varchar(20) room varchar(30) schedule varchar(80) enrollments PKstudent_id integer PKclass_id integer grade decimal(4,2) attendance PKid integer FKstudent_id integer FKclass_id integer session_date date status varchar(15)
View details & SQL

Hotel Booking ER Diagram

hotel booking database

Hotels, room types, rooms, guests, bookings and payments — a booking engine schema.

1:N 1:N 1:N 1:N 1:1 N:M hotels PKid integer name varchar(150) city varchar(80) country varchar(60) star_rating integer amenities PKid integer Ulabel varchar(60) room_types PKid integer FKhotel_id integer name varchar(80) capacity integer base_price decimal(10,2) room_type_amenities PKroom_type_id integer PKamenity_id integer rooms PKid integer FKroom_type_id integer room_number varchar(10) floor integer guests PKid integer Uemail varchar(255) full_name varchar(120) phone varchar(30) bookings PKid integer FKroom_id integer FKguest_id integer check_in date check_out date total_price decimal(10,2) status varchar(20) created_at timestamp payments PKid integer FKbooking_id integer amount decimal(10,2) method varchar(30) paid_at timestamp
View details & SQL

Inventory Management ER Diagram

inventory database

Warehouses, suppliers, products, stock levels and purchase orders — tracking goods end to end.

1:N 1:1 1:1 1:N 1:N 1:N 1:N warehouses PKid integer name varchar(100) location varchar(150) suppliers PKid integer name varchar(150) Uemail varchar(255) phone varchar(30) products PKid integer FKsupplier_id integer Usku varchar(40) name varchar(150) unit_price decimal(10,2) stock_levels PKwarehouse_id integer PKproduct_id integer quantity integer purchase_orders PKid integer FKsupplier_id integer FKwarehouse_id integer status varchar(20) ordered_at timestamp purchase_order_items PKid integer FKpurchase_order_id integer FKproduct_id integer quantity integer unit_cost decimal(10,2)
View details & SQL

Project Management ER Diagram

project management database

Organizations, projects, members, tasks, labels and sprints — a team tool schema.

1:N 1:1 1:1 1:N 1:N 1:N 1:N 1:N 1:N 1:N N:M organizations PKid integer name varchar(120) created_at timestamp users PKid integer Uemail varchar(255) full_name varchar(120) projects PKid integer FKorganization_id integer name varchar(120) description text created_at timestamp project_members PKproject_id integer PKuser_id integer role varchar(20) sprints PKid integer FKproject_id integer name varchar(80) starts_on date ends_on date tasks PKid integer FKproject_id integer FKsprint_id integer FKassignee_id integer title varchar(200) status varchar(20) story_points integer due_date date labels PKid integer FKproject_id integer label varchar(40) color varchar(9) task_labels PKtask_id integer PKlabel_id integer comments PKid integer FKtask_id integer FKauthor_id integer body text created_at timestamp
View details & SQL

Social Network ER Diagram

social network database

Users, posts, comments, likes, follows and private messages — the social graph schema.

1:N 1:N 1:N 1:1 1:1 1:1 1:1 1:N 1:N 1:N N:M users PKid integer Uusername varchar(30) Uemail varchar(255) bio text created_at timestamp posts PKid integer FKauthor_id integer content text created_at timestamp comments PKid integer FKpost_id integer FKauthor_id integer body text created_at timestamp likes PKuser_id integer PKpost_id integer created_at timestamp follows PKfollower_id integer PKfollowee_id integer created_at timestamp conversations PKid integer created_at timestamp conversation_participants PKconversation_id integer PKuser_id integer messages PKid integer FKconversation_id integer FKsender_id integer body text sent_at timestamp notifications PKid integer FKuser_id integer type varchar(30) payload json read_at timestamp created_at timestamp
View details & SQL

Restaurant Ordering ER Diagram

restaurant ordering database

Restaurants, menu items, customers, orders and reservations — from menu to kitchen ticket.

1:N 1:N 1:N 1:N 1:N 1:N 1:N 1:N 1:N restaurants PKid integer name varchar(120) address varchar(200) phone varchar(30) menu_categories PKid integer FKrestaurant_id integer name varchar(80) menu_items PKid integer FKmenu_category_id integer name varchar(120) description text price decimal(8,2) available boolean customers PKid integer Uemail varchar(255) full_name varchar(120) phone varchar(30) orders PKid integer FKrestaurant_id integer FKcustomer_id integer order_type varchar(15) status varchar(20) placed_at timestamp order_items PKid integer FKorder_id integer FKmenu_item_id integer quantity integer unit_price decimal(8,2) reservations PKid integer FKrestaurant_id integer FKcustomer_id integer reserved_at timestamp party_size integer status varchar(20) staff PKid integer FKrestaurant_id integer full_name varchar(120) role varchar(30)
View details & SQL

Library Management ER Diagram

library management database

Authors, books, members, loans and fines — catalog and circulation for a lending library.

1:N 1:N 1:N 1:1 N:M categories PKid integer Uname varchar(80) authors PKid integer full_name varchar(120) country varchar(60) books PKid integer FKcategory_id integer Uisbn varchar(20) title varchar(200) published_year integer total_copies integer book_authors PKbook_id integer PKauthor_id integer members PKid integer Uemail varchar(255) full_name varchar(120) joined_at date status varchar(20) loans PKid integer FKbook_id integer FKmember_id integer borrowed_at timestamp due_date date returned_at timestamp fines PKid integer FKloan_id integer amount decimal(8,2) reason varchar(120) issued_at timestamp paid_at timestamp
View details & SQL

Hospital Management ER Diagram

hospital management database

Departments, doctors, patients, appointments, prescriptions, admissions and invoices.

1:N 1:N 1:N 1:1 1:N 1:N 1:N 1:N 1:1 departments PKid integer Uname varchar(100) floor integer doctors PKid integer FKdepartment_id integer full_name varchar(120) speciality varchar(80) Uemail varchar(255) patients PKid integer full_name varchar(120) date_of_birth date Uemail varchar(255) phone varchar(30) appointments PKid integer FKpatient_id integer FKdoctor_id integer scheduled_for timestamp status varchar(20) notes text medications PKid integer Uname varchar(120) strength varchar(40) prescriptions PKid integer FKappointment_id integer issued_at timestamp notes text prescription_items PKid integer FKprescription_id integer FKmedication_id integer dosage varchar(80) quantity integer admissions PKid integer FKpatient_id integer FKdoctor_id integer room_number varchar(10) bed_number varchar(10) admitted_at timestamp discharged_at timestamp daily_rate decimal(10,2) invoices PKid integer FKadmission_id integer amount decimal(12,2) status varchar(20) issued_at timestamp
View details & SQL

Banking Database ER Diagram

banking database

Branches, customers, accounts, holders, transactions, cards and transfers — a retail bank core.

1:N 1:N 1:1 1:1 1:N 1:1 1:N 1:N branches PKid integer name varchar(120) city varchar(80) opened_on date customers PKid integer full_name varchar(120) Uemail varchar(255) phone varchar(30) created_at timestamp account_types PKid integer Uname varchar(60) interest_rate decimal(5,2) accounts PKid integer FKbranch_id integer FKaccount_type_id integer Uaccount_number varchar(20) opened_at date balance decimal(14,2) status varchar(20) account_holders PKaccount_id integer PKcustomer_id integer holder_role varchar(20) transactions PKid integer FKaccount_id integer direction varchar(6) amount decimal(14,2) balance_after decimal(14,2) description varchar(200) occurred_at timestamp cards PKid integer FKaccount_id integer Ucard_number varchar(20) expiry_date date status varchar(20) transfers PKid integer FKfrom_account_id integer FKto_account_id integer amount decimal(14,2) created_at timestamp
View details & SQL

Fitness Gym ER Diagram

fitness gym database

Members, plans, subscriptions, trainers, classes, sessions, bookings and check-ins.

1:N 1:N 1:N 1:N 1:N 1:N 1:N 1:N membership_plans PKid integer Uname varchar(80) price decimal(8,2) duration_days integer members PKid integer full_name varchar(120) Uemail varchar(255) phone varchar(30) joined_at date subscriptions PKid integer FKmember_id integer FKplan_id integer starts_on date ends_on date status varchar(20) trainers PKid integer full_name varchar(120) Uemail varchar(255) specialty varchar(80) classes PKid integer FKtrainer_id integer name varchar(120) description text capacity integer class_sessions PKid integer FKclass_id integer starts_at timestamp room varchar(40) duration_minutes integer bookings PKid integer FKsession_id integer FKmember_id integer booked_at timestamp status varchar(20) check_ins PKid integer FKmember_id integer FKsession_id integer checked_in_at timestamp
View details & SQL

Food Delivery ER Diagram

food delivery database

Restaurants, menus, customers, addresses, couriers, orders and deliveries — from basket to doorstep.

1:N 1:N 1:N 1:N 1:N 1:N 1:N 1:N 1:1 1:N restaurants PKid integer name varchar(120) city varchar(80) phone varchar(30) menu_categories PKid integer FKrestaurant_id integer name varchar(80) display_order integer menu_items PKid integer FKmenu_category_id integer name varchar(120) description text price decimal(8,2) available boolean customers PKid integer full_name varchar(120) Uemail varchar(255) phone varchar(30) addresses PKid integer FKcustomer_id integer line1 varchar(200) city varchar(80) postal_code varchar(20) is_default boolean couriers PKid integer full_name varchar(120) phone varchar(30) vehicle_type varchar(20) active boolean orders PKid integer FKcustomer_id integer FKrestaurant_id integer FKaddress_id integer status varchar(20) total decimal(10,2) placed_at timestamp order_items PKid integer FKorder_id integer FKmenu_item_id integer item_name varchar(120) quantity integer unit_price decimal(8,2) deliveries PKid integer FKorder_id integer FKcourier_id integer status varchar(20) picked_up_at timestamp delivered_at timestamp
View details & SQL

HR and Payroll ER Diagram

hr payroll database

Departments, employees, job titles, payroll runs, payslips, leave and attendance.

1:N 1:N 1:N 1:N 1:N 1:N 1:N departments PKid integer Uname varchar(100) job_titles PKid integer Utitle varchar(100) min_salary decimal(12,2) employees PKid integer FKdepartment_id integer FKjob_title_id integer FKmanager_id integer full_name varchar(120) Uemail varchar(255) hired_on date status varchar(20) payroll_runs PKid integer period_start date period_end date status varchar(20) payslips PKid integer FKpayroll_run_id integer FKemployee_id integer gross_pay decimal(12,2) deductions decimal(12,2) net_pay decimal(12,2) leave_requests PKid integer FKemployee_id integer leave_type varchar(40) starts_on date ends_on date status varchar(20) attendance PKid integer FKemployee_id integer work_date date hours_worked decimal(5,2)
View details & SQL