Common Applications Data Table Structure Design

12002

Course Introduction

In this course, we will further explore the key points of data table structure design. In application development, there are commonly applications that use a single table and those that use three tables. We will first learn how to design a data table in application development, including how to name tables, define fields, choose appropriate data types, and set primary keys to ensure data integrity and accuracy. We will also introduce the application of three tables, learning how to design relational tables, including the construction of data IDs and the use of redundant fields, to establish correct associations between multiple tables for effective data management and querying. Through this course, you will gain a deeper understanding of the key concepts of data table structure design and master practical skills for designing and managing data tables in application development, thereby enhancing your data management and application development capabilities.

1. Understanding the Relationships Between Data Tables: Single Table Applications and Three Table Applications

In application development, to design a good data table structure, it is essential to understand the relationships between data tables. Based on the differences in application types, we categorize applications into two main types: single table applications and three table applications.

A single table application is the simplest, requiring only the design of one data table to store all data, enabling data creation, deletion, modification, and querying; whereas in a three table application, three tables need to be designed, including two entity tables and one relational table. This requires understanding the relationships between data tables and decomposing data into different tables based on business needs for better data management.

2. Data Table Structure Design for a Single Table

In a single table application, to design an appropriate data table based on business needs, factors such as table naming conventions, field definitions, selection of suitable data types, and setting primary keys and auto-increment IDs must be considered. The goal of these steps is to ensure that the data table structure can accurately store the required data and meet application development needs.

We will refer to the 1table-crud project in the Jianghu framework to help everyone understand the data structure design of a single table application.

Read the Jianghu framework documentation: 1table-crud

Naming Conventions for Table Names

The 1table-crud project in the Jianghu framework is essentially a simple student information management system used to store and display all information about students. Therefore, when designing the data table, we need to consider whether the table name can clearly reflect the content and purpose of the data table to facilitate developers' understanding and maintenance of the database structure. In this project, we created a table named "student" to store student information. Such naming conventions help improve the readability and maintainability of the database structure.

Defining Fields: Field Names

Once the data table name is determined, we can define the fields in the table. Based on business needs, the content that needs to be stored in the table includes: student ID, name, age, gender, grade, class, etc. We can define them as follows:

Student ID (studentId): A unique identification number for each student
Name (studentName): The name of the student
Age (age): The age of the student
Gender (gender): The gender of the student, which can be male or female
Grade (level): The grade the student is in
Class (classId): The class the student is in
Height (bodyHeight): The height of the student

The defined field names are clear and understandable, accurately reflecting the information we need to store. If business needs change in the future, we can adjust the fields at any time.

Using Common Data Types (int, decimal, varchar)

When defining fields, it is necessary to choose appropriate data types and lengths to store data. Generally, it is recommended to use string type (VARCHAR) with a length of 255. The VARCHAR type allows for the storage of variable-length string data, accommodating different lengths of text data, which can generally meet the storage needs of various fields; specifying a length of 255 can accommodate most common string data without taking up excessive storage space. For example:

Student ID (studentId): String type (VARCHAR), length 255;
Name (studentName): String type (VARCHAR), length 255;
Gender (gender): String type (VARCHAR), length 255;
Grade (level): String type (VARCHAR), length 255;
Class (classId): String type (VARCHAR), length 255;
Height (bodyHeight): String type (VARCHAR), length 255;

If the data type to be stored is an integer, it is recommended to use integer type (INT) with a length of 11, which can also be adjusted based on actual conditions. For example:

Age (age): Integer type (INT), length 11;

If the data to be stored has a decimal part, decimal type (DECIMAL) can be used, with the decimal places adjusted according to actual needs. In the DECIMAL data type, the parameters represent total length and the number of decimal places. For example:

Height (bodyHeight): (DECIMAL(4,1));

Primary Key and Auto-Increment ID

After completing the definition of fields and setting data types, the next important step is to set the primary key. We generally use an auto-increment ID to generate the primary key. In the student table "student," we add a field:

Auto-increment ID (id): Integer type (INT), length 11;

