Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the design methods of the database?" in the operation of actual cases, many people will encounter such a dilemma, and then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

I. Database design process

Database technology is the most effective means of information resource management. Database design refers to the construction of an optimal database schema, the establishment of a database and its application system, effective storage of data, and meeting the requirements of user information and processing for a given application environment.

In the requirement analysis stage of database design, the application needs of each user (real world requirements) are integrated, and a conceptual model (information world model) independent of machine characteristics and DBMS products is formed in the conceptual design stage, which is described by Emurr diagram. In the logical design phase, the Emurr diagram is transformed into a specific data model supported by database products, such as a relational model, to form a database logical schema. Then, according to the requirements of user processing and the consideration of security, the necessary VIEW is established on the basis of the basic table to form the external schema of the data. In the physical design stage, according to the characteristics of DBMS and the needs of processing, the physical storage is arranged, the index is designed, and the schema in the database is formed.

1. Demand analysis phase

The requirements are collected and analyzed, and the results are the data requirements described by the data dictionary (and the processing requirements described by the data flow diagram).

The focus of requirements analysis is to investigate, collect and analyze users' information requirements, processing requirements, security and integrity requirements in data management.

The method of needs analysis: investigate the organizational structure, investigate the business activities of various departments, assist users to clarify the requirements for the new system, and determine the boundaries of the new system.

The commonly used survey methods are as follows: follow-up homework, hold an investigation meeting, ask someone to introduce, inquire, design a questionnaire, ask users to fill in, and consult records.

The methods of analyzing and expressing user requirements mainly include top-down and bottom-up methods. The top-down structured analysis method (Structured Analysis, referred to as SA method) starts with the top-level system organization, analyzes the system layer by layer, and describes each layer with data flow diagram and data dictionary.

The data flow diagram expresses the relationship between data and processing. The data in the system is described with the help of data dictionary (Data Dictionary, referred to as DD).

Data dictionary is a collection of all kinds of data descriptions, it is about the description of the data in the database, that is, metadata, not the data itself. The data dictionary usually consists of five parts: data item, data structure, data flow, data storage and processing (at least the data type of each field and the primary foreign key in each table).

Data item description = {data item name, data item meaning description, alias, data type, length

Value range, value meaning, logical relationship with other data items}

Data structure description = {data structure name, meaning description, composition: {data item or data structure}}

Data flow description = {data flow name, description, data flow source, data flow destination

Composition: {data structure}, average traffic, peak traffic}

Data store description = {data storage name, description, number, inflow data stream, outflow data stream,

Composition: {data structure}, data volume, access method}

Process description = {process name, description, input: {data stream}, output: {data stream}

Processing: {brief description}}

two。 Conceptual structure design stage

Through the synthesis, induction and abstraction of user requirements, a conceptual model independent of concrete DBMS is formed, which can be represented by Emurr diagram.

The conceptual model is used to model the information world. The conceptual model does not depend on a data model supported by DBMS. The conceptual model can be transformed into a specific data model supported by a DBMS on the computer.

Features of the conceptual model:

The main results are as follows: (1) it has strong semantic expression ability, and can express all kinds of semantic knowledge conveniently and directly.

(2) it should be simple, clear and easy for users to understand. It is a language for communication between users and database designers.

A common method of conceptual model design is the IDEF1X method, which is a semantic modeling technology that applies the entity-relation method to the semantic data model to build the system information model.

The steps to create the Emurr model using the IDEF1X method are as follows:

2.1 step Zero-initialize the project

The task of this stage is to start with purpose description and scope description, determine modeling goals, develop modeling plans, organize modeling teams, collect source materials, and formulate constraints and specifications. The collection of source materials is the focus of this stage. Through the investigation and observation results, the business process, the input and output of the original system, various reports, collecting the original data, the basic data table is formed.

2.2 first step-define entities

