SQL VS NoSQL How to select a database

In the previous article, we discussed the main differences between SQL and NoSQL databases. Next, we'll use the knowledge in the previous article to determine how to determine the better choice in a particular scenario.

First, let's summarize:

SQL database:

• < Span class= "Md-expand"? Use tables to store related data

• You need to define the underlying schema before using the table

• encourage use of normalization to reduce data redundancy

• supports the use of Jion operations to remove related data from multiple tables using a single SQL statement

• need to meet data integrity constraint rules

• use transactions to ensure data consistency

• can be used on a large scale

• querying with a powerful SQL language

• provides a lot of support, expertise and accessibility

NoSQL database:

• Use a document in class JOSN format to store key value pair information

• Storing data does not require a specific pattern

• Use non-normalized standard storage information to guarantee that a document contains all the information for an entry

• No need to use jion operation

• Allows data to be stored in any location without validation

• Guaranteed update of individual documents, not multiple documents

• Delivers superior performance and scalability

• Querying using the Josn data object

• is a new kind of technology

SQL database is suitable for those projects where the requirements are determined and the integrity of the data is strict. NoSQL databases are suitable for those unrelated, uncertain, and evolving needs that are valued for speed and scalability. In simple terms:

• SQL is accurate. It is best suited to well-defined projects with precise criteria. Typical usage scenarios are online stores and banking systems.

• NoSQL is changeable. It is best suited for data with uncertain requirements. Typical usage scenarios are social networks, customer management, and network analysis systems.

Few projects are well suited to a database. If your data needs are small or non-standard data, any kind of database is possible. You know your project better than I do, and I don't recommend porting data on SQL to NoSQL or vice versa unless it provides a very substantial benefit. Of course the choice is yours. The pros and cons of using them should be considered at the beginning of the project so that they do not lead to selection errors.

Scenario One: Communication Records

Let's redefine the operation and implement the Address book system based on SQL. Our initial simple definition contact table has the following fields;

• Id

• Title

• FirstName

• LastName

• Gender

• Telephone

• Email

• Address1

• Address2

• Address3

• City

• Region

• ZipCode

• Country

Question one: few people have only one mobile phone number. Maybe we can add three more fields: landline, mobile, and work, but no matter how many fields we assign to a contact, someone needs more. We need to create a separate telephone table so that you can save multiple numbers for a contact. This also standardizes our data-we don't need a contact who doesn't have a phone:

• contact_id

• Name (Description field: fixed, work, private, etc.)

• Number

question two: We also encounter these issues with the contact mailbox, so we also need to create a email table:

• contact_id

• Name (Description field: fixed, work, private, etc.)

• Address

question three: We are filling in the contact information is we do not want to fill in his geographical location, or we want to record his work, life, travel and other places. So we need a address table:

• contact_id

• Name (text such as home, office, etc.)

• Address1

• Address2

• Address3

• City

• Region

• ZipCode

• Country

The table we originally designed contact was streamlined to:

• Id

• Title

• FirstName

• LastName

• Gender

Well, we've designed a normalized database that can be used to store any contact's phone number, email address, and address. But......

The pattern is fixed and unchanging

We did not take into account the contact's birthday, company or position. No matter how many fields we add, we'll get more needs: notes, Anniversaries, relationships, social accounts, types of chocolates, etc. Predicting each requirement is very difficult for us, so we need to create a table where the stored form is a key-value pair to cope with the increasing demand.

The data is fragmented.

It is cumbersome for developers and system administrators to continually check the database. The logic of the program becomes more complex and more efficient, because it select is less practical to use a single statement to fetch JOIN All the information for a contact with multiple tables. (This can be done, of course, but when a contact China contains a phone number, email address and address information: If a person has 3 phone numbers, five mailboxes, and two addresses, then the SQL query produces 30 results, which means that it can be slow. )

