5.2.6 Normalizing a table [Row store table]
When defining a table as a row store table, consider the normalization of the table.
Moving redundant data in a table to another table is called table normalization. To improve the storage efficiency of tables, normalize tables. By repeatedly normalizing tables, you can configure a complicated database into an optimum form.
- Organization of this subsection
(1) Improving the storage efficiency of tables
When a table contains multiple columns with similar information, normalize the table by dividing it into multiple tables so each table contains only one of the columns with similar information. This operation improves the data storage efficiency in the table.
The following figure shows an example of normalizing a table containing multiple columns with similar information.
|
|
![[Figure]](GRAPHICS/F0000140.GIF)
- Explanation
-
Before the STOCK table is normalized, column PCODE corresponds to both column PNAME and PRICE on a one-to-one basis. As a result, there is redundant information in these columns. In this case, you can break up the STOCK table and create a separate PRODUCT table with columns PCODE, PNAME, and PRICE.
By doing this, the PRODUCT table does not contain redundant information in its columns PCODE, PNAME, and PRICE.
(2) When some columns have high access frequency while others do not
When a table contains some columns that are accessed frequently and others that are accessed less frequently, you normalize the table into a table containing the columns with high access frequency and a table containing the columns with low access frequency.
The following figure shows an example of normalizing a table containing columns with high access frequency and columns with low access frequency.
|
|
![[Figure]](GRAPHICS/F0000141.GIF)
- Explanation
-
Before the STOCK table is normalized, it contains columns with high access frequency (PNO and QUANTITY) and columns with low access frequency (PNAME and PRICE). In this case, you can normalize the STOCK table into a table containing only the columns with high access frequency (the STOCK table) and a table containing only the columns with low access frequency (the PRODUCT table).
This normalization improves the overall processing efficiency.