📋 Rules for Designing Good Tables, Integrity Rules & Constraints in a Table

📋 Rules for Designing Good Tables, Integrity Rules & Constraints in a Table iti

🧩 Introduction

In any Relational Database Management System (RDBMS), data is stored in tables, which are the foundation of all operations such as querying, reporting, and data manipulation. But not all tables are equal — well-designed tables are essential for maintaining data integrity, minimizing redundancy, and improving efficiency.

In this lesson, you will learn about:

  • Rules for designing good tables

  • Different types of constraints

  • Importance of data integrity

  • Examples of real-world table design

📌 What is a Table in a Database?

A table is a collection of data organized in rows and columns. Each table represents a single entity (e.g., Students, Employees, Products), and each row (also called a record) represents a single instance of that entity.

Example Table: Students

StudentIDNameAgeClassMarks
101Ramesh1710th89
102Suresh1810th92

✅ Rules for Designing Good Tables

1. Atomic Columns (No Multiple Values)

Each column should hold only one value per cell. Avoid storing multiple pieces of information in a single field.

❌ Bad:

Name
Ramesh, Suresh

✅ Good:

FirstNameLastName
RameshKumar

2. Use Meaningful Field Names

Columns should be named in a way that reflects the data they contain.

❌ Bad: col1, data1
✅ Good: StudentID, FullName, DateOfBirth

3. Choose Appropriate Data Types

Always use the most suitable data type for each column.

  • Use INT for numeric IDs

  • Use VARCHAR or TEXT for names or addresses

  • Use DATE for date values

  • Use BOOLEAN for Yes/No values

4. Set a Primary Key

Every table should have a Primary Key, which uniquely identifies each record. It should be:

  • Unique

  • Not NULL

  • Unchanging

Example: StudentID in a Students table

5. Avoid Redundant Data (Normalization)

Data should not be duplicated in multiple places. Use Normalization to break data into related tables.

✅ Use separate tables for:

  • Students

  • Courses

  • Marks

Then link them with foreign keys.

6. Ensure Consistent Data (Integrity Rules)

Tables should be designed to prevent incorrect or inconsistent data.

Examples:

  • A student's age should never be negative

  • A date of birth should be a valid date

  • A course name should not be blank

7. Use Indexes Wisely

Add indexes to columns that are often used in WHERE or JOIN conditions. This improves performance.

8. Avoid Nulls Where Not Needed

Try to set fields as NOT NULL where values are always expected (e.g., name, date of birth). Avoid null values unless required.

🧱 Database Integrity Rules

Data Integrity ensures that the data in the database is accurate, consistent, and reliable.

🔹 1. Entity Integrity

  • Each table must have a Primary Key

  • No part of a Primary Key should be NULL

✅ Ensures that every row can be uniquely identified.

🔹 2. Referential Integrity

  • Ensures that relationships between tables remain consistent.

  • A Foreign Key in one table must refer to a valid Primary Key in another.

Example:
In a Marks table, the StudentID must exist in the Students table.

🔹 3. Domain Integrity

  • Ensures that data entered into a column is valid.

  • Enforced using data types, constraints, and default values

Examples:

  • Age should be between 0 and 120

  • Gender should be either ‘M’ or ‘F’

  • Email should be in correct format

🔹 4. User-Defined Integrity

  • Custom business rules specific to the organization.

  • Example: A student cannot be enrolled in more than 5 courses.

🔐 Constraints in a Table

Constraints are rules applied to the columns of a table to enforce data integrity.

1. PRIMARY KEY Constraint

  • Uniquely identifies each record in a table.

  • Cannot contain NULL values.

sql
CREATE TABLE Students (  StudentID INT PRIMARY KEY,  Name VARCHAR(50) );

2. FOREIGN KEY Constraint

  • Links two tables together.

  • Enforces referential integrity.

sql
CREATE TABLE Marks (  MarkID INT,  StudentID INT,  FOREIGN KEY (StudentID) REFERENCES Students(StudentID) );

3. NOT NULL Constraint

  • Ensures that a column cannot have a NULL value.

sql
Name VARCHAR(100) NOT NULL 

4. UNIQUE Constraint

  • Ensures all values in a column are different.

sql
Email VARCHAR(100) UNIQUE 

5. CHECK Constraint

  • Validates the values entered into a column.

sql
Age INT CHECK (Age >= 5 AND Age <= 25)

6. DEFAULT Constraint

  • Assigns a default value if no value is specified.

sql
Status VARCHAR(10) DEFAULT 'Active' 

📊 Example: Table with Multiple Constraints

sql
CREATE TABLE Students (  StudentID INT PRIMARY KEY,  Name VARCHAR(100) NOT NULL,  Age INT CHECK (Age >= 5 AND Age <= 25),  Email VARCHAR(100) UNIQUE,  Gender CHAR(1) CHECK (Gender IN ('M', 'F')),  EnrollDate DATE DEFAULT CURRENT_DATE );

🧠 Real-World Examples

🏫 School Management System:

  • Students, Teachers, Subjects, Timetable as tables

  • Primary Keys to identify students, teachers

  • Foreign Keys to link marks with students and subjects

🏢 Employee Database:

  • Tables for Employees, Departments, Projects

  • Use constraints to prevent invalid salaries, duplicate employee IDs

📌 Summary

ConceptDescription
TableBasic structure to store data in rows and columns
Good DesignUse atomic fields, meaningful names, primary keys
IntegrityEnsures accuracy and consistency in the database
ConstraintsRules like PRIMARY KEY, NOT NULL, CHECK, etc.

🏁 Conclusion

Designing a good database table is much more than just creating rows and columns. By following best practices, using appropriate constraints, and enforcing integrity rules, you ensure that your data remains reliable, accurate, and efficient for long-term use.

This knowledge is fundamental for COPA students to build strong database-driven applications, manage office records, and understand real-world systems.