11 How to play with database design _ MySQL

11 How to Design bitsCN.com

In our daily work, when we encounter massive data volumes, how can we pick out the desired data? Is it a blind search, a new solution, or a skill? Developers often follow three common forms when designing a Data Form. they think that the regular mode is the only way to design a data form. However, since developers started with this mentality, the design of data forms became strictly compliant and hindered its innovation.

The author Shivprasad has accumulated some experience in his usual work projects and introduced 11 data form creation techniques to us. this can be used as a reference when designing data forms, to enrich our application experience.

Rule 1: What is the essence of an (OLTP or OLAP) application?

When you begin to create a data table with a single time, first, analyze the nature of the program you designed? Is it transactional or analytical? You will find that many developers will apply the regular rules by default, and then consider the performance issue without considering the nature of the application.

Let's take a look at the differences between transactional and analytical features.

Transactional: this kind of application, users are more interested in CRUD, that is, creating, reading, updating and deleting records. This type of data is officially named OLTP.

Analytical: you are interested in analysis, report, and prediction. Such databases are rarely embedded and updated. The main purpose is to obtain and analyze data as soon as possible. The official name is OLAP.

In other words, if you want to focus on embedding, updating, and deleting, you can choose to design regular forms or create a simple and well-regulated data architecture.

The following is a simple chart with the name and address displayed on the left. a simple regular form is designed with an nonstandard structure.

Rule 2: Divides data into different parts based on logical thinking, making life easier

This rule is actually the first paradigm in the "three paradigm. The purpose of this design is to provide this function when you need to query a set of string parsing functions, such as substrings and charindexetc.

For example, if you want to query the name of a student, you can use "Koirala" and "Harisingh" to distinguish between the following charts.

Therefore, a better way is to break the logic of data, so that we can write more concise and easy-to-query forms.

Rule 3: When there is too much data, rule 2 is unavailable

Developers sometimes have a very simple mindset. if you tell them a way, they will continue to do so, knowing that excessive use will cause unnecessary trouble. As we mentioned earlier, rule 2 should first be decomposed to clarify its own needs. For example, when you see the phone number field, you can identify the phone numbers in the ISD code (until they meet your needs ). Although this is a good method, it will bring you more complications.

Rule 4: identifies repetitive and inconsistent data as your greatest enemy

Focus on and reconstruct data replication. I am worried about not the disk space required for data replication, but the chaos caused by it.

In the following chart, "5th Standard" and "th standard" mean the same. you can say that it is because of data or verification data input to your system, if you want to use reports to show their differences, it is very difficult for users to view them.

One solution is to join the same data in different Taskbar by creating a new input value .. You can create a new entry "Standards" to rearrange the data and display the same part.

Rule 5: pay attention to the data split by delimiters

The previous rule 2, the "first paradigm", mentioned to avoid repeated arrays ,. If you see that the syllabus is closely arranged, a lot of data is needed in this field. this is called "repeated array ". If we have to manipulate the data, it is very difficult to query it alone. I even doubt whether this query function is available.

Pay special attention to these data with delimiters. how can we use better methods to move the data to a different taskbar for better classification?

You can see that I have created an independent teaching subject entry and listed the subjects associated with it. This method is mainly applicable in the syllabus field to avoid too many duplicates and data delimiters.

Rule 6: Beware of data dependency

Observe the list of parts in this field ., We have created roll number and standard, and we can see that the teaching subjects are closely linked, but they are not directly associated with the students. If we want to update each student's teaching subject, it seems that it is not logical, but you can convert the data by typing a standard entry.

This rule tells us that "all inputs should depend on the primary key ". All keys shoshould depend on the full primary key and not partially.

Rule 7: Select a derived column

If you want to perform an OLTP application, you must first filter out the derived columns. in OLAP, we need to make some summation to obtain the good performance of uixie ., The average value must use the marks and subject columns.

This rule is called the third paradigm and "should not rely on columns with non-primary keys" (No columns shoshould depend on other non-primary key columns) I personally think this rule cannot be used blindly. If the data is computed, you can see the situation and then decide to implement the third paradigm.

Rule 8: If the performance is critical, do not avoid redundant data.

If you are eager to consider performance normalization issues, you usually need to connect to many lists and reduce the number of non-normalized lists to improve the performance of data charts.

Rule 9: complicated and data-intensive

OLAP projects are mainly used to process a large amount of data. for example, if you want to obtain the sales quota for each country, user, and year. In this case, you can create a sales fact table ).

　　

Rule 10: Design name value table list

A clear-value table indicates that it has some keys, which are associated with other data ., We need to figure out the currency table (currency column) and country table (country column). in the figure, the data we need is displayed by typing a value (number.

You can create a Type to display data in different regions.

Rule 11: unrestricted structure data, custom PK and FK

We often encounter data with an infinite parent-child hierarchical structure. For example, considering a multi-level marketing solution, one of the sales personnel can lead multiple sales personnel. In this case, you can use custom primary keys and set foreign keys to help you achieve unification.

You can select different data processing methods based on your project requirements. Three common paradigms are as follows.

From: c-sharpcorner

BitsCN.com