Entity set members have a common feature and attribute set, and most entities can be identified directly or indirectly from the collected source material, the basic data table. Identify the entities represented by the nouns according to the terms represented in the source material name list and the terms with the end of the "code", such as customer code, agent code, product code, etc., so as to initially find out the potential entity and form a preliminary entity table.

2.3 step 2-define the connection

Only binary connections are allowed in the IDEF1X model, and n-ary connections must be defined as n binary connections. According to the actual business requirements and rules, the entity relation matrix is used to identify the binary relationship between entities, and then the potential, name and description of the connection relationship are determined according to the actual situation, and the relationship type is determined. Whether it is an identification relationship, a non-identification relationship (mandatory or optional) or an uncertain relationship, classification relationship. If each instance of a child entity needs to be identified by a relationship with the parent entity, it is an identity relationship, otherwise it is a non-identity relationship. In a non-identity relationship, it is mandatory if an instance of each child entity is associated with and only one parent entity, otherwise it is non-mandatory. If the parent entity and the child entity represent the same real object, then they are classified relations.

2.4 step 3-define the code

The uncertain relationship generated in the previous stage is removed by introducing cross entities, and then the candidate code attributes are identified from non-crossed entities and independent entities, so that the instance of each entity is uniquely identified, and then the main code is determined from the candidate code. In order to determine the validity of master code and relationship, it is guaranteed by non-empty rules and non-multi-valued rules, that is, an attribute of an entity instance cannot be null, nor can it have more than one value at the same time. Find out the determination relationship of misrecognition, further decompose the entity, and finally construct the bond base view of the IDEF1X model (KB diagram).

2.5 step 4-define attributes

Extract descriptive nouns from the source data table to develop a property table to determine the owner of the attribute. Define non-master attributes and check non-empty and non-multi-valued rules for attributes. In addition, check the fully dependent function rules and non-transitive dependency rules to ensure that a non-master attribute must depend on the master code, the whole master code, and only the master code. As a result, the full attribute view of the improved IDEF1X model is obtained, which at least accords with the third normal form of relational theory.

2.6 step 5-define other objects and rules

Define the data type, length, precision, non-empty, default values, constraint rules, and so on. Define object information such as triggers, stored procedures, views, roles, synonyms, sequences, etc.

3. Logical structure design stage

Transform the conceptual structure into a data model supported by a DBMS, such as a relational model, and optimize it. To design the logical structure, we should choose the data model that is most suitable for describing and expressing the corresponding conceptual structure, and then choose the most appropriate DBMS.

Transforming an Emurr diagram into a relational model actually transforms entities, attributes of entities, and relationships between entities into relational schemas, which generally follow the following principles:

1) A physical type is transformed into a relational schema. The attribute of an entity is the attribute of a relationship. The code of the entity is the code of the relationship.

2) an MVR n contact is converted into a relational schema. The code of each entity connected to the connection and the attribute of the connection itself are converted into the attribute of the relationship. The code of the relationship is the combination of the entity codes.

3) A 1VR n connection can be transformed into an independent relational schema, or it can be merged with the corresponding relational schema at the n end. If it is converted to an independent relation mode, the code of each entity connected to the connection and the attribute of the connection itself are converted into the attribute of the relationship, and the code of the relationship is the code of the n-end entity.

4) A 1:1 connection can be transformed into a separate relational schema, or it can be merged with the corresponding relational schema at either end.

5) A multiple relationship between three or more entities is transformed into a relational model. The code of each entity connected with the multiple connection and the attribute of the connection itself are converted into the attribute of the relationship. The code of the relationship is the combination of the entity codes.

6) the connection between entities of the same entity set, that is, self-connection, can also be dealt with separately according to the above three cases: 1:1, 1RV n and mRV n.

7) Relational patterns with the same code can be merged.

In order to further improve the performance of the database application system, it is usually guided by the standardization theory, and the structure of the data model should be modified and adjusted appropriately, which is the optimization of the data model. Determine data dependencies. Eliminate redundant connections. Determine which paradigm each relationship model belongs to. Determine whether to merge or decompose them. Generally speaking, the standard for decomposing relationships into 3NF is:

