Database Design Guidelines: Naming & Data Types

Introduction

The quality of a database is determined by two pillars: clarity (how easy it is to read) and efficiency (how well it performs).

• Naming Conventions ensure that the database is not a "black box" but a readable map for developers.

• Data Types ensure that data is stored efficiently, preventing storage bloat and calculation errors.

---

Part I: Naming Conventions

Creating a language that everyone on the team speaks.

1. The "Musts" (Technical Constraints)

Violating these rules will typically cause syntax errors or break SQL queries.

• Avoid Reserved Keywords: Never use SQL keywords (e.g., SELECT, TABLE, ORDER, USER). Quoting identifiers (e.g., "Order") is messy and error-prone.

• No Special Characters or Spaces: Use only A-Z, 0-9, and underscores (_). Never use spaces, hyphens, or emojis.

• Start with a Letter: Identifiers must begin with a letter, not a number or underscore.

• Length Limits: Keep names under 64 characters to avoid truncation by database tools.

• Uniqueness: Column names must be unique within a table; table names must be unique within a schema.

2. The "Shoulds" (Best Practices)

These are style choices, but consistency here is critical for long-term maintenance.

• Case Strategy: snake_case

  • Rule: Always use lowercase letters separated by underscores.

  • Why: Many databases (like PostgreSQL) are case-insensitive by default. Using CamelCase can force you to use double quotes around every column name in your queries.

  • Good: first_name, customer_orders

  • Bad: FirstName, CustomerOrders, first-name

• Pluralization: Consistency is Key

  • Recommendation: Use Plural for table names and Singular for column names. This matches conventions used by frameworks like Ruby on Rails and Django.

  • Example: A table named users (plural) contains a column named email (singular).

• Be Descriptive but Concise

  • Avoid over-abbreviation that obscures meaning.

  • Good: birth_date, customer_id

  • Bad: bd, cust_ident, c_id

3. Specific Object Guidelines

4. Anti-Patterns (What to Avoid)

• Hungarian Notation: Do not prefix tables with tbl_ or columns with col_. It is redundant.

  • Bad: tbl_users

  • Good: users

• Ambiguous Names: Avoid generic names like data, info, or value unless unavoidable in a generic EAV structure.

• Reusing ID Names: Don't name a primary key simply key. It is a reserved word in many SQL dialects.

---

Part II: Data Types

Choosing the right container for your data.

Using the correct data type optimizes Performance (speed of indexing/retrieval), Integrity (preventing bad data), and Storage (cost).

1. Numeric Types

• Integers (INT, BIGINT, TINYINT): Use for whole numbers.

  • Tip: Use TINYINT for small ranges (like status codes 0-255) and BIGINT for massive IDs (like Twitter snowflakes).

• Decimals (DECIMAL, NUMERIC): Use for Money.

  • Critical: Never use FLOAT for money. FLOAT is approximate and leads to rounding errors (e.g., storing $19.99 might result in 19.99000001).

• Floats (FLOAT, DOUBLE): Use for scientific calculations (e.g., latitude/longitude, physics simulations) where slight precision loss is acceptable for speed.

2. String (Text) Types

• Varchar (VARCHAR): Variable length text. The most common string type.

  • Usage: Names, emails, addresses. VARCHAR(255) is standard.

• Char (CHAR): Fixed length text.

  • Usage: Only when data is always the same length, like a Country Code (US, JP) or Currency Code (USD).

• Text (TEXT, LONGTEXT): For large bodies of text.

  • Usage: Blog posts, product descriptions, comments.

3. Date & Time Types

• Date (DATE): Stores YYYY-MM-DD. Use for birthdays or holidays.

• Timestamp/Datetime: Stores YYYY-MM-DD HH:MM:SS. Use for audit logs (created_at, updated_at).

  • Tip: Always store time in UTC (Universal Time) to avoid timezone headaches later.

4. Boolean Types

• Boolean (BOOLEAN / BIT): Stores simple True/False or 1/0 values.

  • Usage: is_published, is_verified.

---

Summary: Data Type Cheat Sheet

---

Final Checklist

Before finalizing a table, ask:

1. Naming: Is it snake_case? Is it descriptive?

2. Types: Am I using DECIMAL for money? Am I using VARCHAR for codes that look like numbers (zip/phone)?

3. Keys: Does every table have a Primary Key (usually id)?