This field is set as the primary key and configured to auto-increment. Thus, each time a new student record is inserted, the database will automatically assign a unique auto-increment ID to that student, ensuring that each student record has a unique identifier. It is important to note that the auto-increment ID has no actual significance for business data; it is mainly used for internal database management and optimizing query performance.

In summary, we can use SQL statements to create a student table:

-- Student Table
CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    studentId VARCHAR(255),
    studentName VARCHAR(255),
    age INT(11),
    gender VARCHAR(255),
    level VARCHAR(255),
    classId VARCHAR(255),
    bodyHeight DECIMAL(4,1)
);

3. Data Table Structure Design for Three Tables

In a three table application, two entity tables and one relational table need to be designed. The two entity tables are designed based on business needs, following similar principles to designing a single table. The difference lies in the design of the relational table, which requires consideration of how to construct data IDs and use redundant fields. Next, we will refer to the 3table-crud project in the Jianghu framework to understand the data table structure design for three tables.

Read the Jianghu framework documentation: 3table-crud

Entity Tables: Construction of Data IDs

The 3table-crud project in the Jianghu framework is actually a slightly more complex student management system, involving student management, class management, and the management of relationships between students and classes. Therefore, in addition to the design of the student table and class table, we also need to design a relational table to store the associations between students and classes.

First, let’s look at the design of these two entity tables. In a three table application, to distinguish different entities in business data, we typically construct data IDs. For example, in the student table and class table, in addition to the auto-increment ID, we set data IDs for both students and classes to ensure that each student and class has a unique identifier. This data ID is similar to an ID number for each entity, helping the system accurately identify and manage different data records.

According to the design rules for data tables, we can design the entity tables as follows:

Student Table

-- Student Table
CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    studentId VARCHAR(255), // studentId is the data ID
    studentName VARCHAR(255),
    age INT(11),
    gender VARCHAR(255),
    level VARCHAR(255),
    classId VARCHAR(255),
    bodyHeight DECIMAL(4,1)
);

Class Table

CREATE TABLE class (
    id INT PRIMARY KEY AUTO_INCREMENT,
    classId VARCHAR(255), // classId is the data ID
    className VARCHAR(255)
);

Relational Table: Using Redundant Fields

Next, we will design the relational table. Typically, when designing a relational table, to improve data query efficiency, we can consider using redundant fields to optimize the design of the relational table and reduce associations between tables.

First, we need to determine which fields in the entity tables can establish relationships between students and classes and use these fields as redundant fields in the relational table:

Student Table: Contains fields such as studentId, studentName, age, gender, etc.
Class Table: Contains fields such as classId, className, etc.

Clearly, we want to be able to directly query all student information under a specific class, so we can choose to store studentId and classId as redundant fields in the relational table. This design simplifies query operations and improves the system's query efficiency.

We can name this table "student_class," indicating that it is a relational table storing the association information between students and classes. The field definitions are as follows:

Auto-increment ID (id): The primary key of the relational table, used to uniquely identify each association; Integer type (INT), length 11;
Student ID (studentId): Student ID, stored as a redundant field in the relational table to associate with student information in the student table; String type (VARCHAR), length 255;
Class ID (classId): Class ID, also stored as a redundant field in the relational table to associate with class information in the class table; String type (VARCHAR), length 255;
Join Date (joinAt): The date when the student joined the class; String type (VARCHAR), length 255;

Thus, when a student joins a class, a record will be added to the relational table. In this design, each student can only join one class, ensuring the simplicity and effectiveness of student information in class management.

In summary, we can use SQL statements to create a student_class table:

CREATE TABLE student_class (
    id INT PRIMARY KEY AUTO_INCREMENT,
    studentId VARCHAR(255),
    classId VARCHAR(255),
    joinAt VARCHAR(255)
);

By appropriately constructing data IDs and using redundant fields to design the relational table, we have established a complete student management system. The design of data IDs needs to consider the relationships between different tables to ensure that each record can be accurately identified, thereby supporting the normal operation and data manipulation of the system. At the same time, by optimizing the design of the relational table using redundant fields, we simplified query operations and improved the system's query efficiency, making data processing more efficient and reliable.

## Course Introduction