Each value in the table can only be expressed once.

Each row in the table should be uniquely identified (with a unique key).

Non-key information that depends on other keys should not be stored in the table.

4. Database physical design phase

Select a physical structure (including storage structure and access method) that is most suitable for the application environment for the logical data model. According to the characteristics of DBMS and the needs of processing, the physical storage is arranged, the index is designed, and the schema in the database is formed.

5. Database implementation phase

Using the data language provided by DBMS (such as SQL) and its host language (such as C), the database is established according to the results of logical design and physical design, the application program is compiled and debugged, the data is stored, and the trial operation is carried out. Database implementation mainly includes the following work: defining database structure with DDL, organizing data storage, compiling and debugging application programs, and database trial operation.

6. Database operation and maintenance phase

After trial operation, the database application system can be put into formal operation. In the process of running the database system, it must be constantly evaluated, adjusted and modified. Including: database dump and recovery, database security, integrity control, database performance supervision, analysis and improvement, database reorganization and restructuring.

The use of modeling tools

In order to speed up the speed of database design, there are many database auxiliary tools (CASE tools), such as Erwin of Rational's Rational Rose,CA, PowerDesigner of Bpwin,Sybase 's and Oracle Designer of Oracle's.

ERwin is mainly used to build conceptual model and physical model of database. It can graphically describe entities, relationships and attributes of entities. ERwin supports the IDEF1X method. By using ERwin modeling tools to automatically generate, change and analyze IDEF1X models, not only excellent business functions and data requirements models can be obtained, but also the transformation from IDEF1X models to database physical design can be realized. The models drawn by ERwin tools correspond to logical models and physical models. In the logical model, the IDEF1X toolkit can easily build and draw entity relationships and entity attributes in a graphical way. In the physical model, ERwin can define corresponding tables and columns, and can be automatically converted to appropriate types for various database management systems.

Designers can choose the corresponding database design modeling tools according to their needs. For example, after the completion of requirements analysis, designers can use Erwin to draw ER diagrams, transform ER diagrams into relational data models, generate database structures, and draw data flow diagrams to generate applications.

Second, database design skills

1. Before designing the database (requirements analysis phase)

1) understand customer needs and ask users how to view future demand changes. Let the customer explain their requirements, and as the development continues, often ask the customer to ensure that their requirements are still in the development purpose.

2) understanding the enterprise business can save a lot of time in the later development phase.

3) attach importance to input and output.

When defining database table and field requirements (inputs), you should first examine existing or designed reports, queries, and views (outputs) to determine which tables and fields are necessary to support these outputs.

For example: if a customer needs a report sorted, segmented, and summed by zip code, make sure that it includes a separate zip code field instead of blending the zip code into the address field.

4) create a data dictionary and ER chart

ER diagrams and data dictionaries allow anyone who knows about the database to know how to get data from the database. ER diagrams are useful for showing relationships between tables, while data dictionaries describe the purpose of each field and any aliases that may exist. This is absolutely necessary for the documentation of SQL expressions.

5) define standard object naming conventions

The naming of various objects in the database must be standardized.

two。 Design of tables and fields (database logic design)

Table design principle

1) Standardization and normalization

The standardization of data helps to eliminate data redundancy in the database. There are several forms of standardization, but Third Normal Form (3NF) is generally considered to achieve the best balance in terms of performance, scalability, and data integrity. Simply put, the table design principle of a database that complies with the 3NF standard is: "One Fact in One Place" means that a table contains only its own basic attributes and needs to be decomposed when they are not their own attributes. The relationships between tables are connected by foreign keys. It has the following characteristics: there is a set of tables dedicated to storing associated data connected by keys.

For example, a 3NF database that holds customers and their related orders may have two tables: Customer and Order. The Order table does not contain any information about the customers associated with the order, but within the table

A key value is stored that points to the row in the Customer table that contains the customer information.

In fact, it is sometimes necessary not to standardize the table for the sake of efficiency.

