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

This article introduces the relevant knowledge of "what are the design skills of the database". In the operation of actual cases, many people will encounter such a dilemma. 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!

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

Create a foreign key for the associated field. 

All keys must be unique. 

Avoid 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 skills 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.