MySQL Series one: Database design

Objective

The database design knowledge that was not familiar before was picked up and a simple knowledge comb was made. It was previously thought that database design was nothing more than creating a database, building a table, adding fields, determining the type of a field (which is quite random), and so on. When the database knowledge is systematically re-learned, it is found that the database design also has a set of specifications similar to the software development process, and each step has a different focus.

The concept of database design

Simply put, database design is the process of analyzing requirements, logical design, physical design, and maintenance and optimization. As can be seen, database design is not only reflected in the software development process, but also reflected in the software later maintenance. ( time period )
The analysis of software requirements here is not the same as the requirement analysis in the process of software development, the requirement analysis in database design focuses more on the data source (what data), the attributes of data and the characteristics of data and attributes.

A series of processes for database design require a combination of our existing DBMS, design tables, and tables to make limited storage and efficient access to the data.

Steps for Database design

There is one more question that is not clear, why database design? As far as I can summarize, there are the following benefits:

A good database design is useful for reducing data redundancy and operating anomalies, for limited storage of data, and for efficient access. Before the graduation design of the implementation of the experiment management system is because there is no good design of the database, resulting in the difficulty of data lookup, the fact is, you write a lot of SQL code does not necessarily get the results you want, so this is bad database design consequences. All of you little friends to lesson.

Steps for Database design

OK, so, the database design consists of four steps:

• Demand analysis
• Logic Design
• Physical design
• Maintenance and optimization

As a non-professional DBA, in line with the principle of practicality is the king, that knowledge of the surrounding can be, do not seek in-depth. So the final maintenance and optimization do not do a detailed introduction, if there are small partners interested in this piece, you can refer to the database design tutorial.

Demand analysis

Demand analysis needs to address three issues: 软件需要哪些数据 , 数据有哪些属性 as well 数据属性的特点 . First of all, the software needs the data is determined by the software business, which can be seen from the previous requirements document, the data is the database of the fields in each table, the data attributes are the elements that make up the data, a row of data in the database becomes the basic unit of data, also known as a tuple The characteristic of the data attribute is to analyze whether the data needs to be persisted, if it is, the data will persist in the database, if no, the data can not be persisted in the database (this kind of data is always time-sensitive, involving frequent read and write operations).

Logic Design

Logical design to undertake demand analysis, to solve the core problem is one: Draw e-r diagram. E-r diagram is the process of translating the results of demand analysis into logical models. The E-r diagram consists of three elements: entity set , attribute set , and contact set . The entity set is the same attribute, the set of attributes is the entity, and the contact set is made up of the relationships between the entities (the links here include a number of links, which are explained in detail later in this article). The so-called "logical design" is not related to the specific DBMS. To draw a e-r diagram, you need to know the following concepts:

• Relationship: A table in a relational database
• Entity: A database management object with the same set of properties
• Tuples: A row of data for a table
• Properties: one column per property corresponding to the database table
• Primary key: One or more attributes that can uniquely identify an entity
• Candidate Keywords: for cases where multiple attributes are required to identify an entity

Speaking of the basic concept, the following is an example of the explanation, below the project I am doing to explain:

The whole system is a student information management system, with a lot of modules, I am responsible for the module is accommodation information management, after the demand analysis, finally determined that the module has three parts of the function: Student items repair, the student inquires the electricity and water information, the stay tube registration accommodation information, property handling items repair and logistics allocation accommodation information.

Student: { School number , name, gender, contact information, dorm number}

Overnight tube: { host ID, name, Gender, contact information}

Property: { property manager ID, name, Gender, contact information}

Logistics: { logistics Manager ID, name, Gender, contact}

Accommodation Information table: {id, name, student number, gender, dorm number, Major, class number, contact info}

Dorm: { dorm ID, building No., dorm number}

Item Repair Information table: {id, item name, damage situation, repair person, contact, dorm number, repair time, urgency, whether processing}

Water information: {id, dorm number, water consumption, remaining volume of the month, remaining amount, arrears status}

Electricity information: {id, dorm number, power, remaining charge, remaining amount, arrears status}

Based on the above data, you can draw the following e-r diagram:

Because the original image is too large, only the part is shown. The following is a brief description of the content, each rectangle represents an entity, each entity has a property set, which means that the <M> field cannot be empty, that the field <pi> is the primary key of the entity, and that <ai> the field is the candidate keyword for the entity. There are various connections between entities, and the lines in the diagram represent the specific contact between entities and entities. Here's a brief talk about what this connection is about:

In the database design, there are 4 kinds of basic relations (relationship): one-to-many, single-to-many, multi-pair. For example, teachers can take many students to class, students also have more than one teacher, so the teacher and students are many-to-many links, a student can only in one class, and a class can have multiple students, so the students and the class is a many-to-one connection. Anyway, the analysis of the idea is one and many of the corresponding relationship can be established. Inheritance (Inheritance): For example, students, teachers are people, so the "student" and "teacher" between the two entities and "person" constitutes an inheritance relationship. The existence of an inheritance relationship is for a better extension later. Connections : The status between entities with connection relationships is equal, please think carefully:

Here are the relationships between developers, experts, and lectures:

It can be seen that if the two are not connected, it means the inequality of status. So the connection relationship is also very well understood. Ok,next, dependency : A dependency is an entity that cannot exist alone and must coexist with another entity to make sense. For example: doors and windows must rely on the existence of the house, there is no house, there is no need for windows and doors.

Physical design

Physical design is the core of the final database design, and also the key step of visible results. So what's the problem with physical design?

• Choosing the right DBMS
• Specify naming conventions for databases, tables, and fields
• Determines the field type of a specific field based on the selected DBMS

Select the appropriate database

Today, enterprise-class databases have Oracle and SQL Server, which have high requirements for data security and capacity. Internet projects generally use MySQL, pgsql, so choose the right database according to the type of project you want.

Prescriptive naming conventions

The naming specification needs to follow the principle of field readability and the principle of knowing the name, otherwise it is unnecessary to create a data dictionary for arbitrary field names and add extra work.

Determine the field type

As far as I am concerned, the most difficult thing to judge is the Char and Var char types, which are particularly easy to choose, so the general case is to choose a more conservative varchar type. However, as long as careful analysis found that the two types first in the scope of the expression there is a limit, char type can not exceed 255 bytes, so as long as it is not that ordinary text can generally accommodate, this is said, varchar than char save space, but varchar than char more efficient, This can be understood as follows: when modifying data of varchar type, it may be because of the difference in data length (in the case of the string "abc", the char type requires 5 bytes, and varchar requires only 3 bytes) resulting in " row Migration" , the following Oracle's official explanation of row migrations:

When a row of records is initially inserted, it can be stored in blocks (The block is the smallest unit of disk storage), and because the update operation causes the row to increase, and the block's free space is full, this time a row migration occurs. In this case, Oracle will migrate the positive row data into a new block, and Oracle will keep the original pointer of the migrated row pointing to the new block that holds the row data, which means that the migrated row ID will not change.

It's a bit complicated to say, but it can be summed up to know that the difference between varchar and char types is small, except in big data development applications.

MySQL Series one: Database design