Translation: Database design level 3: Building tables

Database design Level 3: Building Tables

Stairway to Database Design level 3:building Tables

Joe Celko, 3 months 9 days ( first published : 2010/05/25)

The series

This article is part of the stair series : The ladder of database design

do you have a new task for designing and creating a database ? Joe Celko is one of the most extensive writers on SQL and he explains the basic questions. As usual, he occasionally surprises the most seasoned database professionals. Joe isthe recipient of theDBMS Magazine Reader's Choice Award, winning for four consecutive years. He has taught SQL in the United States, the United Kingdom, the Nordic countries, South America and Africa . He worked for the Ansi/iso SQL Standards Committee for ten years, and for SQL-89 and The SQL-92 Standard has contributed.

There are several types of tables, each with their own special requirements for rules and integrity constraints. Regardless of what the requirements are, table-level constraints ensure that rules are enforced and data integrity is maintained.

in the first level, we will use the data elements to name them and categorize them. In the second level, we modeled the data elements with data types and constraints in SQL to implement the operations that the database provided us with rows. In the third level, we put these rows into the table. A table is more than just a bunch of rows with a name set together.

A column can only appear once in a table. Only this makes sense. If you record someone's shoe number two times, you can only be counted as redundant, and when the two columns are inconsistent, the system will be ambiguous and unable to execute. Now we can set the table-level CHECK constraint between the columns in each row. They are not really that different from our previous list of check constraints. They can be named and appear in the list of column declarations in the CREATE TABLE statement instead of connecting to any row. For example:

It is a good way to combine constraints into a large check () clause. The error message will contain the constraint name, so a separate constraint will allow you to better understand the error rather than a single monster called "bad_things_happened" constraints.

Continue our dislike of redundancy, at the table level, we want each row to be unique for the same reason. This can be done through table constraints. These two table-level constraints are unique and primary keys that appear in both single and multiple column versions.

The only constraint is that the combination of columns or columns is unique in the table. But if there is a null value in one or more columns, we allow it to be a unique value. The primary key declaration is the same as the non-null and unique effect of all columns in it. But for historical reasons, a table can have only one primary key declaration. These columns are used as default values for other constraints between tables, but don't worry now.

How the uniqueness constraint is used depends on the type of table involved. Broadly speaking, we can classify a table as one of three types :

1. Entity
2. Relationship
3. Aided

An entity table is a set of things of the same type that are defined by the properties that are modeled by the columns. Each line is an example of this. Each row has the same column. If you can see it, or see it, touch it, it is an entity. The name of the entity table should not be singular ( unless there is really only one member in the collection )because it is modeled for a collection. This name needs to be plural, if possible, preferably a collection. For example, "employee" is not good, "staff" is better, "employee" is the best. "Tree" is not good, "tree" Better, "forest" is the best. You can add your own examples.

Entities are also classified as weak or strong. A strong entity exists in its own merits, and a fragile entity is present in one or more powerful entities. You will purchase a consumer premise that may be on sale.

A relational table references one or more entities and establishes relationships in them. In addition to referencing an entity, a relationship can have its own properties. Marriage certificates belong to marriages, not husbands, wives or priests.

the degree of the relationship is the number of entities in the relationship. There are two entities in a binary relationship, and in the real world we like them best because they are simple. A recursive binary relationship ties the entity to itself. The General N- ary relationship involves n entities, such as a buyer's mortgage, the seller and the bank. It is not always able to decompose into an n -ary relationship with a two-element relationship. Members in a relationship can be optional or mandatory. The optional membership means that we can only have one way to have a 0 entity-a way to buy not always get a discount.

the cardinality of a relationship is the actual number of related events for each entity in the two entities. The basic types of relationship connectivity are: one-to-many, many-to-many. These terms usually have an optional (0 or more) or mandatory (1 or more) memberships.

One-on -One (1:1) relationship is an instance of entity a that is associated with at most one instance of entity B , for example, a relationship between a traditional couple. Every husband has only one wife, and each wife has only one husband. Both of these are mandatory in this case.

A one -to-many (1,n) relationship is An instance of entity A, where entity B has one, one, or more instances, But for an Instance of entity B, there is only one entity A. An instance, it may be that a department has many employees, each employee is assigned to a department. Depending on your business rules, you may allow a designated employee or gouges.