2) data driven

Using data-driven rather than hard-coding approach, many policy changes and maintenance will be much more convenient, greatly enhancing the flexibility and expansibility of the system.

For example, if the user interface wants to access external data sources (files, XML documents, other databases, etc.), you might as well store the corresponding connection and path information in the user interface support table. Also, if the user interface performs tasks such as workflow (sending mail, printing letterhead, changing record status, etc.), the data that produces the workflow can also be stored in the database. Role rights management can also be done through data-driven. In fact, if the process is data-driven, you can put considerable responsibility on the user to maintain their own workflow process.

3) consider various changes

Consider which data fields may change in the future when designing the database.

For example, the surname is the case (note that it is the surname of Westerners, such as the surname of a woman's husband after marriage, etc.). Therefore, when setting up a system to store customer information, the last name field is stored in a separate data table, and fields such as the start date and the end date are appended, so that changes in this data entry can be tracked.

Principles of field design

4) 3 useful fields that should be added to each table

 dRecordCreationDate, which defaults to Now () under VB and GETDATE () under SQL Server

 sRecordCreator, which defaults to NOT NULL DEFAULT USER under SQL Server

 nRecordVersion, the version mark of the record; helps to accurately explain why null data is present or lost in the record

5) use multiple fields for addresses and phones

It is not enough to describe the street address with just one line of record. Address_Line1, Address_Line2, and Address_Line3 can provide more flexibility. Also, it is best to have your own data sheet for phone numbers and email addresses, with their own types and tag categories.

6) use role entities to define columns that belong to a category

When you need to define things that belong to a specific category or have a specific role, you can use role entities to create specific time relationships, so that you can achieve self-documentation.

For example: use PERSON entities and PERSON_TYPE entities to describe people. For example, when John Smith and Engineer are promoted to John Smith, Director and finally climb to the high position of John Smith CIO, all you have to do is change the key value of the relationship between the two tables PERSON and PERSON_TYPE, while adding a date / time field to know when the change occurred. In this way, your PERSON_TYPE table contains all the possible types of PERSON, such as Associate, Engineer, Director, CIO, or CEO. Another alternative is to change the PERSON record to reflect the change in the new title, but it is impossible to track the exact time of a person's location.

7) choose numeric type and text type as much as possible

Special care should be taken with smallint and tinyint types in SQL. For example, if you want to look at the total monthly sales and the total field type is smallint, you cannot calculate if the total exceeds $32767.

Text fields of type ID, such as customer ID or order number, should be set to be larger than generally thought. Assume that the customer ID is 10 digits long. Then you should set the length of the database table field to 12 or 13 characters. However, this extra space can increase the size of the database without having to reconstruct the entire database in the future.

8) add delete tag field

Include a Delete tag field in the table so that rows can be marked for deletion. Do not delete a row individually in a relational database; it is best to use a clean data program and carefully maintain the integrity of the index.

3. Select keys and indexes (database logical design)

Key selection principles:

1) 4 principles of key design

 creates a foreign key for the associated field.

All keys in  must be unique.

 avoids using compound keys.

 foreign keys are always associated with unique key fields.

2) use the system-generated primary key

When designing the database, the key generated by the system is used as the primary key, which actually controls the index integrity of the database. In this way, the database and non-manual mechanisms effectively control access to each row in the stored data. Using system-generated keys as primary keys has another advantage: when you have a consistent key structure, it's easy to find logical flaws.

3) do not use the user's key (do not make the primary key updatable)

When determining which field to use as the key for the table, be sure to be careful about the field the user is going to edit. In general, do not select a user-editable field as the key.

4) optional keys can sometimes be used as primary keys

By further using the optional key as the primary key, you can have the ability to build a strong index.

Principles for the use of indexes:

Indexing is one of the most efficient ways to get data from a database. 95% of the database performance problems can be solved by index technology.

