Basic Relational Database Schemas 📚 to amplify development

Whether you are using either non-relational or relational DBMS the fundamental would be the same. It contents key-value pair which has some default value before assignment. Please save it for future reference 😉.

Table: users

Column Name	Data Type	Constraints	Description
user_id	BIGINT UNSIGNED	PRIMARY KEY, AUTO_INCREMENT	Unique User ID
user_name	VARCHAR(50)	NOT NULL	Full Name
user_email	VARCHAR(100)	NOT NULL, UNIQUE	Email Address
password	VARCHAR(255)	NOT NULL	Hashed Password
role	ENUM('admin', 'user', 'editor')	DEFAULT 'user'	User Role
status	TINYINT(1)	DEFAULT 1	1 = Active, 0 = Inactive
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Timestamp of creation
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	Last update timestamp

---

Table: posts

Column Name	Data Type	Constraints	Description
post_id	BIGINT UNSIGNED	PRIMARY KEY, AUTO_INCREMENT	Unique Post ID
user_id	BIGINT UNSIGNED	FOREIGN KEY -> users(user_id), NOT NULL	Post Author ID
title	VARCHAR(255)	NOT NULL	Post Title
content	TEXT	NOT NULL	Post Content
status	ENUM('draft', 'published', 'archived')	DEFAULT 'draft'	Post Status
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Timestamp of creation
updated_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	Last update timestamp

Table: comments

Column Name	Data Type	Constraints	Description
comment_id	BIGINT UNSIGNED	PRIMARY KEY, AUTO_INCREMENT	Unique Comment ID
post_id	BIGINT UNSIGNED	FOREIGN KEY -> posts(post_id), NOT NULL	Related Post ID
user_id	BIGINT UNSIGNED	FOREIGN KEY -> users(user_id), NULLABLE	Comment Author ID
comment	TEXT	NOT NULL	Comment Content
status	TINYINT(1)	DEFAULT 1	1 = Approved, 0 = Pending
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Timestamp of creation

Table: categories

Column Name	Data Type	Constraints	Description
category_id	BIGINT UNSIGNED	PRIMARY KEY, AUTO_INCREMENT	Unique Category ID
name	VARCHAR(100)	NOT NULL, UNIQUE	Category Name
slug	VARCHAR(100)	NOT NULL, UNIQUE	URL Slug
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Timestamp of creation

Side Menu Table Schema (with Sub-Menus)

Table: side_menu

Column Name	Data Type	Constraints	Description
menu_id	BIGINT UNSIGNED	PRIMARY KEY, AUTO_INCREMENT	Unique Menu ID
parent_id	BIGINT UNSIGNED	NULLABLE, FOREIGN KEY -> side_menu(menu_id)	Reference to Parent Menu ID (for sub-menus)
name	VARCHAR(50)	NOT NULL	Menu Name
icon	TEXT	NOT NULL	SVG Icon Data
permission_level	TINYINT UNSIGNED	NOT NULL	Required Permission
created_at	TIMESTAMP	DEFAULT CURRENT_TIMESTAMP	Timestamp of creation

How it Works?

• If parent_id is NULL, it’s a main menu item.
• If parent_id has a value, it’s a sub-menu under the referenced menu_id.

Example Data

menu_id	parent_id	name	icon	permission_level
1	NULL	Dashboard	🏠	1
2	NULL	Settings	⚙️	2
3	2	Users	👥	2
4	2	Roles	🔑	2

Here, "Users" and "Roles" are sub-menus under "Settings". 🚀