Things about the project (10)-the result of setting up standards for enjoying the [project standards]

The importance of standardization is discussed above. This article focuses on Standardization implementation.

When I was just preparing for the project team, I established C # code specifications, SQL script specifications, and database design specifications.
In addition to database design specifications, the other two websites are ready-made. Just click Baidu.
This database specification is not the first paradigm, the second paradigm, but a truly executable directing.

First, you must specify the names in the database. It is relatively simple to use English words to represent the meaning of objects, while English words are separated by case. For example, goods numbers are named as goodsid.
I have seen pinyin abbreviations as object names. The advantage of this is that the threshold is low, especially for those who have not passed level 4 and typed in pinyin again. However, there are also many problems. The abbreviation of Pinyin really does not understand what it means. The cargo number is named hwbh, And the abbreviation of overseas number is also hwbh. This repetitive problem is also a headache, and some heroes come up with the suffix method, hwbh1, hwbh2, which is just a cloud disaster.

It is also a good choice to use English abbreviations, but it is also troublesome to shrink too much. For example, the English with multiple batches should be most lot, and a big sister (pay attention to being a female) it is abbreviated as ML. Although that's right, it's always uncomfortable.

I have also seen case-insensitive and underlined characters. For example, goodsid is changed to goods_id. This is quite clear. However, it is not convenient for programmers to underline more each time (the underline must be SHIFT +-only ).

After naming, we began to define data types in a unified manner. Relational databases have been developing for more than 30 years, and there are a wide variety of data types. However, you cannot use all data types for a project. In particular, the current DBMS has extended many types, such as bit, bool, and text. Because I don't want to change the database into a zoo, I set the data type in the project.
The integer type can only be Int. The decimal places include numeric (21,3) and numeric (100). The character strings include varchar (5), varchar (20), varchar ), varchar (200); date is only datatime.
Okay, no. These types are enough, and some of them are not used for datatime (instead of varchar ).

As soon as this data type was introduced, some people made comments. He thought that varchar (200) is too small to use varchar (4000 ).
To be honest, this is a rather unreliable opinion. The 4000 characters are equivalent to 2000 Chinese characters and 2000 Chinese characters. You think this system is going to save the paper, DBMS specifies that the total length of a column in a table should not be greater than 8 K. That is to say, if you want to have two varchar (4000) fields, it is difficult to add another field in this table.
However, direct rejection is not a good behavior. At least the project manager should not be too aggressive. To accept the suggestion, I decided to change varchar (200) to varchar (400 ). Of course, a few wicked users can be changed to varchar (201). I am not yet wicked.

To better define these types and prevent unauthorized users from using them, I introduced powerdesigner as a database modeling tool. In addition to database modeling, this tool also supports UML, business modeling, organizational structure, flowcharts, and so on. It is a general interface, and is a better hard currency than Visio and rose in terms of functions.

Powerdesigner (PD) contains a domain object (the specific location is in the menu model-> domain), which defines the data type as an object. You can set a name for an object and start an external number. In this way, according to the above specification, I have set eight domain names. Therefore, domain names cannot be added without authorization.

In PD, you can define the format of the attribute in the table. I hide the data type in the attribute in the table and can only use domain to select.

PD also has a function to copy columns (replicate columns, the specific location is in the columns page box in the table Properties window ). That is, you can introduce existing columns in the model to another table. The introduced Columns cannot be modified. If you modify the source column, all the introduced columns will change. In short, it is the inheritance column, which cannot be overloaded or polymorphism.

This is of great benefit. For example, if you create a personnel table, the personnel number is varchar (20), and the invoice table is saved as a single user, the personnel number here is varchar (10 ). The problem arises. When the personnel number is less than 10, everything is normal. When the number is greater than 10, the creation is successful, but it cannot be implemented in the business, the reason is that the input character is too long.

This problem is very difficult to solve and should be regarded as a bug. However, such bugs are generated only under specific conditions and cannot be seen in general. The introduction of columns can avoid such problems.

When creating a column, PD can not only write names and encoding for the column, but also add comments to the column. In my database design specifications, all columns must be annotated. The comment content is the label content displayed on the front-end. You can use the code to find the comment value through the column extension attribute in the database.

PD can also store triggers, stored procedures, views, and other items in the database. It can also produce different SQL statements based on different DBMS. In short, this is a powerful database modeling tool. According to Sybase, I used 12.5 for a total of 128 MB. The generated PDM file is still in XML format. The latest version is 15.

Primary keys have always been the focus of database design, but some people do not create primary keys. Although I don't know why, the consequences of not creating a primary key are miserable, you cannot delete or modify the view of a DBMS. The primary index is not automatically created.
Create a primary key. This is the same as that of the management market. You need to know how to manage it. If it is difficult to handle the problem and beat the wolf's stick to the sheep, it will fail. In the past two years, our great motherland has failed several times. In order not to let us fail, we must analyze our past experiences.

