SQL Server Database Learning notes-factors to consider when designing tables

Designing a database is actually a table in the design database. What should you pay attention to in order to design good one database? One purpose, 8 recommendations.

A purpose "to minimize the number of tables, each table in as few columns as possible, a reasonable table structure."

8 Recommendations:

First, the first thing to consider is what is the main role of our database? What data does it contain at least? Which entity objects does this data belong to separately? What kind of relationships exist between objects? such as the news article management system database, it to store the data at least include: article classification, article title, publication time, author; and since it is a management system, then there will be someone to add, delete or modify the article, then extend the administrator, there is an administrator has an account, password If you want to comment on the article, you have to have the title of the comment, the content of the comment, the name of the reviewer, the time of the comment, and so on. So much data to store, how to categorize it? How do you sort the relationships between the other categories? This requires the use of tools.

Second, the E-R model. There are many tools for building e-r models, even paper and pens are a tool. The E-r graph is a lot of drawing, its main function is to classify all the data to be stored in the database, organized into a classification, this classification is called the entity, and the data that is classified into this classification is called the entity attribute. There are associations between different entities, such as who publishes articles between articles and administrators, and the relationship between articles and comments that a comment is part of the article, which must be reflected in the E-R model.

Thirdly, each entity is a table, and the attribute of the entity is the column of the table, then the problem is out, what kind of column should be what kind of data type, such as the title of the article should use what data type? If you use NCHAR (50), then look at the article title with NCHAR (50) to save there is no problem. First of all, what do we say about using the nchar type? The first is a type that begins with N in Unicode characters, followed only by the fact that the length of the data is basically the same. But does the title of the article have a fixed length for each one? And the most deadly of the char types is shown, as long as his data, as long as the length is not enough, automatically fill the space. The problem is that if this column of 100,000 rows of data has only 40 characters, then 10 spaces are added to each row, so that the space for 100,000 * 10 characters is actually wasted. The larger the size of the database, the efficiency of data processing must be affected. Again, such as user registration age problem, certainly preferred tinyint, because the current human age can not be more than 255 years old, and this type only accounted for 1 bytes of space, if habitually used int type, I see no problem, just wasted 3 bytes of space, because int is 4 bytes of length. Sum up, do not waste space, nor excessive space-saving, the right amount of line.

The null and default values are allowed. What does that mean? The first thing to be clear is what is a null value and what is the default value. The null value here is neither 0 nor a null character, but is unknown, denoted by null. This is problematic, first, if the null value in the variable-length column itself does not occupy space, but the column in which it resides actually occupies space. Null is special, and the database has extra operations on the null field, so if there are more null fields in the table, it will affect the performance of the database; there is one thing we can not think of, but we will encounter in the future, the null field would bring some minor problems to programming, such as making some bugs. So, in a word, as little as possible to allow the column empty, if must be allowed to also try to put it back. The default value is used when the user may not be willing to fill in or fill out the information. For example, the user's registration time.

Five, the problem of the primary key. Each table requires a primary key to identify a unique piece of data.

VI, constraints and rules. To ensure the complete validity of the data, once the rules for the constraints are defined, the data that satisfies these criteria can be inserted into the database. For example, asked to register the gender of the membership is either male, or female, never allow the third situation, such as age can only be 18~80岁, other registration is not.

VII, FOREIGN key relationship. Setting foreign keys saves space, facilitates programming, and expands programs.

Consider whether to use an index. An index is also a database object, which uses indexes on which columns, does not apply indexes to which columns, whether to use clustered or nonclustered indexes, if full-text indexes are used, and so on. Many questions need to be considered seriously.

SQL Server Database Learning notes-factors to consider when designing tables