a many-to-many (M:N) relationships, sometimes referred to as non-specific, are when entities A An instance of 0, one or more entities B an instance of an entity B instances of 0, one, or more entities A instance, an example might be pizza and a customer.

auxiliary tables are neither entities nor relationships ; it provides information. They are similar to calendars or other tables that are computed in lookup SQL. They are often misunderstood and treated as entities or relational tables.

let's make it more concrete. A sales order is a relationship between a customer ( entity ) and an inventory ( entity ) . Order details are present in a weak entity because we have an order. The relationship has an order number, not a part of the inventory or customer. Transportation costs are obtained from a secondary table. Here are some of the framework tables for this example. I am using GTIN ( Global Trade Item number ) to provide orders and DUNS for customers ( Data General numbering system ). When designing a database, always look for industry standards.

we can see that the sales order is the relationship between the customer and the inventory. Orders have their own keys (ORDER_NBR), but nothing can force us to use only valid customer DUNS numbers or products GTIN Code to meet what we actually have in stock. In fact, I can insert the apparently invalid DUNS and GTIN code in the command table , as I have now declared.

This is where the reference clause comes in. It enables us to perform all the cardinality and degree of things from the data model. The reference is not a link, nor is it a pointer. These are the concepts of physics, the reference is a logical concept, we do not know how it is implemented. The rule it enforces is that the reference table column matches a row in the referencing table. This means that the rows in the referencing table must be unique ; by default, the primary key in the reference table is the destination, but it does not have to be. The values in the reference table are called foreign keys-they are not keys in the table, but are other locations in the pattern.

here is the skeleton pattern, with more meat on it:

Note that the DUNS and GTIN are the keys where we only need to check () constraints, not where they appear in the reference table. Entity tables, customers, and inventory are referenced ; relational tables, orders, references to other tables. This is a general pattern, but not specific.

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

The columns in the FOREIGN KEY clause are in the reference table and must match the reference key for the column, but can have a different name. I can get 1 by placing uniqueness constraints in the right place : 1,1:n and n:m The relationship. As an example of the following table, we can calculate the shipping cost based on the total value of the order. The table can be like this :

Although we have declared the primary key for the secondary transport cost table, it does not resemble the entity's key -there is no validation or validation, it is not an identifier. To use this table, we will query for similar problems :

As an exercise, try writing a constraint to prevent the start and end ranges from overlapping and having gaps. You can redesign the table if you want.

in the modified skeleton mode, when you try to execute an order for a product that is not in stock, you get an error that actually says,"It's out of stock ! " "You can try something else." But if you try to delete a product inventory , You'll also get an error actually saying ,"Hey , Somebody ordered this crap", so you have to go to every order and use something else instead of the item or NULL ( if allowed ) before you can delete it from inventory.

This is the use of declarative referential integrity (DRI) The syntax for manipulating the place is :

Delete and update are referred to as "data base events"; occurs when they occur on a table DRI operation.

1. work does not take action = you will get a message when the transaction is rolled back. This is the default value when you have only one simple reference clause.

2. The Set default = referenced column is changed by the event, but the reference column is changed to the default value. It is clear that reference columns need to declare default values on them. These default values must be in the reference table.

3. Project SET NULL = the referenced column is changed by the event, but the reference column is changed to NULLs . Obviously, the reference column needs to be empty. This is the reason for the "dubious benefits".

4. cascading = The referenced column is changed by the event, and the same value is cascaded to the reference column. This is the most important choice in practice. For example, if we want to stop a product, we can remove it from inventory, and in delete CASCADE ,theSQL engine automatically deletes Sales_ the matching rows in the Order_Details. Similarly, if you update an item in inventory, update cascading replaces the old value with the new value anywhere in the reference.

referential integrity constraints are still valid after you perform these operations. This is the final skeleton :

See if you can figure out what 's going on:

1. a working client died and we removed him.

2. we replaced the dwarf statue on the lawn with a more tasteful flamingo.

3. Project let's Stop the Pink Flamingo.

4. after steps 1 to 3 , an attempt was made to order the lawn Gnome

Obviously, I don't consider restocking problems and other things, but we will get these.

The series

Read the rest of the database Design series and view additional articles.

This article is part of the database design staircase ladder.

Translation: Database design level 3: Building tables