1) the logical primary key uses a unique grouped index, a unique non-grouped index for the system key (as a stored procedure), and a non-grouped index for any foreign key column. Consider how much space the database has, how the tables are accessed, and whether these accesses are used primarily for reading and writing.

2) most databases index automatically created primary key fields, but don't forget to index foreign keys, which are also frequently used keys, such as running a query to show a record of the main table and all associated tables.

3) do not index memo/note fields, do not index large fields (with many characters), this will make the index take up too much storage space.

4) do not index commonly used small tables

Do not set any keys for small data tables, especially if they often have insert and delete operations. Index maintenance for these insert and delete operations may take more time than scanning tablespaces.

4. Data integrity design (database logic design)

1) Integrity implementation mechanism:

Physical integrity: primary key

Referential integrity:

Delete data from the parent table: cascaded deletion; restricted deletion; null value

Insert data in parent table: restricted insert; recursive insert

Update data in parent table: cascading update; restricted update; null value

DBMS can implement referential integrity in two ways: foreign key implementation mechanism (constraint rules) and trigger implementation mechanism.

User-defined integrity:

NOT NULL;CHECK; trigger

2) enforce data integrity with constraints rather than business rules

The database system is used to realize the integrity of data. This includes not only the integrity achieved through standardization but also the functionality of the data. Triggers can also be added when writing data to ensure the correctness of the data. Do not rely on the business layer to ensure data integrity; it does not guarantee the integrity between tables (foreign keys) so it cannot be imposed on other integrity rules.

3) mandatory indication of integrity

Remove harmful data before it enters the database. Activate the indicated integrity feature of the database system. This keeps the data clean and forces developers to spend more time dealing with error conditions.

4) use lookup to control data integrity

The best way to control data integrity is to limit users' choices. Whenever possible, users should be provided with a clear list of values to choose from. This will reduce errors and misunderstandings in typing code while providing data consistency. Some public data are particularly suitable for finding: country codes, status codes, and so on.

5) adopt view

To provide another layer of abstraction between the database and the application code, you can create a special view for the application without requiring the application to access the data table directly. This also gives you more freedom when dealing with database changes.

5. Other design skills

1) avoid using triggers

The function of triggers can usually be implemented in other ways. Triggers can become interference when debugging a program. If you do need to use a trigger, you'd better focus on documenting it.

2) use commonly used English (or any other language) instead of coding

It is best to sort by English name when creating drop-down menus, lists, and reports. If you need to code, you can attach the English that the user knows next to the code.

3) Save commonly used information

It is useful to have a table dedicated to general database information. Store in this table the current version of the database, recent checks / fixes (for Access), the name of the associated design document, customers, and so on. This implements a simple mechanism for tracking databases, which is especially useful for non-client / server environments when customers contact you when they complain that their database does not meet the desired requirements.

4) include version mechanism

A version control mechanism is introduced into the database to determine the version of the database in use. Over time, the needs of users will always change. Eventually, the database structure may be required to be modified. It is more convenient to store the version information directly in the database.

5) documentation

Document all shortcuts, naming conventions, restrictions, and functions.

Use database tools that annotate tables, columns, triggers, etc. Useful for developing, supporting, and tracking changes.

Document the database, either inside the database itself or separately. In this way, when you go back to the second version after more than a year, the chances of making mistakes will be greatly reduced.

6) testing, testing, repeated testing

After establishing or revising the database, you must test the data fields with the data newly entered by the user. Most importantly, let the user test and work with the user to ensure that the selected data type meets the business requirements. Testing needs to be done before putting the new database into the actual service.

7) check the design

A common technique for checking database design during development is to check the database through the application prototypes it supports. In other words, for each prototype application that ultimately expresses the data, make sure you check the data model and see how to extract the data.

III. Database naming specification

1. Naming of entities (tables)

