Database design step by step (1)

Introduction: I have been engaged in database development and design, and I have read some books. A long time ago, I wanted to sort out and summarize the relational database design. However, for various reasons, I was too late to start the work, mainly due to inertia. Today, I am determined to start this amazing and exciting job. This will be a seriesArticleI will discuss it in a speech (I am lazy, and I will use the summary here to train new people in the company ).

 

First, let's answer the following questions:

Database is the foundation of the building

MajorityProgramThe staff are eager to quickly enter the encoding stage after understanding the basic requirements (maybe only the outputCodeCan reflect the workload), less thinking about database design.

This leaves many potential risks to the system. Many software system problems, such as incorrect output data, poor performance or complicated maintenance at the later stage, are closely related to the preliminary database design. At this point, the attempt to modify the database design or optimize the database is equivalent to overthrowing it.

I often compare software development to automobile manufacturing. Automobile Manufacturing will go through the steps of drawing design, model making, sample vehicle manufacturing, small batch production, and finally batch production. The entire process is interlocking, And the next process is based on the premise that the previous process is correct. If there is a leak in the drawing design stage, we can re-design the drawings. If this error is found in the model manufacturing stage, we should re-start from the drawing design stage to the model manufacturing stage, the more problems with the design are found later, the more expensive the modification is.

Databases are the foundation of the entire application. Without a solid foundation, the entire application is in danger.

Powerful databases are powerless in the face of poor Designs

The modern database management system (DBMS) provides convenient graphical interface tools that allow you to easily create tables and define columns. But can we design a structure?

Relational databases have many very good features, but improper design will make these features partially or completely lost.

Let's take a look at the following scenarios caused by poor Database Design:

1. Loss of Data Consistency

An order management system maintains the order information of customers and customers. After a user who uses the system receives a call from the customer to modify the Receiving address, the user modifies the Receiving address on the customer information page of the system, however, the original order of the customer still sent the wrong address.

2. Loss of data integrity

The company's strategic transfer is ready to withdraw from a region. The system operator deletes the configuration information of the region in the system. The system prompts that the region is deleted successfully. Then the problem came. The customer service staff found that the historical order page in the region encountered an error as soon as it was opened.

3. Performance Loss

In an inventory management system, the Warehouse administrator uses this system to record each incoming and outgoing goods and view the inventory of each current goods. After the system was running for a few months, the repository administrator found that opening the current inventory page was very slow and the overall trend was getting slower and slower.

The above scenarios are caused by improper database design. The root causes include: redundant fields are introduced during design, no reasonable design constraints, and no adequate performance design, the above example is just a drop in the ocean.

 

Database platform independence

The database design I discussed in this blog series does not target any relational database product. Whether you are using Oracle, SQL Server, Sybase, or open-source databases such as MySQL or SQLite, you can use them to practice the design methods and concepts discussed here, design is the core and soul of this series of blog posts.

Note: In this article, I will use a database product for demonstration. You can use a database product you are familiar with to experiment. At the end of this article, we will provide links to some free database products for you to download and learn.

Learning and making progress together

Whether you are a Database Designer, application architect, software engineer, Database Administrator (DBA), software project manager, software testing engineer, and other project team members, you can get some from this series of blog posts. Let's discuss and make progress together.

Fields involved

My current dream of this series of blog posts is to involve the whole process of database design. From Requirement Analysis to database modeling (conceptual data modeling), perform normalization until it is converted to SQL statements.

 

Before we design a database, let's take a look at the data storage methods in addition to relational databases.

Flat file)

Package objects ending with. txt and. ini.

Eg: content of A. ini file:

------------------------------------------------------------

[Websites]

Myblog = http://www.cnblogs.com/DBFocus

 

[Directorys]

Image = E: \ dbfocus project \ img

TEXT = E: \ dbfocus project \ Documents ents

Data = E: \ dbfocus project \ DB

------------------------------------------------------------

Advantages:

