Database redesign)

There are three sources for Database Design: (1) You can design databases from existing data, such as excel tables. In this mode, you need to consider the normalization of data, (2) design a new database, which needs to build a complete database step by step from building a E-R diagram; (3) database Reset

There are three sources for Database Design: (1) You can design databases from existing data, such as excel tables. In this mode, you need to consider the normalization of data, (2) design a new database, which needs to build a complete database step by step from building a E-R diagram; (3) database Reset

There are three sources for Database Design: (1) You can design databases from existing data, such as excel tables. In this mode, you need to consider the normalization of data, (2) design a new database, which needs to build a complete database step by step from building a E-R diagram; (3) database redesign, in this mode, you need to modify the existing database to meet new data or performance requirements. This blog introduces the third database design mode. In addition, to introduce the database re-design, we need a ready-made database. We will describe the database of an art auction as an example.

1. Database redesign Purpose

Generally, If we design databases from the first two sources according to the standard model, the final results will meet the relevant requirements. Why do we need to redesign the database? There are two answers to this question: first, it is very difficult to design the database correctly, especially from scratch. Even if we have obtained complete user requirements and created a correct data model, it is still very difficult to convert the model into a real database, especially when the model is very complex. More importantly, users' needs have been changing, which directly leads to constant database changes. Therefore, database redesign is an important part of database design and maintenance. The re-design process will exist throughout the lifecycle of the database.

2. Preparations

Before modifying the database structure, we need to make some preparations. The first task is to familiarize yourself with the structure and data stored in the current database to understand the dependency between the structures in the database. Second, before modifying the actual database, we need to test the database with a considerable amount of data. Only after the test database is correct can we actually modify the operational database ). Finally, you must back up and operate the database.

3. Dependency Diagram

Before modifying the database structure, we also need to construct a dependency graph to describe the dependencies between different database structures. When we modify different database structures, other structures will be affected. The dependency graph shows which structures will be affected by modifying a structure. Describes the dependency diagrams of different structures in the database.

Assume that you need to modify the artist table, which depends on its table work. The trigger Trans_checkSalesPrice and view ArtistWorkNetView also need to be modified. According to the actual situation, modifying the work of a table may also recursively cause the structure dependent on it to be modified. It can be seen that the database re-design is a very complex process. However, modifications to the subnode usually only result in changes to the subnode, which is unlikely to be modified.

The database redesign usually involves three changes: tables or relations, columns, and bases. The following describes the changes in detail. In addition, the design of the database involved in this blog is limited to the extent that the database data range is not large enough to be replicated.

4. database table modification 4.1 table name Modification

Modifying a table name is not as simple as you think, and may lead to a series of structure modifications. For example, to change the name of a work table to WORK_VERSION2, perform the following steps:

1) Use the create table WORK_VERSION2 statement to CREATE a new TABLE. In work, workID is a surrogate key. In WORK_VERSION2, this primary key is not set as a surrogate key. Copy the constraint in the work table to the new table. Modify the name of the constraint. Otherwise, a conflict is prompted.

2) Add the data in the work table to the new table using the insert into statement. The add statement contains all the columns of the work.

3) modify the workID of the WORK_VERSION2 table to the surrogate key. The modification method varies with database products.

4) modify the trigger Trans_checkSalesPrice and view ArtistWorkNetView to change the old table name work to the new table name WORK_VERSION2.

5) Delete the old table work. When deleting a table, you must first Delete the foreign key constraint in the table trans and then delete the work.

6) Add a new foreign key constraint to the trans table to point it to the workID of the new table WORK_VERSION2.

4.2 Add a new table or link

There is usually no difficulty in adding a new table or link. Simply use create table to create a new table and attach Various constraint Values. The only exception is that if the new table is the parent node of an existing table (the new table and the existing table have the maximum base relationship of 1: N ), we need to add a foreign key constraint to the existing table.

4.3 Delete a table or link

To delete a table, you must first Delete the foreign key constraint that depends on the primary key of the table, and then delete the table. Of course, during the deletion process, views and triggers should also be modified or deleted based on the dependency graph.

5. Modify columns in the database table

Column modification is essentially a database table modification, but it is introduced separately because it contains a lot of content.

5.1 column name Modification

If a column is modified in the form of a nonkey, it usually has no effect, but you still need to check whether a trigger or view depends on the column. If yes, you need to modify the corresponding column name. If the primary key is modified, in addition to checking the view and trigger, we also need to modify the corresponding foreign key name.

5.2 add columns

Add a column that can be null directly using the alter table add column statement. During database operation, we can modify the attributes of this column at any time, such as setting the default value. However, the Set default value only exists in the row to be added. The previously added row value may still be null.