1) name the table after a noun or noun phrase, determine whether the table name is plural or singular, and define simple rules for the alias of the table (for example, if the table name is a word, take the first four letters of the word; if the table name is two words, take the first two letters of each word to form a four-letter alias. If the name of the table consists of three words, take one from each of the first two words and then take two more letters from the last word, resulting in a 4-letter alias, and so on.

For a work table, the table name can be prefixed with WORK_ followed by the name of the application that uses the table. In the naming process, the abbreviations can be cobbled together according to the semantics. Note that because ORCLE unifies field names into either uppercase or lowercase, it requires an underscore.

For example:

Defined abbreviation Sales: Sal sales

Order: Ord order

Detail: Dtl details

Then the sales order schedule is named: Sal_Ord_Dtl

2) if the name of a table or field has only one word, it is recommended that you use complete words instead of abbreviations.

For example:

Defined abbreviations Material Ma items

The item list is named Material, not Ma.

But the field item code is: Ma_ID; instead of Material_ID

3) all tables that store the list of values are prefixed with Z

The goal is to sort these values list classes at the end of the database.

4) the naming of all redundant classes (mainly cumulative tables) is prefixed with X

Redundant classes are fields or tables added when the database is not standardized in order to improve the efficiency of the database.

5) the associated class is named by connecting the two basic classes with an underscore, followed by a prefix R, followed by an alphabetical list of two table names or abbreviations of table names.

The association table is used to save many-to-many relationships.

If the associated table name is greater than 10 letters, the original table name must be abbreviated. If there is no other reason, it is recommended to use abbreviations.

For example, if the table Object has a many-to-many relationship with itself, the table that saves the many-to-many relationship is named: R_Object

There is a many-to-many relationship between tables Depart and Employee;; then the associated table is named R_Dept_Emp

two。 Naming of the attribute (column)

1) use meaningful column names, and the columns in the table should adopt a set of design rules for keys. Each table will have an automatic ID as the key, and the logical key will be defined as the first set of candidate keys. if it is a code automatically generated by the database, it will be uniformly named: ID; if it is a custom logical code, it will be named by the abbreviation plus "ID". If the key is a numeric type, you can use the _ NO suffix; if it is a character type, you can use the _ CODE suffix. Standard prefixes and suffixes should be used for column names.

Example: sales order number field name: Sal_Ord_ID; if there is also a database-generated automatic number, it is named: ID.

2) all attributes are suffixed with the relevant type, and note that if other suffixes are needed, they are placed before the type suffix.

Note: the data type is a text field, and the type suffix TX may not be written. Some fields with obvious types can be left out of the type suffix.

3) naming with prefix

Using a uniform prefix for the column names of each table greatly simplifies the writing of SQL expressions. This does have drawbacks, such as breaking the power of the automatic table join tool, which associates public column names with certain databases.

3. Naming of views

1) views are prefixed with V, and other naming rules are similar to those of tables.

2) naming should reflect the function of each view as far as possible.

4. Naming of triggers

The flip-flop is prefixed with TR, the flip-flop name is the corresponding table name with suffix, Insert flip-flop with "_ I", Delete flip-flop with "_ D", Update flip-flop with "_ U", such as:

TR_Customer_I,TR_Customer_D,TR_Customer_U .

5. Stored procedure name

The stored procedure should start with "UP_" and be distinguished from the stored procedure of the system. The subsequent part is mainly composed of verb-object form, and each component is divided by an underscore. For example, add the stored procedure of the agent's account to "UP_Ins_Agent_Account".

6. Variable name

Variable names are lowercase, and if they are in phrase form, separate each word with an underscore, such as @ my_err_no.

7. Other considerations in naming

1) none of the above names can exceed the system limit of 30 characters. The length of the variable name is limited to 29 (excluding the identity character @).

2) English characters are used for naming data objects and variables, and Chinese naming is prohibited. Never leave a space between the characters of an object name.

3) keep words carefully and make sure that your field names do not conflict with reserved words, database systems, or common access methods

5) keep the consistency of field names and types, and be sure to ensure consistency when naming fields and assigning them data types. If the data type is an integer in one table, don't become a character in another table.

This is the end of the content of "what are the design methods of the database"? thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.