The storage format of files is very simple, and normal editors can open and modify files.

Disadvantages:

Unable to support complex queries

No verification function

The insert and delete operations on the content in the middle of a flat file actually generate a new file.

Applicable scenarios:

Stores a small amount of data that is not frequently modified, such as application configuration information.

Windows Registry

Incorrect modification to the Windows registry may cause system disorder. Therefore, we do not recommend that you store a large amount of data in the registry.

The Windows registry is a tree structure that stores system configuration information and application configuration information.

By storing different configurations on different branches of the registry, the public configuration information of the application is separated from the personal configuration information of the user.

Eg: a document version management system that can be configured to be associated with the file comparator installed on the master machine for document comparison. This is a public configuration. The file comparator path can be stored in the HKEY_LOCAL_MACHINE \ SOFTWARE branch of the Registry.

At the same time, the document version management system can record the 10 most recently opened document paths. This is the user's personal configuration information, which can be different for 10 recently opened documents of different Windows users. These configuration information can be stored in the HKEY_CURRENT_USER \ Software branch of the Registry.

Excel form (spreadsheets)

Advantages:

Excel is very popular and users are very familiar with spreadsheet expressions

Simple statistics are available to facilitate the generation of various charts

Disadvantages:

It is not applicable to many cases where the relationship between spreadsheet is complex.

Unable to cope with complex queries

Weak data verification

Applicable scenarios:

The data volume is not a very large office automation environment

XML

XML is a semi-structured data. Compared with Hypertext Markup Language (HTML), its tags can be customized and can be expanded.

Eg: content of an XML file

-----------------------------------------------------

<? XML version = "1.0" encoding = "UTF-8"?>

<Classschedule>

<Class name = "Psychology" room = "Field 3">

<Instructor> Richard storm </instructor>

<Students>

<Student>

<Firstname> Ben </firstname>

<Lastname> breaker </lastname>

</Student>

<Student>

<Firstname> Carol </firstname>

<Lastname> enflame </lastname>

<Nickname> candy </nichname>

</Students>

</Class>

</Classschedule>

-----------------------------------------------------

XML files have several features.

First, XML labels must be strictly matched and cannot be staggered.

Second, the XML file must have a root node that contains all other elements.

Third, different nodes at the same level do not have to contain the same elements. In the preceding example, Carol, the second student, has a special node named nickname. This feature makes XML more responsive to changes than relational databases in some scenarios.

Advantages:

Natural hierarchical structure

The text content is self-interpreted by tags.

The XML schema can be verified through XSD (XML schema language ).

There are many auxiliary technologies such as XPath, XQuery, XSL, and XSLT.

Some commercial databases (such as Oracle and SQL Server) support the storage and operation of XML data.

Disadvantages:

Data redundancy information is large.

Unable to support complex queries

Limited verification functions

The insert and delete operations on the content in XML are actually re-generating a new file.

Applicable scenarios:

Suitable for storing data with a small amount of data and hierarchical structures, such as tree configuration information

Nosql Database

I am not familiar with many non-relational databases, but do not provide many product names. There are some articles in the garden. This article also provides links for you to learn and study.

1. Key-value Database

Redis, Tokyo cabinet, flare

2. Document-oriented database

MongoDB, couchdb

3. Distributed Computing-oriented databases

Cassandra, Voldemort

Nosql has been very popular in recent years. I don't think nosql is a silver bullet. In some SNS application scenarios, nosql shows its superiority, however, in the financial industry and other scenarios with high data consistency, integrity, availability, and transactional requirements, nosql is not necessarily applicable now. We should fully analyze the application requirements and carefully select technologies and products.

Overview

1. Key Role of Database Design for successful software projects

2. This course has nothing to do with database products. The core is the design concept and method.

3. Various Data Storage scenarios

References

1. Oracle Database 10 GB Express Edition

2.SQL Server2008 r2Express-Overview

3.SQLiteHome Page

4.NosqlDatabase pen talk