In this course, we will further explore the key points of data table structure design. In application development, there are commonly applications that use a single table and those that use three tables. We will first learn how to design a data table in application development, including how to name tables, define fields, choose appropriate data types, and set primary keys to ensure data integrity and accuracy. We will also introduce the application of three tables, learning how to design relational tables, including the construction of data IDs and the use of redundant fields, to establish correct associations between multiple tables for effective data management and querying. Through this course, you will gain a deeper understanding of the key concepts of data table structure design and master practical skills for designing and managing data tables in application development, thereby enhancing your data management and application development capabilities.

## 1. Understanding the Relationships Between Data Tables: Single Table Applications and Three Table Applications

In application development, to design a good data table structure, it is essential to understand the relationships between data tables. Based on the differences in application types, we categorize applications into two main types: single table applications and three table applications.

A single table application is the simplest, requiring only the design of one data table to store all data, enabling data creation, deletion, modification, and querying; whereas in a three table application, three tables need to be designed, including two entity tables and one relational table. This requires understanding the relationships between data tables and decomposing data into different tables based on business needs for better data management.

## 2. Data Table Structure Design for a Single Table

In a single table application, to design an appropriate data table based on business needs, factors such as table naming conventions, field definitions, selection of suitable data types, and setting primary keys and auto-increment IDs must be considered. The goal of these steps is to ensure that the data table structure can accurately store the required data and meet application development needs.

We will refer to the 1table-crud project in the Jianghu framework to help everyone understand the data structure design of a single table application.

