Table Structure Design and Creation Syntax Details

Table structure design is a core环节 in database modeling and must adhere to normalization principles to prevent data redundancy and update anomalies. When designing, consider the following points:

  • First Normal Form (1NF): Ensure that column values are indivisible; for example, store "Employee Name" as a separate column rather than nested within an "Employee Information" field.
  • Second Normal Form (2NF): On the basis of satisfying 1NF, eliminate partial dependencies of non-prime attributes on the primary key; for example, store "Department Number" as an independent column in a "Department Table".
  • Third Normal Form (3NF): Eliminate transitive dependencies of non-prime attributes on other non-prime attributes to avoid data duplication.

Table Creation Syntax

In SQLite, table creation syntax follows standard SQL specifications, with the core statement being: 

CREATE TABLE table_name (  
  column_name1 data_type constraint,  
  column_name2 data_type constraint,  
  ...  
);
  • Table Name: Must comply with naming rules, such as users or employees.
  • Column Definition: Column names must be unique; data types include integers (INTEGER), strings (TEXT), dates (DATE), booleans (BOOLEAN), etc.
  • Constraints: You can add NOT NULL (enforces that the column cannot be null), DEFAULT (default value), PRIMARY KEY (primary key), FOREIGN KEY (foreign key), and others.

Data Types and Constraints

SQLite supports multiple data types. Common types and their uses are as follows:

  • Integer Type: INTEGER (stores integers or floating-point numbers)
  • Text Type: TEXT (stores strings, supporting subtypes such as TEXT, BLOB, etc.)
  • Date Type: DATE (stores date values, such as 2023-04-15)
  • Boolean Type: BOOLEAN (stores TRUE or FALSE)

Constraints are an important means to ensure data integrity:

  • Primary Key Constraint: Defined via PRIMARY KEY, automatically ensuring uniqueness and non-null status, e.g., CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT).
  • Foreign Key Constraint: Linked via FOREIGN KEY to another table, e.g., CREATE TABLE orders (order_id INTEGER PRIMARY KEY, user_id INTEGER FOREIGN KEY REFERENCES users(id)).
  • Default Value: Specified via DEFAULT, e.g., CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT DEFAULT 'Unknown').

Example: Creating a User Table

The following example demonstrates how to create a user table containing a primary key, foreign key, and default value: 

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,  -- Auto-incrementing primary key
  name TEXT NOT NULL,                    -- Name cannot be null
  email TEXT UNIQUE,                     -- Email must be unique
  created_at DATE DEFAULT CURRENT_TIMESTAMP  -- Creation time defaults to current time
);

This table includes:

  • The id field serves as the primary key, implemented with AUTOINCREMENT for auto-incrementing.
  • The name field enforces non-null status; the email field uses a unique constraint to prevent duplicate emails.
  • The created_at field defaults to the current time, eliminating the need for manual setting.

Table Structure Optimization Suggestions

  • Avoid Over-normalization: Excessive normalization may lead to decreased query performance; constraints should be relaxed moderately in scenarios with small data volumes.
  • Use Reasonable Data Types: For instance, TEXT fields should not be excessively large; use BLOB for large text data to avoid occupying excessive storage space.
  • Index Optimization: Add indexes to frequently queried fields (such as name or email) to improve query efficiency.

Table Operation and Maintenance

After creating a table, you can operate on data using statements such as INSERT, UPDATE, and DELETE, while simultaneously filtering data with conditions using JOIN, WHERE, etc. For example: 

INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
SELECT * FROM users WHERE name = 'Alice';

With reasonable table structure design, you can ensure data consistency and integrity while improving the efficiency of subsequent operations.