Database Design for Project Summary

Recently, I have completed a complete database design for a small project. I hope you can give me some suggestions on the design.

Sometimes a project is normalProgramEmployees generally do not have access to database design. They generally have professional DBAs or old programmers to design them. The following are some possible reasons for my speculation:
1: A newbie does not know much about the project. This is exactly the strength of laruence.
2.
3: database design directly affects project complexity and performance to some extent.

First, we need to know what a paradigm is.Why do we always mention the paradigm when talking about database design. Paradigm: a set that conforms to a certain level of relational patterns. Database Design must follow certain rules. In relational databases, such rules are the paradigm.

2: Classification of paradigm. relationships in relational databases must meet certain requirements. Currently, relational databases have six paradigms: first, second, third, fourth, fifth, and sixth. The first paradigm meets the minimum requirements, and the rest are the same. So many categories do not necessarily need to be fully satisfied. We usually achieve the third paradigm at ordinary times.
third: what is the role of paradigm?
1. Advantage: it refers to the process of converting it into some tables. This method can make the results obtained from the database clearer.
2: disadvantage: duplicate data may be generated in the database, resulting in redundant tables.
3: it is a refined process after identifying the data elements and relationships in the database, and defining the required tables and projects in each table.
4: The design paradigm is the specification that must be met by the database design. Databases meeting these specifications are concise and clear in structure, and there will be no exceptions in the insert, delete, and update operations. On the other hand, it creates problems for programmers and may store a large amount of unnecessary redundant information.

The following describes the first three paradigms:
I. First paradigm. It is a basic requirement for the relational model. databases that do not meet the first paradigm are not relational databases. The first example indicates that each column in a database table is an inseparable basic data item. The same Column cannot contain multiple values, that is, an attribute in an object cannot have multiple values or duplicate attributes. If duplicate attributes exist, you may need to define a new object. A new object consists of duplicate attributes. The new object has one-to-multiple relationships with the original object. This single attribute is composed of basic types, including integer, real number, complex type, logical type, and date type. For example, if there is a table that stores files, it should be like this: You can see that this table contains several columns, if we put all the information in a column, the 1nf defined above will not be satisfied.

Create Table Regulations (
ID Int Identity,
Title nvarchar ( 200 ) Null ,
Fileaddress varchar ( 255 ) Null ,
Opendate datetime Null ,
Typeid Int Null ,
Postdate datetime Null ,
Constraint pk_regulations primary key (ID)
)

Ii. Second paradigm: built on the basis of the first paradigm. Each instance or row in the database table must be divided by a unique region. You usually need to add a column to the table to store the unique identifier of each instance. This unique attribute column is called as the primary keyword, primary key, and primary code. As shown in the preceding regulations, the ID column is an identity column ).
 

Iii. Third paradigm: a database table must not contain non-primary keywords that have already been included in other tables. For example, there is a file table regulations on it. If this table is the primary file for storage and there are n attachments, we need to create another attachment table to store the attachments. How can we associate the two tables? We can insert the primary key of the main file table along with the attachment information as a record into the attachment table. The information inserted here only contains the primary key ID, without inserting other information, this relationship satisfies the requirements of the third paradigm.

Create Table attachment (
ID Int Identity,
Fileid Int Null , // Primary Key ID of the Master File
Address varchar ( 255 ) Null ,
Title nvarchar ( 200 ) Null ,
Constraint pk_attachment primary key (ID)
)

At last, I summarized the specific applications in my project:

1. enable the data dictionary concept to improve development efficiency.What is a data dictionary? I will not mention it here. If you do not know it, you can search it online. In a project, you may encounter many options, that is, to select the content of some small data items for the form. See the figure below:

 

 

Each module has more or less such options when inserting records. If each table has a corresponding option table, the maintenance workload is quite large, all the options that can store these small data volumes to a table. The data dictionary table is as follows:

The following figure shows the data display of the data dictionary:

 

Second, I have a further understanding of setting the field data type in the database table.

1: SQL has a special data type. Unicode data types include nchar, nvarchar, and ntext. traditional non-Unicode data types allow the use of characters defined by specific character sets. The Unicode data type allows columns to store any characters defined by the Unicode standard. The Unicode Standard contains all characters defined in various character sets. The Unicode data type is used, and the occupied space is twice that of the non-Unicode data type. When the length of a column changes, the nvarchar character type should be used. When the column length is fixed, the nchar character type should be used. During form verification, users sometimes enter a mix of English and Chinese characters. For convenience of verification, you can set the fields in this case to Unicode.

2: For non-Unicode data, try to select the corresponding type. For example, the mobile phone number is generally composed of numbers and the length is basically fixed. Set it to Char (15, set email to varchar (100.

Third: How to flexibly use one-to-multiple relationships.A one-to-many relationship is very common, but it can be better if it is applied flexibly.
For example, in the figure above, there is an option for understanding pipelines. It has a one-to-many relationship with the corresponding primary table. If this option appears in different modules, do we need to create a one-to-many relationship for each primary table? I chose to create an intermediate relational table. We can associate the master table that contains the MPs queue option in all modules with this intermediate relational table, so that we only need to maintain this relational table, saving a lot of time.