Data Table & View Specifications

12003

Introduction to This Lesson

In this lesson, we will further explore the key points of data table structure design. In application development, there are commonly applications using a single table and applications using 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 deep 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: Applications of a Single Table and Three Tables

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: applications using a single table and applications using three tables.

Applications using a single table are the simplest, requiring only the design of a single data table to store all data, enabling data creation, deletion, modification, and querying. In contrast, applications using three tables require the design of three tables, including two entity tables and one relational table, necessitating an understanding of 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 applications using a single table, to design an appropriate data table based on business needs, factors such as 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 scaffolding to help everyone understand the data structure design for a single table application.

Read the Jianghu scaffolding documentation: 1table-crud

Naming Conventions for Table Names

The 1table-crud project in the Jianghu scaffolding 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. Thus, we created a table named "student" to store student information in this project. 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 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 belongs to
Height (bodyHeight): The height of the student

These 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 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 consuming 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 as needed. In the DECIMAL data type, the parameters represent the total length and the number of decimal places. For example:

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

Primary Key and Auto-Increment ID

After defining the fields and setting the 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, every 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 practical 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 applications using three tables, it is necessary to design two entity tables and one relational table. The two entity tables are designed based on business needs, following similar principles as 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 scaffolding to understand the data table structure design for three tables.

Read the Jianghu scaffolding documentation: 3table-crud

Entity Tables: Constructing Data IDs

The 3table-crud project in the Jianghu scaffolding is actually a slightly more complex student management system, involving student management, class management, and the management of the relationship between students and classes. Therefore, in addition to designing 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 applications using three tables, 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 data table design rules, 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 as 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 as data ID
    className VARCHAR(255)
);

Relational Table: Using Redundant Fields

Next, we will design the relational table. Typically, when designing relational tables, to enhance 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 the relationship 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, with the following field definitions:

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, used to associate 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, used to associate 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. Additionally, by optimizing the design of the relational table using redundant fields, we simplified query operations, improved the system's query efficiency, and made data processing more efficient and reliable.

## Introduction to This Lesson

In this lesson, we will further explore the key points of data table structure design. In application development, there are commonly applications using a single table and applications using 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 deep 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: Applications of a Single Table and Three Tables

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: applications using a single table and applications using three tables.

Applications using a single table are the simplest, requiring only the design of a single data table to store all data, enabling data creation, deletion, modification, and querying. In contrast, applications using three tables require the design of three tables, including two entity tables and one relational table, necessitating an understanding of 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 applications using a single table, to design an appropriate data table based on business needs, factors such as 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 scaffolding to help everyone understand the data structure design for a single table application.

> Read the Jianghu scaffolding 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 scaffolding 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. Thus, we created a table named "student" to store student information in this project. 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 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 belongs to
- Height (bodyHeight): The height of the student

These 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 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 consuming 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 as needed. In the DECIMAL data type, the parameters represent the total length and the number of decimal places. For example:

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

### Primary Key and Auto-Increment ID

After defining the fields and setting the 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, every 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 practical 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 applications using three tables, it is necessary to design two entity tables and one relational table. The two entity tables are designed based on business needs, following similar principles as 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 scaffolding to understand the data table structure design for three tables.

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

### Entity Tables: Constructing Data IDs

The 3table-crud project in the Jianghu scaffolding is actually a slightly more complex student management system, involving student management, class management, and the management of the relationship between students and classes. Therefore, in addition to designing 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 applications using three tables, 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 data table design rules, 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 as 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 as data ID
    className VARCHAR(255)
);
```

### Relational Table: Using Redundant Fields

Next, we will design the relational table. Typically, when designing relational tables, to enhance 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 the relationship 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, with the following field definitions:

- 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, used to associate 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, used to associate 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. Additionally, by optimizing the design of the relational table using redundant fields, we simplified query operations, improved the system's query efficiency, and made data processing more efficient and reliable.