Database design method, specification and skill
First, the database design process
Database technology is the most effective means of information resource management. Database design refers to a given application environment, constructing the optimal database mode, establishes a database and its application, and effectively stores data to meet user information requirements and processing requirements.
During the design of the demand analysis phase, the needs analysis phase is integrated with the application requirements of each user (the demand of the real world), forming a machine characteristics in the conceptual design phase, independent of the concept model of each DBMS product (information world model), using E-R map. In the logic design phase, convert the E-R diagram into a specific database product supported data model, such as a relationship model, forming database logic mode. Then, according to the requirements of the user processing, the necessary views (view) to form the external mode of the data is formed on the basis of the basic table. In the physical design phase, the physical storage arrangement, design index is performed, and the physical storage schedule, design index is formed, and the internal mode of the database is formed.
1. Demand analysis phase
Demand collection and analysis, resulting in the data demand description described by the data dictionary (and the processing requirements of the data flow diagram).
The focus of demand analysis is to investigate, collect and analyze information requirements, processing requirements, security and integrity requirements in data management.
Method of demand analysis: Investigate the status of organizations, investigating business activities of various departments, assisting users to identify the various requirements of the new system to determine the boundaries of the new system.
Commonly used surveys: follow-up operations, open investigations, please introduce, ask, design survey sheets, please fill in, check the record.
The methods of analyzing and expressing user needs mainly include two types of methods from top-down and bottom up. STRURED Analysis, SA method, from the top-level system organization, using a layer-by-layer decomposition mode analysis system, and describes each layer of data flow graphs and data dictionary.
Data flow graph expresss the relationship between data and processing processes. The data in the system is described by means of the Data Dictionary, referred to as DD).
The data dictionary is a collection of various types of data descriptions, which is about data in the database, namely metadata, not the data itself. Data dictionaries typically include five parts of data items, data structures, data streams, data storage, and processing (at least the data type of each field) and the primary keys within each table).
Data item description = {data item name, data item meaning description, alias, data type, length,
Value range, value of value, logical relationship with other data items}
Data Structure Description = {Data Structure Name, Meaning Description, Composition: {Data Items or Data Structure}}
Data flow description = {data flow name, description, data stream source, data flow direction,
Composition: {data structure}, average traffic, peak flow}
Data Store Description = {Data Storage Name, Description, Number, Flowing Data Stream, Flowing Data Stream,
Composition: {data structure}, data volume, access method}
Process Description = {Process Name, Description, Enter: {Data Stream}, Output: {Data Stream},
Process: {brief description}}
2. Concept structure design phase
By synthesizing the user's needs, an abstraction is summarized, and a conceptual model independent of the specific DBMS can be represented by E-R.
Concept model is used in the model of information. The conceptual model does not rely on a data model supported by a DBMS. The conceptual model can be converted to a specific data model supported on a computer on a computer.
Conceptual model features:
(1) Has strong semantic expressive ability, convenient, direct expression of various semantics knowledge in the application.
(2) It should be simple, clear, easy to understand, and is the language of communicating with database designers.
A common method of concept model design is an IDEF1X method, which is a semantic modeling technology applied to a semantic data model to establish a system information model.
The steps to create an E-R model using the IDEF1x method are as follows:
2.1 Part I - Initialization Project
The task of this phase is to start from the description and scope description of the purpose, determine modeling target, development modeling plan, organizing modeling team, collecting source materials, formulating constraints and norms. Collection source materials are the focus of this phase. Basic data sheets were formed by investigating and observing results, business processes, input and output of original systems, various reports, collected raw data.
2.2 First Step - Defining Entities
Entity set members have a common feature and attribute set, which can be directly or indirectly identified from the collected source material - basic data sheets directly or indirectly identify most of the entities. According to the terms of the source material name table, the term "code" ends, such as customer code, agent code, product code, etc., to initially identify the potential entity, resulting in a potential entity, forming a preliminary Entity table.
2.3 Step 2 - Definition Contact
Only binary links are allowed in the IDEF1X model, and N-yuan contact must be defined as n binary contacts. According to the actual business needs and rules, use the entity contact matrix to identify the binary relationship between the entity, and then determine the potential, relationship name, and instructions of the connection relationship according to the actual situation, determine the type of relationship, the identification relationship, non-identification relationship (forced Or optional) or non-deterministic relationships, classification relationships. If each instance of the sub-entity needs to be identified by the relationship between the parent entity, it is an identification relationship, otherwise it is a non-identification relationship. In the non-identification relationship, if the instance of each sub-entity is associated with a parent entity, it is forced, otherwise it is not forced. If the parent entity represents the same real object as the sub-entity, then they are classified.
2.4 Step 3 - Defining Code
By introducing the cross-entity to remove the non-determination relationship generated by the previous phase, then the Hou Selection Code Properties is identified from the non-cross-entity and independent entity to uniquely identify the instance of each entity, and then determine the main code from the Hou Code. In order to determine the validity of the main code and relationship, the non-air rules and non-multi-value rules are guaranteed, that is, one attribute of an entity instance cannot be null, and there is no one or more values at the same time. Find a misunderstanding determination relationship, further decompose the entity, and finally construct a key-based view of the IDEF1x model (KB].
2.5 Step 4 - Define Properties
From the source data sheet, the descriptive noun develops an attribute table to determine the owner of the attribute. Define non-primary code properties, check for non-empty and non-multileble rules for properties. In addition, check the full dependent function rules and non-delivery dependence rules, ensuring that a non-correct attribute must depend on the master code, the entire master code, just the master code. A full property view of the improved IDEF1X model that is at least in accordance with the third paradigm of the relationship theory is obtained.
2.6 Step 5 - Define other objects and rules
Define the data type, length, precision, non-empty, default, constraint rules, etc. of the attribute. Define object information such as trigger, stored procedures, views, roles, synonyms, sequences.
3. Logical structure design phase
Conversion of the conceptual structure into a data model (such as a relational model) supported by a DBMS, and optimizes it. The design logic structure should be selected that is best suited to describe the data model that express the corresponding concept structure, and then selects the most suitable DBMS.
Converting the E-R diagram into a relational model is actually to transform the contact between the entity, entity's properties, and entities to a relational mode. This conversion generally follows the following principles:
1) A physical transformation into a relational mode. The properties of the entity are the properties of the relationship. The code of the entity is the code of the relationship. 2) A M: N Contact Translation into a relationship mode. The code for each entity connected to this connection and the properties of the contact itself are converted to the properties of the relationship. The relationship is a combination of each solid code.
3) A 1: n Contacts can be converted to a separate relational pattern or can be merged with the N-terminal corresponding to the relationship mode. If converted to an independent relationship mode, the code of each entity connected to the connection is converted to the property of the relationship to the relationship, while the code is the code of the N-terminal entity.
4) A 1: 1 Contact can be converted to a separate relationship mode, or the relationship mode corresponding to any end can also be merged.
5) One multi-connection between three or more entities is converted into a relational mode. The code for each entity connected to the multi-diverse connection and the properties of the contact itself are converted to the properties of the relationship. The relationship is a combination of each solid code.
6) The contact between the same entity set, that is, from the above 1: 1, 1: N and m: n, respectively, may be processed separately.
7) The relationship mode with the same code can be merged.
In order to further improve the performance of the database application system, it is usually modified as a standardized theory, and the structure of the data model should also be modified, which is the optimization of the data model. Determine data dependence. Eliminate redundant links. It is determined that each relational model belongs to the first paragraph. Determine if they are merged or decomposed. Generally, the relationship is broken down into 3nF standard, namely:
Each value in the table can only be expressed once.
· Each line in the table should be unique (with unique keys).
Non-bonded information depends on other keys should not be stored within the table.
4. Database Physical Design Stage
Select a physical structure (including storage structure and access method) for the logical data model. According to the DBMS features and the needs of the processing, the physical storage arrangement, design index, and form a database in the database.
5. Database Implementation Stage
The data language (such as SQL) provided by DBMS and its host language (e.g., c), establishes a database based on logical design and physical design results, and prepares the debugging application, organizes data, and has a trial operation. Database implementation mainly includes the following work: Define database structures, organize data warehouses, prepare data, and database trial operations with DDL
6. Database operation and maintenance phase
The database application system can be put into formal operation after the trial operation. Evaluate, adjust and modify them during the database system operation. Including: Database dump and recovery, database security, integrity control, supervision, analysis and improvement of database performance, database reorganization and reconstruction.
Modeling tool
In order to speed up the database design speed, there are currently many database aids (Case tools), such as Rational Rational Rose, CA's Erwin and BPWIN, Sybase's PowerDesigner, and Oracle's Oracle Designer, etc.
Erwin is mainly used to establish a conceptual model and physical model of the database. It describes the properties of entities, contacts and entities with graphical ways. Erwin supports the IDEF1x method. Automatically generate, change, and analyze the IDEF1x model by using Erwin modeling tools, not only can achieve excellent business features and data requirements models, but also enable transformation from the IDEF1x model to database physical design. The model drawn by Erwin tool corresponds to both logical models and physical models. In a logical model, the IDEF1x toolbox can easily build and draw an entity contact and entity properties in a graphical manner. In the physical model, Erwin can define the corresponding table, column, and can automatically convert to the appropriate type for various database management systems.
Designers can use the corresponding database design modeling tools as needed. For example, after the demand analysis is completed, the designer can use Erwin drawer to convert the ER diagram into a relational data model, generate a database structure; draw data flow diagrams, generate an application. Second, database design skills
1. Design the database before (during the analysis phase)
1) Understand customer needs and ask how users look at future demand changes. Let customers explain their needs, and as the development continues, it is necessary to ask customers to ensure that their demand is still in the purpose of development.
2) Understanding business services can save a lot of time in future development stages.
3) Pay attention to the input and output.
When defining database tables and field requirements (input), you should first check existing or designed reports, queries, and views (outputs) to determine which of these outputs are necessary and fields.
Example: If the customer needs a report to sort by postal coding, segmentation and summing, you have to ensure that a separate postal coding field is included without the postal coding into the address field.
4) Create a data dictionary and ER chart
ER charts and data dictionary allows people to understand how to get data from the database. The ER diagram is useful to the relationship between the table, and the data dictionary illustrates the use of each field and any other alias. This is exact essential for documentation for SQL expressions.
5) Define the standard object naming specification
Naming of various objects of the database must speculate.
2. Design of tables and fields (database logic design)
Table design principle
1) Standardization and standardization
The standardization of data helps to eliminate data redundancy in the database. Standardization has several forms, but Third Normal Form (3NF) is often considered to have the best balance in performance, scalability, and data integrity. Simply, the table design principle of complying with the 3nf standard database is: "One Fact in One Place" means that a table includes only the basic properties of its own, and it is not necessary to decompose when it is not their own properties. The relationship between the tables is connected by foreign bonds. It has the following features: There is a set of tables that store the associated data connected by the key.
Example: A 3NF database that stores customers and its related orders may have two tables: Customer and ORDER. The ORDER table does not contain any information for the order related to the customer, but a key value will be stored in the table, which points to the line of the customer information in the Customer table.
In fact, for efficiency, it is also necessary to standardize the table.
2) Data drive
Many strategic changes and maintenance will be much easier, greatly enhanced, and greatly enhanced system flexibility and scalability.
For example, if the user interface is to access the external data source (file, XML document, other databases, etc.), you may wish to store the corresponding connection and path information in the user interface support table. Also, if the user interface performs tasks such as workflow (send mail, print letter, modifying record status, etc.), then data generating workflow can also be stored in the database. Role permission management can also be done by data drivers. In fact, if the process is data-driven, you can push considerable responsibility to the user, by the user to maintain its workflow process.
3) Consider various changes
Which data fields that may be changed while designing a database.
For example, the surname is this (note is the surname of Westerners, such as women who have been married). So, when you create a system storage customer information, store the last name field in a separate data table, and you also attach the starting day and terminating day, so you can track the changes in this data entry.
Field design principle
4) 3 useful fields that should be added in each table
· DRECORDCREATIONDATE, default is now (), and under SQL Server, getDate () · SRecordcreator, under SQL Server, NOT NULL DEFAULT USER
· NRecordVersion, recorded version tag; help to accurately describe NULL data or loss data in the record
5) Use multiple fields to the address and telephone
It is not enough to describe the street address. Address_Line1, address_line2 and address_line3 can provide greater flexibility. Also, the phone number and email address are best to have its own data sheet, and it has its own type and tag category.
6) Use role entities to define columns belonging to a category
When you need to define a specific category or a specific role, you can create a specific time-related relationship with a role entity, so you can implement self-documentation.
Example: Use the Person entity and the Person_Type entity to describe the person. For example, when John Smith, Engineer is upgraded to John Smith, Director and even finally climbed to John Smith, CIO's high, and all you have to do is changing the key value between the two tableson and person_type, increasing one Date / Time fields to know when the change occurs. In this way, your Person_Type table contains all Person possible types, such as Associate, Engineer, Director, CIO or CEO, etc. There is also an alternative to changing the Person record to reflect changes in the new title, but this cannot track the specific time of the position where you are in place.
7) Select the digital type and text type as much as possible
Use the Smallint and Tinyint types in SQL to be particularly careful. For example, if you want to see the total monthly sales, the total field type is smallint, then if the total amount exceeds $ 32,767, it cannot be calculated.
The text field of the ID type, such as the customer ID or order number, etc. should be set to be more imagined. Suppose the customer ID is 10 digits. Then you should set the length of the database table field to 12 or 13 characters. But this additional space can achieve the growth of database size without the need to refactor the entire database in the future.
8) Increase the deletion mark field
In the table contains a "delete tag" field so that the row is marked as deletion. Do not delete a row separately in the relational database; it is best to use clear data programs and carefully maintain index integral.
3. Select key and index (database logic design)
Key Selection Principle:
1) Key design 4 principle
• Create foreign keys for related fields.
· All keys must be unique.
· Avoid using a compound key.
· Foreign key is always associated with unique key fields.
2) Use the system generated primary key
When designing the database, the system generated key is used as a primary key, then actually controls the index integrity of the database. In this way, the database and non-manual mechanisms effectively control the access to each line of storage data. There is also an advantage that the system generated key is made as the primary key: when you have a consistent key structure, it is easy to find logical defects.
3) Do not use the user's key (not let the primary key to have updateable)
When determining what the field is used as a table, you can be careful that the user will be edited. In general, do not select the user-edit field as a key.
4) Optional button Sometimes you can do primary key
Further use of the option key to make the primary key, you can have the ability to build powerful indexes.
Index usage principle:
Indexes are one of the most efficient ways to get data from the database. 95% of database performance issues can be resolved using indexing techniques.
1) The logical primary key uses the unique group index, and the unique non-group index is used to the system key (as a stored procedure), which uses a non-group index for any foreign key column. How much space considering the database, how to access, and whether these access is mainly used as read and write. 2) Most databases index automatically created primary key fields, but don't forget the index foreign key, they are often used frequently, such as running a query showing a record of the primary table and all associated formats.
3) Do not index the MEMO / Note field, do not index large fields (there are many characters), so that the index will take much storage space.
4) Do not index common small tables
Don't set any keys for small data tables, if they often have this insert and delete actions. Index maintenance for these insertions and deletions may consume more time than scanning tables.
4. Data Integrity Design (Database Logic Design)
1) Integrity implementation mechanism:
Entity integrity: primary key
Reference integrity:
Delete data in the parent table: Cascade deletion; limited deletion;
Insert data in the parent table: restricted insert; recursive insertion
Update data in the parent table: cascading update; limited to the null value
DBMS can have two ways to implement: foreign key implementation mechanism (constraint rules) and trigger implementation mechanism
User definition integrity:
NOT NULL; Check; Trigger
2) Force data integrity with constraint rather than business rules
The database system is used to achieve data integrity. This includes not only the integrity of standardization and also includes functionality of data. You can also increase the trigger to ensure the correctness of the data when writing data. Do not rely on the business layer to ensure data integrity; it does not guarantee the integrity of the table (foreign bond), so it is impossible to impose on other integrity rules.
3) Force instruction integrity
Remove it before harmful data enters the database. Activate the integrity features of the database system. This allows the data to be cleaned to force the developer to put more time handling error conditions.
4) Use Find Control Data Integrity
The best way to control data integrity is to limit the selection of users. As long as it is possible to provide a clear value list for users to choose from. This will reduce the incorrect and mishand of the type of code to provide data consistency. Some public data is especially suitable for finding: national code, status code, etc.
5) Adopt view
In order to provide another layer of abstraction between the database and the application code, you can establish a dedicated view for the application without having to access the data table directly to the application. This is also equal to providing more freedom when processing database changes.
5. Other design skills
1) Avoid using triggers
The functionality of the trigger can usually be implemented in other ways. The trigger may become interference when debugging the program. If you really need a trigger, you'd better focus on its documentation.
2) Use common English (or any other language) instead of using coding
It is best to sort in English name when creating a drop-down menu, a list. If you need to encode, you can attach the user known by the user.
3) Save common information
It is useful to make a table to store general database information. Store the current version of the database in this table, recently check / fix (for access), the name of the design document, and the information, etc. This enables a simple mechanism to track the database. When the customer complains that their database does not meet the desire to meet your requirements, this is especially useful for non-client / server environment.
4) Contains version mechanism
Introduce version control mechanisms in the database to determine the version of the database in use. For a long time, the needs of users will always change. It may eventually be required to modify the database structure. It is more convenient to store version information directly into the database.
5) Prepare a document
For all shortcuts, naming specifications, restrictions, and functions must be programmed.
Use database tools that are added to tables, columns, triggers. Very useful for development, support and tracking modifications.
Database documentation, or build a document inside or separately in the database itself. In this way, after a year, I will return to the 2nd version after more than a year, and the chance to make mistakes will be greatly reduced. 6) Test, test, repeated test
After establishing or revising the database, you must use the data field to test the data field with the user. Most importantly, let users test and ensure that the selected data type meets business requirements. Test needs to be completed before putting the new database into actual services.
7) Check design
The common technologies for checking database designs during development are through their supported application prototypes. In other words, for each final expressed data, you can check that you check the data model and see how to remove the data.
Third, the database naming specification
1. Named of entity (table)
1) Table Name or noun phrase, determine whether the table name is a plural or singular form, and the alias defined for the list is a simple rule (for example, if the table name is a word, the alias will take the first 4 letters of the word; if The table name is two words, and the first two letters of two words form four letters long alias; if the name of the table consists of 3 words, take one from the first two words and then then from the last word Remove two letters, the result is still an alias for 4 letters, and the rest is pushed.
For work Table, the table name can be added to the name of the application that uses the table after the prefix Work_. In the naming process, according to semantic scattless abbreviation. Note that because ORCLE will unify the field name into one of the uppercase or lowercase, it is required to add an underscore.
Example:
Defined abbreviation SALES: SAL sales;
Order: ORD order;
Detail: DTL details;
Then the sales order will be named: SAL_ORD_DTL;
2) If the table or the name of the field has only one word, it is recommended not to use abbreviations, but use a complete word.
Example:
Defined abbreviation Material MA items;
The item is named: Material, not MA.
But the field item coding is: ma_id; not matial_id
3) All stored value lists plus prefix Z
The purpose is to sort these values list in the database.
4) Naming (mainly accumulated tables) of all redundant classes (mainly in the table)
Redundant categories is to increase database efficiency, add fields or tables when non-standardized databases.
5) Association class named the prefix R after connecting two basic classes with underscores, followed by the alphabetical quoriosis or the abbreviation of the table name.
Association tables for saving multiple pairs of relationships.
If the associated table name is greater than 10 letters, the original table name must be abbreviated. If there are no other reasons, it is recommended to use abbreviations.
Example: Table Object and itself have many-to-many relationships, saving many-to-many table names: r_Object;
Table depart and employee; there are many-to-many relationships; the association table is named R_DEPT_EMP
2. Named of attributes (column)
1) Use a meaningful column name, the column within the table should be a set of design rules for the key. Each table will have an automatic ID as a primary, logically masterjian as the first group of candidate, if the database is automatically generated, unified named: ID; if it is a custom logical The encoding is named using an abbreviated method. If the key is a digital type, you can use _no as a suffix; if it is a character type, you can use the _code suffix. The column name should adopt a standard prefix and suffix.
Example: Name field named: Sal_ord_id; if there is still a database generated automatic number, name: ID.
2) All attributes plus the suffix of types, note that if other suffixes are needed, they are placed before the type suffix. Note: The data type is the field of the text, and the type suffix TX can not be written. Some types are more obvious fields, and they can write the type suffix.
3) Name the prefix
Give the column names of each table adopt a unified prefix, then great simplification will be made when writing SQL expressions. This does have a disadvantage, such as destroying the role of the automatic table connection tool, and the latter links the public column name to some databases.
3. Named view
1) The view is similar to the name of V as a prefix, other naming rules, and tables;
2) Naming should try to reflect the functions of each view.
4. Naming of the trigger
The trigger is used as a prefix as a prefix, the trigger name is the corresponding table name plus the suffix, the Insert trigger plus '_i', the delte trigger plus '_d', the UPDATE trigger plus '_u', such as: tr_customer_i, tr_customer_d, tr_customer_u .
5. Store process name
The stored procedure should be started with 'up_', and the stored procedure of the system is distinguished, and the subsequent part is mainly composed of passenger guests, and various components are divided by the underline. If the stored procedure for adding the agent's account is 'Up_ins_Agent_account'.
6. Variable name
The variable name uses lowercase, if belongs to the form of phrase, separating each word with underscore, such as @my_err_no.
7. Other considerations in naming
1) The above naming must not exceed 30 characters system restrictions. The length of the variable name is limited to 29 (excluding identification character @).
2) Data objects, the naming of variables use English characters, prohibiting using Chinese naming. Never leave space between the characters of the object name.
3) Be careful to keep the words, to ensure that your field name does not conflict with the retention word, database system or common access method
5) Keep the word name and type of consistency, you must ensure consistency when named the field and specify the data type. If the data type is an integer in a table, you can turn it in another table.
Relevant information:
