Database Design (2/9): Domains, Constraints and Defaults (Domains, Constraints and Defaults)

Is it completely new for designing and creating a database? It doesn't matter, Joe Celko, one of the most-read SQL authors in the world, will tell you these basics. As always, even the most professional database veteran will surprise them. Joe is Dmbs magazine, the most popular author of the book for many years. He teaches SQL in the United States, Britain, Northern Europe, South America and Africa. He worked for the Ansi/iso SQL Standards Committee for 10 years and made outstanding contributions to the SQL-89 and SQL-92 standards.

For database developers, a clear understanding of SQL data types and domains is a basic requirement, but not a base. If you choose the most appropriate data type, it can avoid many errors. In addition, if you define the data field as precisely as possible by constraining it, you will catch a variety of problems that would otherwise plague the application developer's work.

In the 1th chapter, we name the data elements for what they are and classify them. So now we know that we can't use generic, magical, universal "id" as the data element. It must be an exact name that has a definite meaning in the schema. Better yet, the name should be enterprise-wide, industry-wide or globally prioritized.

In the 2nd chapter, we will decide to put the data into the computer by selecting the appropriate domain. Domain idea to go back to Dr. Codd. Well, actually before this good doctor (good Doctor), because it comes from math. The data types in domain image programming, but involve more. The domain has a valid operator to complete on its value, and there is a data type in addition to it. For example, I can use integer to record the temperature in the database, which is a data type. When I see 100, that's a value. At the same time, I can add, subtract, multiply and divide integers, but it doesn't make sense to do so at a temperature. You can't put your body temperature on the 36° and boil it up. The type of model determines what operations are allowed. But I need to know whether the number is in Fahrenheit (Fahrenheit (°f)), Celsius (Celsius (°c)) or absolute (Kelvin (°K)); That's the unit of measure. Put these together and you have a field.

Standard SQL has the Create DOMAIN statement, which creates a schema object that is not a true domain. It's just an abbreviation we're about to discuss.

SQL has 3 major types of data:

1. Value (numberic)
2. Strings (String)
3. Date (temporal)

The numerical type is divided into precise and approximate. The exact values are integers, SMALLINT, DECIMAL, numeric, and bigint. They hold precise values and have explicit calculation operators. Approximate values are real, FLOAT and double PRECISION (double precision). These are floating-point numbers, they have rounding problems, and recently IEEE floating-point standards are commonly used. SQL is no exception; most other programming languages that can be calculated use all or use some of these types because they embed computer hardware long before the SQL invention. PC and mini computer users are probably not aware of the number of binary decimal (BCD (binary Coded decimal)), but they are part of the commercial PC mainframe over the years. If you don't understand these types, you can google them.

String types are divided into fixed-length and variable-length. The fixed-length strings are char (n) and nchar (n), where (n) is their length. NCHAR (n) is the abbreviation for "National Character", and its true meaning is any character from any language that Unicode has implemented. CHAR (n) is the local ASCII character set. A variable-length string does not multibyte a blank character like a fixed-length character. Choosing a length and character set for a column is a constraint that you really want to consider before you use it. Columns that are too short will block real data, and too long columns will introduce false data. One of my favorite tricks, when I see a column defined in nvarchar (255), uses it to load the Chinese heart Sutra. This is the classic verse of Zen Buddhism (Zen Buddhism). If I can't teach them sql, I'll bring them to enlightenment.

Date types are divided into pure date time and interval types. Date and time types are divided into dates and times. It represents a point in time. The date type contains the year, month, and day. The time type includes times, minutes, seconds, and 10 binary seconds. Together, they make up the timestamp data type in standard SQL, which SQL Server calls DateTime. The interval type is like days, hours, minutes, and seconds duration. SQL Server does not represent them as specific types, and uses integer functions to get similar results.

The rounding and truncation implementations of all data types are defined. All data types allow nulls, and in the SQL standard, there is a core set of functions for all data types, providing all the property extensions and detail implementations. For example, SQL Server has a bit data type, which is an exact number, allowing only 0, 1, and null values. You'll also find more data types, but this "three values" will do most of your work.

Use numeric types when you want to do calculations on the data. That is, like quantity, number, total and so on data element. If you need to perform calculations other than simple plus minus, define some extra decimal digits for rounding and overflow.