The alter table add column statement is directly used to add a column not null for the first time. However, we cannot set it to not null because there are already many rows in the table. If we set not null directly, an error is returned. Then update all the exercises whose values are not null, and then use the SQL statement ALTER TABLE ALTER COLUMN to set the COLUMN attribute to not null.

5.3 Delete Columns

The direct deletion of nonkey columns is normal, but you still need to pay attention to views and triggers. If you delete a foreign key, you must first Delete the constraint corresponding to the foreign key, and then delete the column corresponding to the foreign key.

Deleting a primary key is complicated. For example, to delete the primary key of the work table and replace it with the new primary key, perform the following steps:

1) First Delete the foreign key constraint WorkFK In the table trans;

2) Delete the primary key constraint WorkPK In the table work;

3) use the column (Title, Copy, ArtistID) to create a new primary key constraint WorkPK;

4) create a foreign key constraint WorkFK dependent on the Title, Copy, ArtistID in the work table in the trans table;

5) Delete the workID column.

5.4 modify the data type or constraints of a column

You can run the alter table alter column command to modify attributes of a COLUMN. If you change a column from null to not null, make sure that all columns are not null. Conversion between different data types may lead to data loss. However, it is normal to convert the numeric, time, and currency type to char or varchar. In turn, it may not be allowed by DBMS.

You can use the alter table add constraint and alter table drop constraint statements to ADD or delete a CONSTRAINT.

6. Modify the Cardinalities 6.1.

Modify the minimum base of the parent, that is, let the child table have or do not have a parent, that is, determine whether the child foreign key is null. For example, for a 1: N relational DEPARTMENT to EMPLOYEE, the foreign key DepartmentNumber appears in employee. Modify whether to specify department to modify the null state of DepartmentNumber.

If you change the minimum base number from 0 to 1, you need to set the foreign key to not null. In this case, make sure that the existing foreign keys are not null. Otherwise, we need to update the data first. The method for modifying Foreign keys varies depending on different DBMS products, but you can follow the steps below:

1) Delete the old foreign key constraint;

2) set the foreign key column as not null;

3) Add a new foreign key constraint.

In the preceding example, the SQL statement for modifying the minimum base is as follows:

ALTER TABLE EMPLOYEE    DROP CONSTRAINT DepartmentFK;ALTER TABLE EMPLOYEE    ALTER COLUMN DepartmentNumber Int NOT NULL;ALTER TABLE EMPLOYEE    ADD CONSTRAINT DepartmentFK FOREIGN KEY (DepartmentNumber)        REFERENCES DEPARTMENT (DepartmentNumber)        ON UPDATE CASCADE;

Cascade attributes must be determined based on specific services.

Child end

You need to use the trigger or application code to set a non-zero minimum base on the child end. Therefore, you also need to use the trigger to change the minimum base from zero to one. If you change the minimum base number from 1 to 0, delete the trigger directly.

6.2 increase the maximum base: to 1: N

Assume that there is a relationship between EMPLOYEE and PARKING_PERMIT. Currently, the default value of employee is parent, because the foreign key is in parking_permit. When changing the relationship between and 1 to 1: N, you need to consider which side is parent (1 ). If the employee is still a parent, you only need to remove the foreign key unique attribute in parking_permit. If parking_permit is changed to the parent end, we need to move the foreign key to the employee and import the data to the employee table. The specific operations are as follows:

1) Add a foreign key PermitNumber that can be null to the employee;

2) import data from parking_permit to the employee table:

UPDATE EMPLOYEE    SET EMPLOYEE.PermitNumber =        (SELECT  PP.PermitNumber         FROM  PARKING_PERMIT AS PP         WHERE  PP.EmployeeNumber = EMPLOYEE.EmployeeNumber);

3) Delete the foreign key employee number in parking_permit;

4) Add a foreign key constraint to the employee;

5) modify the corresponding trigger and view.

1: N to N: M

It is easier to change the 1: N relationship to the N: M relationship. You only need to create a new table (interp table) that only contains two table primary keys ), then, the combination of the two primary keys is used as the primary keys of the new table, each of which is dependent on the Foreign keys of the other table. import data from the child table, and then delete the foreign key constraints in the child table. Finally, modify the trigger and view to apply the new table.

6.3 reduce the maximum base (data loss exists)

To reduce the N: M relationship to a 1: N relationship, we need to add a foreign key in the child table and then import the foreign key data from the interp table. Modify the corresponding trigger and view, and delete the interp table. To reduce the relationship between 1: N to, we need to change the foreign key data in the child table to 1, and then change the foreign key constraint to unique. In both cases, you must determine how to delete data.