Database Design-semi-structured storage and database structured storage

Database Design-semi-structured storage and database structured storage

Business scenario: the user fills in some documents, reports them, and approves them. There are several fields in the document that need statistics, and the business is not complicated.

In a seemingly simple scenario, when the developer came up with the PDM design, I was shocked. There were nearly 70 tables, each of which had more than one hundred fields. The developer complained that it took a week to design the database.

Design Scheme 1I asked if I could classify the documents. A type of document is designed as a table, and a field is used to differentiate the document. This will reduce the number of tables. The reply is that it cannot be classified, and solution 1 won't work.

Design Scheme 2Make an Excel template and review it in excel during the approval process. The problem is that there are fields for statistics and it won't work.

Design Scheme 3, The content of the document is saved in blob in JSON format. If you want to make statistics on the field, do the common condition query field, redundant from blob for statistics.

Summary:Solution 3 greatly reduces the workload of developers, but requires a high level of business analysts. It can identify the fields for analysis, statistics, and query. It has lost many advantages of traditional database design. The design is often balanced. This design will undoubtedly reduce the workload, but it is unfriendly to queries and statistics. If you don't care about the workload, I suggest you make the most traditional design.

What optimizations have mainstream relational databases optimized to store unstructured and semi-structured data?

Databases are mainly used to store structured data
Semi-structured data has some storage methods similar to databases, such as XML database management.
Unstructured data is often stored in file systems and seldom stored in databases (for example, a repository must record an image, but only the size and location of the image and other metadata in the database, the actual content of the image is saved in the file system)
Some databases directly store non-/semi-structured data, mainly relying on data compression and data deduplication to optimize storage efficiency.


How can I reasonably plan the physical and logical storage structures of a database when creating a database?

Logical Structure planning is to increase, decrease, or adjust the logical structure to improve the efficiency of applications. It analyzes the optimization of the ORACLE logical structure by designing basic tables, indexing, and clustering. The Optimization of the physical storage structure mainly refers to the rational allocation of physical storage addresses of the logical structure. Although this can not reduce the number of reads and writes to the physical storage, it can make these reads and writes as parallel as possible, reduce disk read/write competition to improve efficiency. You can also perform precise computing on physical storage to reduce unnecessary physical storage structure expansion, thus improving system utilization.