> Read the Jianghu framework documentation: [1table-crud](https://www.openjianghu.org/doc/page/article/11356 "1table-crud")

### Naming Conventions for Table Names

The 1table-crud project in the Jianghu framework is essentially a simple student information management system used to store and display all information about students. Therefore, when designing the data table, we need to consider whether the table name can clearly reflect the content and purpose of the data table to facilitate developers' understanding and maintenance of the database structure. In this project, we created a table named "student" to store student information. Such naming conventions help improve the readability and maintainability of the database structure.

### Defining Fields: Field Names

Once the data table name is determined, we can define the fields in the table. Based on business needs, the content that needs to be stored in the table includes: student ID, name, age, gender, grade, class, etc. We can define them as follows:

- Student ID (studentId): A unique identification number for each student  
- Name (studentName): The name of the student  
- Age (age): The age of the student  
- Gender (gender): The gender of the student, which can be male or female  
- Grade (level): The grade the student is in  
- Class (classId): The class the student is in  
- Height (bodyHeight): The height of the student

The defined field names are clear and understandable, accurately reflecting the information we need to store. If business needs change in the future, we can adjust the fields at any time.

### Using Common Data Types (int, decimal, varchar)

When defining fields, it is necessary to choose appropriate data types and lengths to store data. Generally, it is recommended to use string type (VARCHAR) with a length of 255. The VARCHAR type allows for the storage of variable-length string data, accommodating different lengths of text data, which can generally meet the storage needs of various fields; specifying a length of 255 can accommodate most common string data without taking up excessive storage space. For example:

- Student ID (studentId): String type (VARCHAR), length 255;  
- Name (studentName): String type (VARCHAR), length 255;  
- Gender (gender): String type (VARCHAR), length 255;  
- Grade (level): String type (VARCHAR), length 255;  
- Class (classId): String type (VARCHAR), length 255;  
- Height (bodyHeight): String type (VARCHAR), length 255;

If the data type to be stored is an integer, it is recommended to use integer type (INT) with a length of 11, which can also be adjusted based on actual conditions. For example:

- Age (age): Integer type (INT), length 11;

If the data to be stored has a decimal part, decimal type (DECIMAL) can be used, with the decimal places adjusted according to actual needs. In the DECIMAL data type, the parameters represent total length and the number of decimal places. For example:

- Height (bodyHeight): (DECIMAL(4,1));

### Primary Key and Auto-Increment ID

After completing the definition of fields and setting data types, the next important step is to set the primary key. We generally use an auto-increment ID to generate the primary key. In the student table "student," we add a field:

- Auto-increment ID (id): Integer type (INT), length 11;

This field is set as the primary key and configured to auto-increment. Thus, each time a new student record is inserted, the database will automatically assign a unique auto-increment ID to that student, ensuring that each student record has a unique identifier. It is important to note that the auto-increment ID has no actual significance for business data; it is mainly used for internal database management and optimizing query performance.

In summary, we can use SQL statements to create a student table:

```
-- Student Table
CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    studentId VARCHAR(255),
    studentName VARCHAR(255),
    age INT(11),
    gender VARCHAR(255),
    level VARCHAR(255),
    classId VARCHAR(255),
    bodyHeight DECIMAL(4,1)
);
```

## 3. Data Table Structure Design for Three Tables

In a three table application, two entity tables and one relational table need to be designed. The two entity tables are designed based on business needs, following similar principles to designing a single table. The difference lies in the design of the relational table, which requires consideration of how to construct data IDs and use redundant fields. Next, we will refer to the 3table-crud project in the Jianghu framework to understand the data table structure design for three tables.

> Read the Jianghu framework documentation: [3table-crud](https://www.openjianghu.org/doc/page/article/11358 "3table-crud")

### Entity Tables: Construction of Data IDs

The 3table-crud project in the Jianghu framework is actually a slightly more complex student management system, involving student management, class management, and the management of relationships between students and classes. Therefore, in addition to the design of the student table and class table, we also need to design a relational table to store the associations between students and classes.

First, let’s look at the design of these two entity tables. In a three table application, to distinguish different entities in business data, we typically construct data IDs. For example, in the student table and class table, in addition to the auto-increment ID, we set data IDs for both students and classes to ensure that each student and class has a unique identifier. This data ID is similar to an ID number for each entity, helping the system accurately identify and manage different data records.

According to the design rules for data tables, we can design the entity tables as follows:

- Student Table

```
-- Student Table
CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    studentId VARCHAR(255), // studentId is the data ID
    studentName VARCHAR(255),
    age INT(11),
    gender VARCHAR(255),
    level VARCHAR(255),
    classId VARCHAR(255),
    bodyHeight DECIMAL(4,1)
);
```

- Class Table

```
CREATE TABLE class (
    id INT PRIMARY KEY AUTO_INCREMENT,
    classId VARCHAR(255), // classId is the data ID
    className VARCHAR(255)
);
```

### Relational Table: Using Redundant Fields

Next, we will design the relational table. Typically, when designing a relational table, to improve data query efficiency, we can consider using redundant fields to optimize the design of the relational table and reduce associations between tables.

First, we need to determine which fields in the entity tables can establish relationships between students and classes and use these fields as redundant fields in the relational table:

- Student Table: Contains fields such as studentId, studentName, age, gender, etc.  
- Class Table: Contains fields such as classId, className, etc.

Clearly, we want to be able to directly query all student information under a specific class, so we can choose to store studentId and classId as redundant fields in the relational table. This design simplifies query operations and improves the system's query efficiency.

We can name this table "student_class," indicating that it is a relational table storing the association information between students and classes. The field definitions are as follows:

- Auto-increment ID (id): The primary key of the relational table, used to uniquely identify each association; Integer type (INT), length 11;  
- Student ID (studentId): Student ID, stored as a redundant field in the relational table to associate with student information in the student table; String type (VARCHAR), length 255;  
- Class ID (classId): Class ID, also stored as a redundant field in the relational table to associate with class information in the class table; String type (VARCHAR), length 255;  
- Join Date (joinAt): The date when the student joined the class; String type (VARCHAR), length 255;

Thus, when a student joins a class, a record will be added to the relational table. In this design, each student can only join one class, ensuring the simplicity and effectiveness of student information in class management.

In summary, we can use SQL statements to create a student_class table:

```
CREATE TABLE student_class (
    id INT PRIMARY KEY AUTO_INCREMENT,
    studentId VARCHAR(255),
    classId VARCHAR(255),
    joinAt VARCHAR(255)
);
```

By appropriately constructing data IDs and using redundant fields to design the relational table, we have established a complete student management system. The design of data IDs needs to consider the relationships between different tables to ensure that each record can be accurately identified, thereby supporting the normal operation and data manipulation of the system. At the same time, by optimizing the design of the relational table using redundant fields, we simplified query operations and improved the system's query efficiency, making data processing more efficient and reliable.