[Question] How can I cope with the increasing number of columns in a relational database?

It may be difficult to understand what I want to say by reading the article. Let's first describe the problems encountered in the project. In our project, a table like this is used to save various resources, such as resource 1 and resource 2, which are identified by ResGenre. We can think of resource type as an abstract concept. Resource 1 and resource 2 are all resource subclasses. All of these

It may be difficult to understand what I want to say by reading the article. Let's first describe the problems encountered in the project. In our project, a table like this is used to save various resources, such as resource 1 and resource 2, which are identified by ResGenre. We can think of resource type as an abstract concept. Resource 1 and resource 2 are all resource subclasses. All of these

It may be difficult to understand what I want to say by reading the article. Let's first describe the problems encountered in the project.

In our project, a table like this is used to save various "resources", for example, resource 1, resource 2 ...... And so on. It is identified by ResGenre.

We can think of resource type as an abstract concept. Resource 1 and resource 2 are all resource subclasses. At first, all these child types only have ResId, ResName, and other fields. A ResInfo table can meet the requirements. But we all know that projects are ever changing, and the only thing that remains unchanged is "change ". As the business develops, there may be more and more resource subtypes. The headache is that the fields in the original table cannot meet the requirements, and the fields of each subtype are constantly expanding, the biggest headache is that they add different fields ...... What should I do?

There are several possible methods:

Single Table inheritance

The single-Table inheritance means that all fields are stored in a table. When a field is added, the original table is expanded.

The advantages of this method are simple and crude. It is desirable when there are few subtypes and few special attributes of subtypes. However, if there are more than 10 sub-types, and there are many sub-type fields, the disadvantage is also obvious: too much redundancy, a row of records has many attributes unrelated to the current subclass, in addition, page management is complicated. Every time a field is added, all child types are affected.

Create a table for each child Type

Add a table by adding a child type.

......

Two sub-types of storage are completely independent. Every time you add a table page, you need to re-manage a table. This method is not very good when there are many sub-types.

Multi-Table inheritance

There are both base tables and child-type tables, just like inheritance in object-oriented systems.

Multi-Table inheritance can reduce field redundancy. However, when many sub-types are the same, there are many tables, which makes it troublesome to manage them.

Semi-structured data model

If there are many child types or new fields must be added frequently, you can use a BLOB column to store data in XML or JSON format.

Property is an attribute column. It can use Json to store additional fields. It also contains field names and values.

{"Field1": "Value1", "Field2": "Value2 "}