Database design Level 3: Building tables

From: Stairway to Database Design level 3:building Tables

author Joe Celko,

translation: Liu Jongmao Shesheni Xu Yali Lai Huifang

President

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.

There are many types of tables, and each table has requirements for their specific rules and integrity constraints. No matter what the requirements are, surface-level constraints ensure that the integrity of the data is performed and maintained.

In the table, the column appears only once. It makes sense that if you record someone's shoe size two times, it will be superfluous and confusing when the columns are inconsistent. Now we can have a check constraint on the surface level of the joins in each row. This is not the same as checking the previous column (check). They can be named and appear in multiple column declarations in the CREATE TABLE statement, and are not attached to any rows. For example:

CONSTRAINT valid_employee_age--don ' t hire people before they is born CHECK (Emp_birth_date < emp_hire_date)

Generally, the inspection Unit is not synthesized into a large check () clause, and the error message contains the constraint name, so that independent constraints can make you more aware of the problem than a single complex named constraint.

Next is our redundancy problem, at the surface level we want each row to be the only one for the same reason, which can be implemented by constraints. The two surface level constraints are unique and primary KEY, which can be a single-column or multiple-row combination.

A unique constraint indicates that the combination of columns or columns is unique in the table. However, there is null in the column or columns, and if it is a unique value, we are allowed. The PRIMARY key declaration has the same effect as for all columns in the table, not null and unique, but as a rule, a table can have only one PRIMARY key declaration, which are used as other constraints between tables, but now don't care.

How uniqueness constraints are used depends on the type of table, in general, we can divide a table into three types:

1. Entities (Entity)

2. Relationship (Relationship)

3. Auxiliary (auxiliary)

An entity table is a collection of elements of the same type that are defined by the properties that are modeled by the columns. Each row is an instance of such an element, and each row has the same column. If you can see how it feels, see or touch it, then it is an entity. The name of the entity table should not be singular (unless there is really only one member in this collection) because it is modeled on a set, named as a complex number, or, if possible, named with a collection. For example, "Employee" is not good, "Employees" better, "personnel" best. "Tree" is not good, "Trees" Better, "Forest" best. You can add your own examples.

Entities are also weak and strong, a strong entity exists with its own merits, and a fragile entity exists in one or more powerful entities. For example, you need to buy to discount.

A relational table refers to one or more entity tables, and relationships are established between them. A relationship can have its own properties of an external referencing entity. The registration number belongs to the marriage, not to the husband, wife or priest.

The relationship level is the number of entities in the relationship, the two-ary relationship has 2 entities, and in practical applications we like them because they are simple; the binary iterative relationship is associated to the entity itself, and the general n-ary relationship involves n entities, like a mortgage on a buyer, seller, and bank. It is not usually possible to decompose n-ary relationships into two-dollar relationships. The relationship of a member can be optional or required. An optional relationship means that we can have a class of 0 entities-not all deals are discounted.

The relationship cardinality is the actual number of related occurrences for each of the 2 entities. The basic connection types of the relationship are: 1:1,1:n, and N. These terms are usually compatible with an optional (0 or more) or mandatory (1 or more) relationship.

1:1 A relationship is the time when an instance of an entity A is associated with an instance of entity B. For example, take the usual relationship between husband and wife. Every husband has and only one wife; every wife has and only one husband. In this case all must be one.

A 1:n relationship is an instance of entity A, with one instance of entity B having 0, one or more instances of entity B, and only one instance of entity a. An example would be a department with a lot of employees, each employee assigned to a department. Depending on your business rules, you will allow employees who are not assigned departments or empty departments.

A/n relationship, sometimes called non-specific, has 0 instances of one or more entity B for an instance of entity A, and for an instance of entity B, there are 0 instances of one or more entity a. Examples of this can be pizza and customers.

A secondary table is not an entity or a relationship; it provides information. They are query tables (looks up tables) that are like calendars or replace calculations in SQL. They are often misunderstood to be treated as entities or relational tables.

