Data Dictionary-based Universal Query System (2) Analysis of database Composition Structure

Haha, the first write TechnologyArticleI didn't expect so many people to pay attention to it. Thank you. If you have a visitor, I will write it. If you are not good at writing, you will just make a brick.

Let's talk about it first.SQLThe injection problem is not taken into consideration, because it has been being doneWinform, Little experience, many of them are groping, so I don't know much about this part.

Now let's continue with our topic and follow our promise. Today's topic is the analysis of the database composition structure.

 

When designing and using databases, we mainly consider the following.

1.Table

In a general database, a common table corresponds to information related to an object, such as an order and a record in an order.

2.Columns in the table

The list in the table shows each attribute of the object.

3.Column Data Structure

Whether to use integer, floating point, or date, string The data structure of a column depends on the information to be expressed in the column.

4.Relationship between tables

That is, how tables are associated with each other, that is, how object information is associated.

 

The method used in the previous article is as follows: To analyze the position of these structures in our module.

First, there is a ing relationship between the element in the database and the object representation. For example, an order is an order that we usually express with the user, but in the database, it should be a table.Orders,

The order number should be a tableOrders,InNoColumn.

Similarly, further, in fact, these operations are required when a user builds a query. For example, if they select a value greaterSQLShould be expressed"> ",They choose and, we should express it as"And ",As one of the replies in the previous article said:

You need provide a middle tier to seperate your DB and your client.
This tier provides bussinss-related column list to your client.

 

ThisMiddle tireIt requires us to design again in the future, for each ofClassThere must be at least two attributes. One is displayed to the user, and the other is used to formSQLStatement.

 

Tables and columns are better processed.

Here we mainly talk about the data structure.

Consider the following questions.

1.The data structure you see is not exactly the same as the actual data structure.

For example, in the database, the numeric type hasInt, double, decimal,String hasVarchar, nchar...But for the user who wants to query information, he does not need to know such a specific thing. He only needs to know that a property is a value and a string.

2.The data structure you see is different from the actual structure.

In this case, the most typical is the processing of time. Some databases are designed to facilitate processing and use the date8Bit String storage, such20081217, And some directly useDatetimeStorage, but no matter what is used for storage, this attribute reflects that the user is real time .

3.The data structure you see requires additional information.

A typical example is the status bit.IntOrTinyintTo store the status of a record, such0Indicates not processed,1Processing,2Indicates that it has been processed.

You do not need to let users know0,1,2But you want to allow him to accurately express his meaning through the query. That is, he can check "How many orders have been processed today"Instead"How many orders are in the status today?2"Well, this is back to the one we mentioned earlier.Middle tier.

Based on the above situations. We can roughly divide the data into four types

The first type indicates the data of the numeric value, which can beInt,YesDecimal ,...

Second, string You don't need to explain this much.

Third, date and time can beDatetime,Or yes.Char

Fourth, status bit For example, not processed or processed.

Finally, let's talk about the table connection. .

Table connections include internal connections, external connections, one-to-one, one-to-many connections during design. However, in the customer's opinion, the connection means that the user can query additional information, for example, when querying a sales record, he can query the order where the record is located. When querying an order, he can list all the sales records of the order.

Therefore, the processing of various connections only represents a link information at the user level.

Okay, well, today's task is completed, and various elements in the database are analyzed. We will try to provide the database design tomorrow ..

Let's take a look at the official discussion.~~~~

 

Series Article connection

I. SQL statement Structure Analysis

II. Analysis of the database composition structure.

3. Database design.

4. Entity Design

5.AlgorithmImplementation