Database Naming Convention Guide

📁 1. General Principles

Rule	Example
Use lowercase with underscores	user_profile, not UserProfile or UserProfile
Use singular table names	user, order, product
Avoid reserved keywords	app_user, order_entry
Be descriptive but concise	invoice_item, not ii
Be consistent everywhere	Consistency > Perfection

---

🗃️ 2. Table Names

Type	Naming Rule	Example
Entity	Singular noun	product, customer, order_entry
Relationship	entity1_entity2 (alphabetical)	product_category, user_role
Log / History	entity_log, entity_history	login_log, order_history

If a table name conflicts with a reserved word:

• Add a prefix/suffix: app_user, order_record, group_access

---

🔑 3. Primary Keys

Rule	Example
Always use id	id (in every table)
Use surrogate keys (integer auto-increment or UUIDs)
Avoid natural keys as primary unless there's a clear benefit

---

🔗 4. Foreign Keys

Rule	Example
Name columns as entity_id	user_id, order_id
Prefer using singular form	user_id not users_id

---

📊 5. Column Names

Type	Naming Rule	Example
Booleans	Use is_, has_, can_	is_active, has_paid
Timestamps	Use _at suffix	created_at, updated_at
Foreign Keys	entity_id	customer_id, role_id
Descriptive Names	Avoid vague terms	total_amount, not value

---

📚 6. Index Naming

Rule	Example
Use idx_<table>_<column>	idx_user_email, idx_order_date

---

🔄 7. Constraints and Keys

Type	Convention	Example
Primary Key	pk_<table>	pk_user
Foreign Key	fk_<from_table>_<to_table>	fk_order_user
Unique Constraint	uq_<table>_<column>	uq_user_email
Check Constraint	chk_<table>_<column>	chk_user_age

---

🧪 8. Views & Stored Procedures (if used)

Type	Convention	Example
View	vw_<name>	vw_active_users
Stored Procedure	sp_<action>_<entity>	sp_get_customer_orders

---

🚀 Example Tables & Columns

Table: app_user

Column Name	Type	Notes
id	INT/UUID	Primary Key
email	VARCHAR	Unique
password	VARCHAR	Hashed
is_active	BOOLEAN
created_at	TIMESTAMP	Default: now()

Table: order_entry

Column Name	Type	Notes
id	INT/UUID	Primary Key
user_id	INT/UUID	FK → app_user(id)
total_amount	DECIMAL
status	VARCHAR	e.g., pending, paid
created_at	TIMESTAMP