Let's go over the details. A sales order is the relationship between a customer (entity) and our Inventory (entity). The order details are the weak entities that exist because we have orders. This relationship has an order number that is not part of the inventory or customer. Shipping costs are obtained from the auxiliary form. For this example, I have used some skeleton tables here. For order items, I use Gtin (Global Trade Item number), and for customers, I use guns (Data Universal numbering System). When you design a database, remember to look at industry standards first.

CREATE TABLE Sales_orders (Order_nbr INTEGER not NULL PRIMARY KEY CHECK (Order_nbr > 0), Customer_duns CHAR (9) Not NULL, Order_shipping_amt DECIMAL (5,2) not NULL CHECK (Shipping_amt >= 0.00), etc); CREATE TABLE Sales_order_details (Order_nbr INTEGER not NULL, Gtin CHAR (a) is not NULL, PRIMARY KEY (ORDER_NBR, Gtin), Item_qty INTEGER not NULL CHECK (Item_qty > 0), Item_unit_price DECIMAL (8,2) not NULL CHECK (Item_unit_price >=0.00)); CREATE TABLE Customers (Customer_duns CHAR (9) not NULL PRIMARY KEY CHECK (Customer_duns like ' [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] '), etc); CREATE TABLE Inventory (Gtin CHAR () not NULL PRIMARY KEY CHECK (Gtin like ' [0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] '), Onhand_qty INTEGER not NULL CHECK (onhand_qty >= 0),

We can see that the order form is the relationship between the customer and the inventory. Orders have their own primary key (ORDER_NBR), but nothing forces us to use a valid customer DUNS number or a product gtin number in our inventory. In fact, I can insert the apparently invalid Duns and gtin yards into the order form, and now declare that.

This is where we want to introduce the REFERENCES clause. It is something that lets us force all cardinality and degree from the data model. A reference (reference) is not a link or pointer. These are physical concepts, references are a logical concept, and we don't know how it is implemented. It enforces that, in the reference table, the referencing tables column conforms to the single-line rule. This means that the rows in the referencing table must be unique; By default, the primary key (PRIMARY key) can be used in the reference table, but not necessarily. The values in the reference table can be called foreign keys (Foreign keys)-they are not in their table, but elsewhere in the schema.

This is the main architecture with more information on it:

CREATE TABLE sales_orders (ORDER_NBR INTEGER not NULL PRIMARY KEY  check (order_nb R > 0),  customer_duns CHAR (9) Not NULL  references Customers (customer_duns),   Order_shipping_amt DECIMAL (5,2) DEFAULT 0.00 not NULL  check (Shipping_amt >= 0.00),  etc); CREATE TABLE sales_order_details (ORDER_NBR INTEGER not null  references Orders (ORDER_NBR),  gtin CHAR (+) NOT null  references Inventory (Gtin),  primary key (ORDER_NBR, Gtin),--both column KEY  item_qty INTEGER NOT null  check (Item_qty > 0),  item_unit_price DECIMAL (8,2) not null   CHECK (Item_unit_price >= 0.00)); CREATE TABLE Customers (Customer_duns CHAR (9) not NULL PRIMARY KEY  check (customer_duns like ' [0-9][0-9][0-9][0-9][0-9][0-9][ 0-9][0-9][0-9] '),  etc);

Note that where Duns and gtin are primary keys, we only have check () constraints, not in the reference table in which they appear. Entity tables, customer and inventory are referenced, relational tables, orders, references to other tables. This is the general pattern, but not fixed.

The Multi-column form of this clause is as follows:

FOREIGN KEY (ORDER_NBR, Gtin) REFERENCES sales_order_details (ORDER_NBR, Gtin)

The column in the FOREIGN KEY clause is a reference table that needs to match the reference primary key, column to column, but may have a different name. I can set the relationship between 1:1,1:n and N: {by placing a unique constraint in the appropriate place. As an example of a secondary table, we can calculate the shipping cost based on the total amount of the order, which looks like this:

CREATE TABLE shipping_costs (Start_order_amt_tot DECIMAL (10,2) not NULL, End_order_amt_tot DECIMAL (10,2) is not NULL, CONSTRAINT Valid_shipping_range CHECK (Start_order_amt_tot < End_order_amt_tot), PRIMARY KEY (Start_order_amt_tot, End_order_amt_tot), Shipping_amt DECIMAL (5,2) not NULL CHECK (Shipping_amt > 0.00));

Although we declare the primary key (PRIMARY key) on the auxiliary freight table, it is not like the primary key of the entity-there is no validation or verification, nor is it an identifier. Using this table, we will look at similar queries:

SELECT Shipping_amt From Shipping_costs WHERE <order amount total> between Start_order_amt_tot and End_order_amt_tot;

As an exercise, try to write down constraints that will block the start and end ranges from repetition and faults. You can redesign the table if you want.

In the revised main structure, when you try to order a product with no inventory, you will receive the error "No inventory!" "That way, you can try something else." But if you try to remove the product from the inventory, you will also receive the error "amount, someone has placed an order for this product", so you must replace it with a different value or a null value for each order before deleting it from the inventory.

This is where referential integrity (declarative referential Integrity (DRI)) is used. The syntax is:

On DELETE [NO ACTION | SET DEFAULT | SET NULL | CASCADE] On UPDATE [NO ACTION | SET DEFAULT | SET NULL | CASCADE]

Deletions and updates are called "Data base Events", and DRI operations occur when they occur to a table.

NO ACTION: Transaction rollback, you receive a prompt. The default action when there is a simple references clause.

Set Default: Referenced columns are changed by events, but the referenced column values are modified to their default values. Of course, the referenced columns should have a declared default value above them. These defaults need to be in the reference table.

Set NULL: The referenced column is changed by the event, but the referenced column value is modified to NULL. Of course, the referenced column allows null values. This is where the introduction of the null value "presumption of innocence (benefit of the doubt)".

CASCADE: The referenced columns are changed by events, and the values are cascaded to the referenced columns. This is the most important option in practice. For example, if we want to stop a product, we can remove it from the inventory, and on DELETE cascade will let the SQL engine automatically delete the matching rows in Sales_order_details. Similarly, if an item is updated in inventory, on UPDATE cascade automatically replaces the referenced column with the new value.

Referential integrity constraints are still valid after these operations are completed. This is the final architecture:

CREATE TABLE sales_orders (ORDER_NBR INTEGER not NULL PRIMARY KEY  check (order_nb R > 0),  customer_duns CHAR (9) Not NULL  references Customers (customer_duns)  on UPDATE CASCADE  on DELETE CASCADE,  order_shipping_amt DECIMAL (5,2) DEFAULT 0.00 not NULL  check (Shipping_amt >= 0.00),  etc); CREATE TABLE sales_order_details (ORDER_NBR INTEGER not NULL  references Orders (ORDER_NBR)  on UPDATE CASCADE  on DELETE CASCADE,  gtin CHAR (+) not NULL  references Inventory (gtin)  on UPDATE CASCADE  on DELETE CASCADE,  primary key (ORDER_NBR, Gtin),--both column KEY  item_ Qty INTEGER NOT NULL  check (Item_qty > 0),  item_unit_price DECIMAL (8,2) not null  check (Item_unit_price >= 0.00));

What do you think will happen when you see what happens in the following situations?

1. A client went away, and we removed it.

2. We modified the lawn gnome statue to be a more tasteful pink Flamingo.

3. We stop selling pink Flamingo.

4. After 1-3 steps, someone tries to lawn the gnome order.

Obviously, I left unresolved issues and other things, but we will touch these.

Original link:

http://www.sqlservercentral.com/articles/Stairway+Series/69927/

Database design Level 3: Building tables