The simplest primary key is guid, which is a 32-Bit String.
Advantage: the world's only random generation.
Disadvantage: The structure is too long and unordered.

The long structure indicates that the index space is large. The larger the space, the longer the retrieval time.
Speaking of disorder, let's talk about the indexing mechanism. indexing exists for quick retrieval, therefore, the data in it is arranged in order (we recommend that you check the indexing principle for the order of data ).
Now the ordered index has a disordered guid. The index is a single rib. I think you have to listen to me when you get to this acre of land. Therefore, indexes follow their own rules. When a new guid comes in, it sorts all guids again. The indexing method is correct on its own (to maintain order), but the cost of re-indexing is the performance reduction. In a word, using guid as the primary key will reduce the efficiency of the database.

GUID is not suitable for primary keys (I did not say this sentence, which is described by the Microsoft SQL Development Team ).

The row cannot be identified, and the column ID is int, which is also discussed by the project team. The result is no. Because the ID column is generated after the data is saved, that is, Zhuge bright afterwards. After the event, Zhuge Liang was not doing well in advance.

The problem arises. One document is saved to two tables, the master table and the detail table. The two are associated with the primary key of the primary table. Generally, two tables are added together. I want to write the primary key of the primary table to the list, And Zhuge Liang later will know the value of the primary key after I finish writing the primary table. I will read the return value again, it is assigned to the entity class of the detail table again, and then written to the detail table. This is a headache, and Zhuge Liang cannot control it afterwards, or it is very difficult to control it. Therefore, it is not ideal to use the ID column.

Let's create a primary key generation algorithm. This advantage is obvious, but the disadvantage is also obvious. The main disadvantage is to increase the development workload and get a primary key every time. In case of poor control, duplicate primary keys will become even worse. But this is the best method I can think. My IQ is a good method. Haha

The primary key generation method is available. To facilitate management and make the data persistence layer more common, I ordered that all primary keys be named IDS (ID is a reserved word, so add S ).
 
The database is basically OK. Now let's look at the system framework.

The system framework is relatively simple: Data Persistence Layer + business layer + presentation layer. The object class of the data persistence layer is generated from the database. Don't ask me how to generate it. The database is provided to you. Can't you generate it?

The business layer is full of code, and the database basically has nothing to do with it.

Because domain and annotation are defined for columns in the database, the presentation layer can be generated through the database. The domain generates controls, and the annotations generate lable. The column code is used as the control name, of course, you must add a prefix (different prefixes are used for different controls ). The location must be manually adjusted. If you are not in trouble, you can write all the locations to the database.
You don't need to worry too much about the basic interface layer and data layer. Well-designed the database, the two layers are basically finished, finding an intern and putting their positions on the tab is complete.

Now, my team can put all its energy into the business layer. The business layer is the hardest hit by standardization. Here I decided to put a fierce person to solve this problem.

Post:

My database naming method is only available under sqlserver. It is used in Oracle to find the dead, because all the Oracle column names are of the size. If the size is determined, quotation marks must be used. Therefore, Oracle uses underscores to differentiate data. If you stick to the quotation marks, it is also acceptable. If you can bear to add two quotation marks for each column.

In fact, many people know this set of things, and many people do this. I mentioned it because it is part of my project standards.

From my project experience, programmers do not know much about databases. Most of them use hibernate. Now I am better off with LINQ, and I will not write any entity classes. Most people will select, insert, delete, and update.

In fact, let's take a look at it, whether it's VB, Pb, Delphi, or Java ,. net, PHP, calculate earlier dBase, Foxpro, then look at the architecture, standalone version to C/S to B/S, and B/S, these technologies are constantly updated, the back-end database remains unchanged. If the version is not changed, it indicates that the version is continuously upgraded. The core operation of our business system is to add, delete, modify, and query operations, all of which are database operations. So it is necessary to learn about the database. Looking into the future, the sky is Bi and data warehouse, that is to say, the database is still a long journey!

Sometimes I think that SQL is the same as the single 9 sword, and there are only a few moves (the Single 9 sword has nine moves, and the SQL only has four moves), but each move changes a lot, there are more than 300 changes (as for the number of 300, I can't remember it. If you have time to flip through "swordsman", I think the change in select will not be less than this number.

Therefore, it is easy to learn SQL, and it is difficult to be proficient. (It takes a lot of effort to learn the nine swords ). In addition, the content learned by the database processing mechanism (index, optimization, lock mechanism, etc.) is very huge.

Experienced users will know that when the system is inefficient, it is often caused by the database (manual SQL writing is not optimized ). So it is recommended that programmers learn SQL and have multiple skills.

Powerdesigner 12.5 official (15-day trial) http://download.sybase.com/eval/PowerDesigner/powerdesigner125_eval.exe size 125 MB (132,006,349 bytes)