Design specification of relational database table structure--A brief discussion

database table structure Design specification-Briefly, why is it, because the main point of view is from the original public account of an article, slightly added some of their own views.

Who is going to design the database?

Certainly is the concrete development engineer to carry on, develops schoolmate's words, first business familiarity degree is higher, second unifies Oo and Orm's thought, can have the good use relational database characteristic. If the DBA classmate, although the database itself to know more, but for the business less understanding, it is difficult to have a more objective design. But the business on-line or run-time, need DBA classmate can join in, for some performance points and unreasonable points to optimize, colleagues can also on-line, for SQL Review, in the bud stage, to eliminate the high-risk SQL. Such collaboration should be more efficient.

What are the 12 rules that the parent of the relational database summarizes (find a few useful lists)?

Information law all information in a relational database is represented in a single way-the value in the table.

Ensure that the access law relies on a combination of table names, primary key values, and column names to guarantee access to each data item (this is actually a readability constraint for a database table).

Null values are systematically processed to support null values (NULL), and null values are handled in a systematic manner, and null values do not depend on the data type.

The physical independence of data regardless of how the data in the database changes in the way it is stored or accessed, both the application and the terminal activity maintain a logical invariance.

Logical independence of the data when the table is made theoretically without compromising the information changes, both the application and the terminal activity remain logically invariant.

The integrity of data integrity constraints that are specific to a relational database must be defined in a relational database sub-language and can be stored in a data directory, rather than in a program.

Distribution independence regardless of whether the data is in physical or distributed storage, or at any time changing the distribution strategy, the RDBMS's data manipulation sub-language must be able to keep the application and terminal activity logically invariant.

Do you want logic like triggers in the database?

In the application System division, the application system is used to write the business logic, carries all the business logic, then the database is used to persist the data, simultaneously provides the query function, at this time if has the business logic redundancy in the database, one database does the thing which he is not good, and secondly the business logic is scattered, causes the subsequent maintenance cost to become bigger , if the database is the system of data storage, then let him try to keep a single bar.

That data doesn't fit in a relational database?

1, the picture of the latter file, not suitable for the relational database, you can find third-party storage media, and then the database storage path;

2, temporary data or short life cycle data, such as the session data in Web applications, the survival period is very short, there is no need to persist in the database storage;

3, log files and other information, log file retention, for the system to run traceability and traceability, play a big role, but not suitable for storage in the database, you can log the analysis of the results or statistical calculation to retain;

The order of the columns in the database, how is it better?

The order of the columns is irrelevant to the table, but it is customary to sort the columns in the order of "primary key + foreign KEY + Entity Data + non-Entity data", which obviously makes the column more readable.

?

What about the paradigm and inverse paradigm?

Canonical description of the paradigm, each field contains only the smallest information attributes, while the model contains primary keys, non-primary key fields rely on primary keys, model non-primary key fields cannot be interdependent.

The advantage of the normalization model is that the data is not redundant, the update is very easy, the disadvantage is that the multi-model join situation, the performance will be problematic.

A bit of the inverse paradigm model is that the data is very readable at the storage level, and it is determined that the update needs to maintain redundant data.

The purpose of the paradigm design is to eliminate duplication of data, avoid writing unnecessary code to synchronize data, keep the table thin, and reduce the number of read operations that are required to read data from one table, maximizing the use of clustered indexes, allowing for more optimized data access and connectivity, Reduce the number of indexes used per table because the cost of maintaining indexes is high.

Database primary key, is the logic that uses the self-increment or the system to specify?

This can be based on the business scenario, in most cases, the use of database self-increment as the primary key, the problem is not big, but if faced with the situation of the sub-database table, it is necessary to have a certain logic, such as the same table, 00 and 01 Even a physical table, the primary key of the database, is not repeatable. In addition, each table requires a natural key that is meaningful to the user, and may not be able to find such an item in a particular case, and a composite key can be used at this time. This key is not used as a unique identifier in the program, but can be used when querying directly against a database. This system generation plus business meaning of two combinations, in the system and business design to make a trade-off.

Database foreign key, is only in the application system maintenance, or database level do strong check (from know: Mysqlops)?

Whether foreign keys are used to look at business scenarios, as well as development costs, are roughly listed at what time and when not suitable for use:

1. Internet industry applications do not recommend the use of foreign keys: large user volume, high concurrency, this database server can easily become a performance bottleneck, especially by the IO capability limit, and can not easily expand horizontally; If you put the control of data consistency into a transaction, you let the application server bear this part of the pressure, and the reference server is generally easy to do the horizontal scaling;

2. Traditional Industries

1>. The number of software applications is limited, in other words, controllable;

2> The data volume of the database server is generally not very large, and the active data is limited;

Combined with the above 2 sentence description, that is, the performance of the database server is not a problem, so do not worry about the performance of the problem, in addition, the use of foreign keys can reduce development costs, with the help of the database product itself trigger can achieve the data consistency and update between the table and the associated table, and finally, the use of foreign keys, can also be done by the Developer and Database Designer Division of labor, you can take more workload for programmers;

How to combine the business scenario to do database design?

Common business scenarios can be listed below,

1, whether the table structure is the core business data, is the core or procedural, which determines the attention of the table;

2, the reading and writing of database tables, and the proportion of reading and writing;

3, the amount of data and the increase in the situation, such as how much data added each day, save the past days of data;

4, the data of the hash, the table field is included in a lot of data, but there is not much data in a record, or all have, this will affect the performance of the situation;

How to improve the efficiency of insert?

1, you can consider the use of multi-value insertion, similar to this form, insert into xxx_table () values (), (), (), but note that SQL statements form the size of the text do not exceed the database limit;

2, commit transactions, can be submitted in batches, for example, if the ORM framework uses Ibatis, you can consider using the Batchinsert function, such SQL statements are separate, but the submission is a one-time;

3, the overall, is to reduce the interaction between the database and avoid unnecessary network overhead;

Ps:

Reference information from public account information is as follows

"Database development" to share database related technical articles, tool resources, selected courses, hot information, welcome attention. Number: {Dbdevs}

http://iamzhongyong.iteye.com/blog/2192178

Code for structural design of relational database tables-a brief discussion (turn)