A Difficult Problem in Database Design

  mongodb, question

The problem is more complicated, so simplify the description as much as possible.

For example, a data table for storing articles uses mongodb for data storage.

1. There are uncertain columns, such as click rate, content, title, reply, user uid. The number of benefits is uncertain, which may be more than a dozen or hundreds. May increase or decrease, is dynamic.

2. The number of stores in each column is uncertain. For example, there may be millions or only one to reply to this field.

3. Each field needs to be able to store versions. For example, in the field of content, there may be multiple versions, version 1, version 2, and version 3. The number of versions is uncertain.

This is how I designed it

1. data table: data. Store the basic information of the article. For example, the time when the article was added and the status of its publication.
2. field table: Field. Store specific field content, such as reply, content, title.
3. There is also a data table: version. Stored version. The specific content of each version is still stored in field.

Current problems:

1, storage is no problem. For example, the reply and content of an article in i query can be queried.
2. There are performance problems. For example, paging, one page lists 25 items. Each item needs to list some data to the user in the list. Such as title, content, time, etc. About 7 fields. Add up to: 25*7=174 queries, which took about 10 seconds.
If this problem is solved, it may be necessary to write a copy of the data in advance and put it into the data table. This is the anti-normal form. For example:

 Title:' title',
 Content:' content',

Store such redundant data in the Data table.

3. Search problems. I still feel that there are some functional limitations and performance problems.

On the whole, the design is very complicated, the code is very tired, and the problems emerge one after another.
I don’t know whether the database design problem or mongodb is not suitable for storing such data.
If it were you, how would you design this database?

1. there are uncertain columns-> this is not necessary, the necessary columns should be relatively fixed, if not fixed, from dozens to hundreds, how do you present the data?
You are not sure which columns there are and how to write your front page.
If you really want to have some optional fields, you can uniformly store them in one field or another table.

2. for example, if you reply to this field, there may be millions-> the contents of the reply cannot be the same as the table in which the article is stored, right?
Of course, the reply requires a separate table.
When a field needs to store more than one result, the contents of this field will generally be independent into another table.

3. Although mongodb does not have as many restrictions as relational databases, I think the basic design should be based on relational databases.
When you put everything in one table, you should query in batches faster, but it may be very troublesome when you want to search accurately, and it may also be very troublesome to write.
For example, if the content of your reply is in the “reply” field in the article table, each time you add a reply, you have to find out the original data first, then take out the content of the reply data, and you also need to cycle through to see if the content of the reply is repeated.