Finally, full-text search is very difficult. If a person wants to query a character type: favorite, we need to query the above four tables in turn to determine whether the name of a contact, telephone, mail or address according to these results are sorted. If you have used WordPress The search, you will find that it is annoying!

using NoSQL to replace SQL

our contact is concerned with the human entity. However, information about people is unpredictable and needs vary in different stages. If we use a NoSQL database it would be convenient for NoSQL to store a person's information as a document and put it contacts in a collection:

{name: ["Billy", "Bob", "Jones."], Company:"Fake Goods Corp", JobTitle:"Vice President of Data Management", Telephone: {home:"0123456789", Mobile:"9876543210", work:"2244668800"}, Email: {Personal:"[Email protected]", work:"[Email protected]"}, Address: {home: {line1:"Ten Non-existent Street", City:"Nowhere", Country:"Australia"}}, Birthdate:isodate ("1980-01-01T00:00:00.000Z"), Twitter:' @bobsfakeaccount ', note:"Don ' t trust this guy", Weight:"200lb", Photo:"52e86ad749e0b817d25c8892.jpg"}

In this example, we do not store the person's gender and title, and can add some useless information to other contacts. This kind of storage is legal in NoSQL, and we can add and delete a field according to the wishes of everyone.

Because a contact is stored in a document, we can use a query statement to retrieve all the information for a person. For full-text search, in MongoDB we can contact define an index in the field:

Db.contact.createIndex ({"$* *": "text"});

Then we can use the following statement for full-text search:

Db.contact.find ({  $text: {$search: "Something"}});

Scenario Two: Social networks

A typical social network uses the same information as the contacts, but it also adds new features such as social networking, status updates, private messages, and likes. These features are added or deleted according to the user's needs, and predicting the user's needs can be very difficult for developers.

Other than that:

• Most updates have an original starting point: the user. However, it is not possible for developers to update all of their feedback at once.

• No matter what the user thinks, a failed version can cause too much economic damage. The interface design and functional performance of an application is higher than the priority of data integrity.

Therefore, NoSQL is a good choice. The database allows us to store different types of data to enable us to quickly develop new features. For example, because all updates to the state of the data can be status made in a document in the collection:

{  User_id:objectid ("65f82bda42e7b8c76f5c1969"),  update: [    {      date:isodate (" 2015-09-18t10:02:47.620z "),      " feeling more positive Today "    },    {      date:isodate (" 2015-09-17t13:14:20.789z "),      " spending far too much time here "    }    {      date:isodate ( "2015-09-17t12:33:02.132z"),      "Considering my life choices"    }  ]}

Although the data can be a bit more for this document, we can just take a subset of the document, such as the latest state, and so on. For each user, the history is also easily searchable.

Scenario Three: Warehouse Management system

Now, let's analyze a warehouse management system. We need to record the following information:

• Information on the storage of goods and location of storage

• The movement of goods in warehouses, for example: The distribution of adjacent locations for the same goods

• The order in which the goods are placed and the series of questions after delivery.

Data requirements:

1. Basic information such as size, size, color, etc. that are not related to the storage of the goods, we need to be able to use any of the goods. We can't think about some of the goods. Personalized information such as the speed of a laptop processor or the lifespan of a cell phone battery.

2. We should try our best to avoid the mistakes that occur. We cannot let the goods disappear or store the goods in the place where the goods have been deposited.

3. Operate in a much simpler way. We record the same action of moving a shipment from one location to another or moving from A to B.

Therefore, we need to consider the integrity of the data and support for the transaction. For now, SQL is a great way to meet our needs.

Summary

I hope that the above cases can help you, but each actual project is different, for a no two you need to make your own decision to choose a suitable. (although, for our developers, we are less willing to change our existing choices, no matter how good the new technology is!) O (∩_∩) o)

Suggestion: Try to learn more about the new technology. This allows us to choose a database for development with great reason. Good luck!

SQL VS NoSQL How to select a database