Introduction: I have been engaged in database development and design, and I have read some books. A long time ago, I wanted to sort out and summarize the relational database design. However, for various reasons, I was too late to start the work, mainly due to inertia. Today, I am determined to start this amazing and exciting job. This will be a series of articles, and I will discuss it in a speech-based manner (for personal laziness, the summary here will be taken directly to the company to train new people ).

 

First, let's answer the following questions:

Database is the foundation of the building

Most programmers are eager to quickly enter the encoding stage after learning about the basic requirements (only the code can be output to reflect the workload), and have little thought about database design.

This leaves many potential risks to the system. Many software system problems, such as incorrect output data, poor performance or complicated maintenance at the later stage, are closely related to the preliminary database design. At this point, the attempt to modify the database design or optimize the database is equivalent to overthrowing it.

I often compare software development to automobile manufacturing. Automobile Manufacturing will go through the steps of drawing design, model making, sample vehicle manufacturing, small batch production, and finally batch production. The entire process is interlocking, And the next process is based on the premise that the previous process is correct. If there is a leak in the drawing design stage, we can re-design the drawings. If this error is found in the model manufacturing stage, we should re-start from the drawing design stage to the model manufacturing stage, the more problems with the design are found later, the more expensive the modification is.

Databases are the foundation of the entire application. Without a solid foundation, the entire application is in danger.

Powerful databases are powerless in the face of poor Designs

The modern database management system (DBMS) provides convenient graphical interface tools that allow you to easily create tables and define columns. But can we design a structure?

Relational databases have many very good features, but improper design will make these features partially or completely lost.

Let's take a look at the following scenarios caused by poor Database Design:

1. Loss of Data Consistency

An order management system maintains the order information of customers and customers. After a user who uses the system receives a call from the customer to modify the Receiving address, the user modifies the Receiving address on the customer information page of the system, however, the original order of the customer still sent the wrong address.

2. Loss of data integrity

The company's strategic transfer is ready to withdraw from a region. The system operator deletes the configuration information of the region in the system. The system prompts that the region is deleted successfully. Then the problem came. The customer service staff found that the historical order page in the region encountered an error as soon as it was opened.

3. Performance Loss

In an inventory management system, the Warehouse administrator uses this system to record each incoming and outgoing goods and view the inventory of each current goods. After the system was running for a few months, the repository administrator found that opening the current inventory page was very slow and the overall trend was getting slower and slower.

The above scenarios are caused by improper database design. The root causes include: redundant fields are introduced during design, no reasonable design constraints, and no adequate performance design, the above example is just a drop in the ocean.

 

Database platform independence

The database design I discussed in this blog series does not target any relational database product. Whether you are using Oracle, SQL Server, Sybase, or open-source databases such as MySQL or SQLite, you can use them to practice the design methods and concepts discussed here, design is the core and soul of this series of blog posts.

Note: In this article, I will use a database product for demonstration. You can use a database product you are familiar with to experiment. At the end of this article, we will provide links to some free database products for you to download and learn.

Learning and making progress together

Whether you are a Database Designer, application architect, software engineer, Database Administrator (DBA), software project manager, software testing engineer, and other project team members, you can get some from this series of blog posts. Let's discuss and make progress together.

Fields involved

My current dream of this series of blog posts is to involve the whole process of database design. From Requirement Analysis to database modeling (conceptual data modeling), perform normalization until it is converted to SQL statements.

 

Before we design a database, let's take a look at the data storage methods in addition to relational databases.

Flat file)

Package objects ending with. txt and. ini.

Eg: content of A. ini file:

------------------------------------------------------------

[Websites]

Myblog = http://www.cnblogs.com/DBFocus

 

[Directorys]

Image = E: \ dbfocus project \ img

TEXT = E: \ dbfocus project \ Documents ents

Data = E: \ dbfocus project \ DB

------------------------------------------------------------

Advantages:

