Dynamically scalable database model design

In the general database design, we define how many attributes each object has, what is the data type of each attribute, how long it is, whether it is allowed to be blank, and what constraints it has, these definitions are completely static. They are all defined during system creation and cannot be modified dynamically. However, the attributes of an object change rapidly, or the objects and attributes are stored by the user in the system.

In the general database design, we define how many attributes each object has, what is the data type of each attribute, how long it is, whether it is allowed to be blank, and what constraints it has, these definitions are completely static. They are all defined during system creation and cannot be modified dynamically. However, the attributes of an object change rapidly, or the objects and attributes are stored by the user in the system.

In the general database design, we define how many attributes each object has, what is the data type of each attribute, how long it is, whether it is allowed to be blank, and what constraints it has, these definitions are completely static. They are all defined during system creation and cannot be modified dynamically. However, when the attributes of an object change rapidly or the objects and attributes are customized by the user in the system, a database model that can be dynamically expanded is required, to save dynamic data.

For example, to create an e-commerce website, we need to create a commodity table to store the attributes of various commodities to be sold. However, there are various product attributes. Different types of products have different attributes. Therefore, it is impossible to create a static product table to store all the attributes. In this case, you need to create a dynamic database model.

Common Dynamic Scaling database design methods include:

1. Store various data types with strings and read object attributes through row-to-column conversion.

As an example, we can create two tables: "commodity" and "commodity attribute". The commodity table is a common commodity attribute, you can put the public attributes of most commodities such as product names and prices in this table. The product table and the product Attribute Table form a one-to-multiple relationship. The product Attribute Table only needs to define the "property name" and "property value" attributes of a product to save each property of a product.

In this way, each time you read a product, you can read the property set of the product, and then re-bind the property set to the object to temporarily put the object on the page.

The advantage of this approach is that it is flexible and allows you to create countless different attributes for the product to meet the needs of e-commerce for rapid changes and fast online release. The disadvantage is that the statistics will be slow in the future, because it requires row-to-column conversion, and it will be very troublesome if it involves various Join queries.

2. predefine a large number of redundant columns and match the corresponding columns according to the user's object attribute type settings.

If we do not want to convert rows to columns, We can pre-define the data columns. Because we are not sure which data type is the data type, we can define a lot of columns in the table, each data type defines several or dozens of columns. These columns can be empty. If predefined columns are not used, they do not occupy much data space.

In SharePoint 2007 or earlier versions, this method is used to store the list data. The following is the structure of the AllUserData table in SharePoint2007. A dozen to dozens of columns are defined for each data type. You can use this table to store the list data when creating different lists.

The advantage of this database design method is that there will be no row-to-column conversion problems, so the performance is better when you join or output a report. The disadvantage is that there are many columns in a table, most columns are not used in most cases and are difficult to expand. For example, we want to define 17 bit columns, but the system only has 16 columns by default. In this case, you need to use two rows of data in the database to represent one row of List data.

3. Use the XML data type to store dynamic column data.

XML data type is a standard of SQL. Currently, mainstream databases support XML data types. The database provides special syntax for XML to quickly retrieve and operate XML data. In the new version of SharePoint, XML is used to store the content of the user-defined list.

For the product table and product Attribute Table mentioned above, you can also create a product table and add an XML column to the table to store the various attributes of the product. This is a recommended solution.

4. dynamically create tables for user-defined entities.

Another dynamic method is to dynamically create a table in the program. Every time you define an object in the program, you can create a corresponding table according to the user's definition. For example, Microsoft's Dynamic CRM is implemented in this way. You can create a large number of objects in the system and define the relationships between objects. Then, the system creates the corresponding tables and Foreign keys according to the user's definition.

The advantage of this method is that the performance is good. Each entity corresponds to its database table, and there is no large number of redundant columns, and there is no row-to-column conversion problem. The disadvantage is that it is difficult to develop and has high requirements on users. If you want to modify object attributes after creating an object and storing a large amount of data, it will be very difficult.