How should we design the database (4) index & writing plan how should we design the database (3) MongoDB development and learning (1) What's new, getting started with the classic MongoDB Development in Practice [no basic learning, complete ASP. n

In fact, I have been preparing the basic materials for another blog post, but I chatted with my friends and asked me what I was doing recently. I said I was working on the System Log Module, after talking with him, this blog came into being.

My blog directory: Index & writing plan

 

All data can be expressed as follows: ID, table name, column name, and value

For example, there is a data entry into the user table:

ID (guid, int for ease of understanding)	Username	Password	Email
1	Crazyjinn	123456	CrazyJinn@W.C

This record can be converted:

ID	Table Name	Column name	Value
1	User	Username	Crazyjinn
1	User	Password	123456
1	User	Email	CrazyJinn@W.C

You can see this design in a variety of places with high requirements on flexibility. For example, in the message "How should we design database (3)", some park friends mentioned similar design.

Of course, this method is not very efficient; however, you can add the clustered index to the table name, then add the non-clustered index to the column name, and then split it horizontally. If you are in a good mood, then perform a read/write splitting, I believe that it is theoretically acceptable for applications with non-high concurrency and tens of millions of data.

 

Now, enter the text

Now we need to create a general Log Module.

Since it is common, it means that the flexibility is very strong, because you do not know what the data structure to be recorded in the log is.

In addition, there is a very abnormal demand for me: there must be a rollback function. But let's leave him alone.

Based on the previous discussions, we can easily design a log table.

ID

Table Name

Column name

Type (create \ edit \ delete)

Value

Last modification time

If you are dealing with all the irrelevant issues, this is enough. But you must know that the most important thing about RDBMS is the relationship processing.

For example, to log two tables:

On the Log Surface designed above, such a one-to-many relationship cannot be stored. We have many-to-many relationships.

Of course, you can expand the log table to store one-to-many/many-to-many relationships. Although I am not sure whether it can be done, I have not considered this in depth. If you think of a good design, please leave a message to discuss with me

 

Let's rethink the nature of the log module:

1. A large amount of data

2. Only a large amount of data (not associated with other modules, pure data)

This scenario reminds me of nosql. Here, we use MongoDB for implementation. For more information about MongoDB, see the following two articles:

Xiang shu: MongoDB development and learning (1) getting started, classic getting started

Fish Li: apsaradb for MongoDB practice development [zero-basic learning, with a complete Asp.net example]

MongoDB uses bson to store data. You can simply regard bson as JSON. As we all know, JSON is a very easy-to-expand, loose data format. Based on JSON's easy-to-expand feature, we can design log tables in this way.

Logid

ID

Table Name

Content (the stored content includes the last modification time, modification type, and new modification ID)

If I modify the user table six times, the log data is as follows:

We focus on the three pieces of data marked in red boxes.

The first data entry. contactlist is an array type with a length of 0. This indicates that no corresponding contact exists.

The length of the second data record is changed to 1, which indicates that a contact Association is added to the user. Let's take a full look at the second data record:

We can see that a complete contact is included.

The third data contactlist is null, which indicates that I modified the user information somewhere else. This modification does not involve contact, so it is saved as null. When we retrieve data, if a list is found to be null, We Need To Recursively search for data not null. For example, here I want to find the contactlist of the second data.

For your convenience, I paste the JSON below. You can read the above picture.

{
  "Content" : [{
      "_id" : new BinData(3, "mtonv7sMCkewsMIjWZ9/qg=="),
      "Username" : "1",
      "Password" : "1",
      "Number" : 1,
      "LastModified" : new Date("27/11/2012 10:28:18"),
      "ContactList" : [{
          "_id" : new BinData(3, "1QwcXGKedUCO27QprZB26Q=="),
          "UserID" : new BinData(3, "YyQDfuoj6EuuDNl91leigA=="),
          "Phone" : "Phone1",
          "Email" : "Email1"
        }, {
          "_id" : new BinData(3, "EeWfiFCknkex4H2jEraR/w=="),
          "UserID" : new BinData(3, "YyQDfuoj6EuuDNl91leigA=="),
          "Phone" : "Phone2",
          "Email" : "Email2"
        }]
    }, {
      "_id" : new BinData(3, "Afk3spV0q0uKM+yNs/SHbw=="),
      "Username" : "1 to 2",
      "Password" : "1 to 2",
      "Number" : 2,
      "LastModified" : new Date("27/11/2012 10:35:03"),
      "ContactList" : []
    }, {
      "_id" : new BinData(3, "H/5o2lizmUWkaxAZUgNHzg=="),
      "Username" : "2 to 3",
      "Password" : "2 to 3",
      "Number" : 3,
      "LastModified" : new Date("27/11/2012 10:40:28"),
      "ContactList" : [{
          "_id" : new BinData(3, "7HDyGU2+A02HbQtUFbOo8A=="),
          "UserID" : new BinData(3, "H/5o2lizmUWkaxAZUgNHzg=="),
          "Phone" : "PhoneNew",
          "Email" : "EmailNew"
        }]
    }, {
      "_id" : new BinData(3, "zf2SiYW81kufGO7ZgY5r3A=="),
      "Username" : "3 to 4",
      "Password" : "3 to 4",
      "Number" : 4,
      "LastModified" : new Date("27/11/2012 10:41:34"),
      "ContactList" : null
    }, {
      "_id" : new BinData(3, "N68jDslbU0uvdHJTSq0vIg=="),
      "Username" : "5",
      "Password" : "6",
      "Number" : 7,
      "LastModified" : new Date("27/11/2012 17:14:12"),
      "ContactList" : null
    }, {
      "_id" : new BinData(3, "Fw6OqMNcc0K+rySfgz3dTg=="),
      "Username" : "9",
      "Password" : "9",
      "Number" : 9,
      "LastModified" : new Date("27/11/2012 17:16:15"),
      "ContactList" : [{
          "_id" : new BinData(3, "zfsQRK5A0kGFFcnc5TZ9GA=="),
          "UserID" : new BinData(3, "YyQDfuoj6EuuDNl91leigA=="),
          "Phone" : "PhoneNew",
          "Email" : "EmailNew"
        }]
    }],
  "ModelID" : new BinData(3, "YyQDfuoj6EuuDNl91leigA=="),
  "ModelName" : "User",
  "_id" : ObjectId("50b4254257751f09a02decba")
}

 

In this way, the prototype of a log function will come out.

Put aside

 

PS: I wrote a small demo, but it was messy. If you are interested, you can leave a mailbox. I will send it to you.