The storage format of files is very simple, and normal editors can open and modify files.

Disadvantages:

Unable to support complex queries

No verification function

The insert and delete operations on the content in the middle of a flat file actually generate a new file.

Applicable scenarios:

Stores a small amount of data that is not frequently modified, such as application configuration information.

Windows Registry

Incorrect modification to the Windows registry may cause system disorder. Therefore, we do not recommend that you store a large amount of data in the registry.

The Windows registry is a tree structure that stores system configuration information and application configuration information.

By storing different configurations on different branches of the registry, the public configuration information of the application is separated from the personal configuration information of the user.

Eg: a document version management system that can be configured to be associated with the file comparator installed on the master machine for document comparison. This is a public configuration. The file comparator path can be stored in the HKEY_LOCAL_MACHINE \ SOFTWARE branch of the Registry.

At the same time, the document version management system can record the 10 most recently opened document paths. This is the user's personal configuration information, which can be different for 10 recently opened documents of different Windows users. These configuration information can be stored in the HKEY_CURRENT_USER \ Software branch of the Registry.

Excel form (spreadsheets)

Advantages:

Excel is very popular and users are very familiar with spreadsheet expressions

Simple statistics are available to facilitate the generation of various charts

Disadvantages:

It is not applicable to many cases where the relationship between spreadsheet is complex.

Unable to cope with complex queries

Weak data verification

Applicable scenarios:

The data volume is not a very large office automation environment

XML

XML is a semi-structured data. Compared with Hypertext Markup Language (HTML), its tags can be customized and can be expanded.

Eg: content of an XML file

-----------------------------------------------------

<? XML version = "1.0" encoding = "UTF-8"?>

<Classschedule>

<Class name = "Psychology" room = "Field 3">

<Instructor> Richard storm </instructor>

<Students>

<Student>

<Firstname> Ben </firstname>

<Lastname> breaker </lastname>

</Student>

<Student>

<Firstname> Carol </firstname>

<Lastname> enflame </lastname>

<Nickname> candy </nichname>

</Students>

</Class>

</Classschedule>

-----------------------------------------------------

XML files have several features.

First, XML labels must be strictly matched and cannot be staggered.

Second, the XML file must have a root node that contains all other elements.

Third, different nodes at the same level do not have to contain the same elements. In the preceding example, Carol, the second student, has a special node named nickname. This feature makes XML more responsive to changes than relational databases in some scenarios.

Advantages:

Natural hierarchical structure

The text content is self-interpreted by tags.

The XML schema can be verified through XSD (XML schema language ).

There are many auxiliary technologies such as XPath, XQuery, XSL, and XSLT.

Some commercial databases (such as Oracle and SQL Server) support the storage and operation of XML data.

Disadvantages:

Data redundancy information is large.

Unable to support complex queries

Limited verification functions

The insert and delete operations on the content in XML are actually re-generating a new file.

Applicable scenarios:

Suitable for storing data with a small amount of data and hierarchical structures, such as tree configuration information

Nosql Database

I am not familiar with many non-relational databases, but do not provide many product names. There are some articles in the garden. This article also provides links for you to learn and study.

1. Key-value Database

Redis, Tokyo cabinet, flare

2. Document-oriented database

MongoDB, couchdb

3. Distributed Computing-oriented databases

Cassandra, Voldemort

Nosql has been very popular in recent years. I don't think nosql is a silver bullet. In some SNS application scenarios, nosql shows its superiority, however, in the financial industry and other scenarios with high data consistency, integrity, availability, and transactional requirements, nosql is not necessarily applicable now. We should fully analyze the application requirements and carefully select technologies and products.

Overview

1. Key Role of Database Design for successful software projects

2. This course has nothing to do with database products. The core is the design concept and method.

3. Various Data Storage scenarios

References

1. Oracle Database 10 GB Express Edition

2.SQL Server2008 r2Express-Overview

3.SQLiteHome Page

4.NosqlDatabase pen talk