For the sort of whine you will also use numeric types. Example is the restaurant's star rating (such as 1 stars not 2 stars of good, 2 star of not 3 star good, and so on). Do not use numeric types for scales that do not perform calculations or comparisons. A common example of this error is zip code (zip code); The first number 0 has its meaning, you can't calculate it on them, and you can't sort it. The number of labels is used to represent hierarchies.

The use of strings for text, name, and encoding systems can be expressed in regular expressions. For example, a ZIP code should be defined with char (5). Do not try to calculate them; in COBOL, not in SQL.

Use date data types for date data. Yes, it sounds too obvious, I don't have to say. But one of the most common design errors is the use of strings for date and time data. Of course, people who do this do not write constraints to prevent dates like "2010-02-31" or to perform simple time calculation functions. They've made a design mistake, putting the display format into the database, not the front end.

Real numbers and time are continuous examples, and other data types are discrete. The discrete range is a finite number between any two different values (most likely 0). For example, the integer {4,9}, which has {5,6,7,8} between them. There was a children's TV program in icarly, where the hostess persuaded another child to have a new number called DIRF between 5 and 6. The joke was so funny because it was obviously wrong.

Continuity is a mathematical structure that has an unlimited number of data values between any of the two different values. You can always add more and more decimals to real numbers or have no limit on the time. Floating-point numbers have built-in functionality to handle rounding and calculation problems, but time data is not good. Usually this means that you have to use a pair of values to pattern events (start time, end time). If the event is still current, use NULL as the end event value. Then in the application you can use the coalesce () function to convert NULL to current_date or other meaningful values.

A constraint is a reason for a field in a table's column like a record. A constraint is a declarative sentence in a joins constraint value. The most important one is not NULL. Use it for the first time to declare each column, and then if you decide to allow NULL, the comment line statement explains what the description means in the context. For example:

 not NULL   is in

CHECK (< predicate > involving columns) is the simplest row-level constraint. And valid predicates can be used, some typical use will be:

Sex_codeSMALLINT  not NULL CHECK(Sex_codeinch(0,1,2,9)), Body_temperatureDECIMAL(3,1) not NULL CHECK(body_temperaturebetween 0.0  and 45.0), Airport_codeCHAR(3) not NULL CHECK(Airport_code= UPPER(Airport_code)), Zip_CodeCHAR(5) not NULL CONSTRAINTValid_zip_codeCHECK(Zip_Code like '[0-9][0-9][0-9][0-9][0-9]'),

You can also name the constraint, as shown above. This is a good practice because it makes the error message easier to read. With case expressions you get a lot of interesting things, and other predicates that use if-then logic for other complex rules, such as:

Floob_scoreINTEGER CHAR(5) not NULLCHECK( Case  whenFloob_score not between 1  and  About  Then 'F'             whenFloob_score=  the              andFuzz_nbr=  -             Then 'T' ELSE 'F' END =T'), 

As an exercise, write a case expression to verify that it is a number. Expressions can be long but not difficult.

In addition to data integrity, constraints can do other good things for you. The optimizer can use them to improve your query, insert, UPDATE, and delete. They help you save a lot of front-end code; Once done here, you don't have to do it over and over again in many applications, now or in the future. They ensure that all front-end programs use the same defined data elements.

CHECK () Constraints can also be placed at the table level. For example:

 not NULL   is in progressCONSTRAINT  validate_sale_durationCHECK  <=

A table constraint involves two or more columns. In standard SQL, you can also have check () constraints that reference other tables, but now I'll skip this; it's not widely used, or it's not a combination of SQL Server.

The policy of SQL is that when we compare it with NULL, the result of the logical value is unknown, not true or false. However, in the check () constraint, true and unknown are treated equally. We give Unknow the benefit of questioning.

SMALLINT  not NULL DEFAULT 0 CHECK inch (0,1,2,9DEFAULTcurrent_datenot  NULL

The purpose of the default value is to provide a value where the value is not given directly. This is usually done using the INSERT INTO statement, where you have to build the entire row, but you don't want to expose all the rows to the user, or you want to save some programming effort. Always provide default is not likely when you do it sometimes.

Now we're going to create the line, and in the 3rd we need to combine the rows into the table. Where we constrain other types, it applies to columns, more than one line.

Original link:

http://www.sqlservercentral.com/articles/Database+Design/69926/

Database Design (2/9): Domains, Constraints and Defaults (Domains, Constraints and Defaults)