Shulou(Shulou.com)05/31 Report--

This article mainly shows you "what are the experiences of field design in the database". The content is easy to understand and clear. I hope it can help you solve your doubts. Let the editor lead you to study and learn this article "what are the experiences of field design in the database?"

There are many detailed skills in the field design of the database. Let's sort out the past experience in development and make a memo.

The tinyint is-128to128. When the property is set to unsigned. The maximum value is 255. Now you know why you need to set it to the unsigned property. It turns out that it is to maximize the use of the storage space given. If you don't set it. So if your values are all positive. Then the more than one hundred figures of-128 are equivalent to a waste.

Tinyint is automatically set to tinyint (3).

When smallint does not set unsigned, it also looks like more than 30, 000.

Tinytext is 255bytes. About 127Chinese characters are stored. Tinytext is equivalent to varchar type. It is easy to understand if you think of it as this type.

The int type phpmyadmin sets int (10) by default.

Concept correction: I always thought that the 10 here represents the number of digits. Until I wanted to save 1101061021496, the value in the field became: 4294967295. Look, the MySQL manual says:

The display width does not limit the range of values that can be saved in the column, nor does it limit the display of values that exceed the specified width of the column.

The range of int:-2147483648 to 2147483647. It happens to be 10 digits, so it's billions of digits. Database design experience: the value of an order is very large. I'm not sure, if you reach 10 digits, you might as well use the varchar type. Fangwei does not use int, but the varchar type.

Also tell me a lesson from the above: if the values saved in the database become the same. That is, whether I am 1101061021496 or 1101061021569, the result becomes a fixed value, such as 4294967295. Then you can consider confirming whether it is the scope of this field in the database. There have been such problems several times. I just don't know the way of thinking. Caused a lot of time to be wasted.

There is another reason for setting the field to not null: if the column of the mysql table contains null, then the column will not be included in all. That is, it is invalid to use an index. So, to consider the fields that will be indexed in the future, set the field property to not null.

If you want to save NULL, set it manually instead of setting it as the default.

Consider that this field will be searched in the form of like as query keywords in the future. Then define the field as an index. This makes it faster to use like queries.

Taking these into account, what queries are needed in the future. The structure can adapt.

On the design of high-traffic website database, member sub-table or sub-database design considerations:

The primary key should not be set to self-increasing. The consequence of setting it to self-increment is that it cannot be separated on different mysql database servers in the future. For example, because the id number is self-increasing, there may be a situation where the user number is 10005 in both databases.

However, the mysql primary key is automatically set to self-incrementing. You can use another field as the identifier. Not a self-increasing id number. Method: add a field as the row identifier. Specific design: a table to do two fields, one is id as the primary key, self-increasing, the other is uid, as the user's identity.

In program judgment, uid is used as the basis for judging users. Because your data is already in the database. It is more difficult to modify.

Some experience in the Design of Database

1. Follow the [process] order (the operation flow completed by the relevant users at one time)

two。 First list all the attributes (entity (type), attribute, connection)

3. Determine which entity each attribute belongs to.

4.

5. The design of relevant data tables allows for data redundancy (sacrificing space for efficiency), but there must be no contradiction between data items [considerations between engineering and theory]

6. When there are multiple ways of data processing, we should integrate many aspects, consider it, and choose the solution that is most suitable for the current project [not necessarily the best in theory, but suitable and practical]

7. Note: when [Conceptual Model] generates [physical Model], it will put the primary key in 1 as the foreign key in n.

The above is all the contents of the article "what are the experiences of field design in the database?" Thank you for your reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.