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". 🚀
