Database Design 5 Steps (Favorites)

1. Determine entities and relationships

A) design macroscopic behavior. What you do with this database. For example, you want to manage employee information.

b) Determine entities. For a series of behaviors, determine the scope of topics covered by the information being managed. This will become a table. For example, hire employees, designate specific departments, and determine skill levels.

c) determine relationships. Look at the behavior and determine what the relationship is between the tables. For example, there is a relationship between the department and the employee. Give this relationship a name.

d) Refinement behavior. You start with macro behavior, and now examine these behaviors carefully to see what behaviors can turn into microscopic behavior. For example, information about managing employees can be refined to:
Add new Employees
Modify the presence of employee information
Delete the transferred employee

e) determine the business rules. Look at your business rules and determine which one you want to take. For example, there may be a rule that a department has and can have only one department leader. These rules will be designed into the structure of the database.

Example:

Acme is a small company with offices in 5 locations. Currently, there are 75 employees. The company is poised to expand rapidly, dividing 9 divisions, each with its own leadership.
To help find new employees, the personnel department has planned 68 skills to prepare for future personnel management. When employees are recruited, the professional level of each skill is determined.

Define macro Behavior
Some of Acme's macro actions include:
Recruit Staff
Dismissal of employees
Manage Employee Personal Information
Skills information needed to manage the company
Manage which employees have what skills
Management Department Information
Manage Office information

determine entities and relationships
We can identify the subject area (table) and its relationships to store information and create a chart based on macro behavior and description.
We use a box to represent table and a diamond to represent the relationship. We can determine which relationship are one-to-many, the other, and many-to-many.
This is a e-r sketch that will be refined later.



Refine macro Behavior
The following microscopic behavior is based on the macroscopic behavior above:
Add or remove an employee
Add or remove an office
List all employees in a department
Add a skill
Add a skill to an employee
Identify the skills of an employee
Determine the level of each skill for an employee
Identify all employees who have a skill at the same level
Modify an employee's skill level

These microscopic behaviors can be used to determine which table or relationship are required.

Determining Business Rules
Business rules are often used to determine one-to-many, one-to-one, and many-to-many relationships.
The relevant business rules may include:
There are now 5 offices; it is allowed to scale up to 10.
Employees can change departments or offices
Each department has a department leader
Up to 3 phone numbers per office
Each phone number has one or more extensions
When employees are recruited, the professional level of each skill is determined.
3 to 20 skills per employee
An employee may be placed in an office or may not be assigned to an office.

2. Determine the required data

To determine the required data:
1. Identify support data
2. List all the data that you want to track. The data that describes the table (subject) Answers these questions: Who, what, where, when, and why
3. Set up data for each table
4. List available data that each table currently looks appropriate
5. Set the data for each relationship
6. If available, list the applicable data for each relationship

Identify support data

The support data you have identified will be the field name in the table. For example, the following data will apply to table employee, table skill, table expert in.



If you draw this data into a chart, it looks like this:



Need to note:
When determining your support data, be sure to refer to the macro behavior you identified earlier to understand how to use that data.
For example, if you know that you need a list of all employees sorted by last name, make sure that you decompose the supporting data into first and last names, which is better than simply providing a name.
The name you choose is best kept consistent. This will make it easier to maintain the database and easier to read the output reports.
For example, if you use an abbreviated name Emp_status in some places, you should not use the full name (empolyee_id) in another place. Instead, these names should be emp_status and emp_id.
Whether the data is relative to the correct table is irrelevant, and you can do it according to your preferences. In the next section, you'll pass the test to make a judgment.

3. Standardized data

Standardization is a series of tests that you use to eliminate data redundancy and ensure that data is associated with the correct table or relationship. A total of 5 tests. In this section, we will discuss the 3 frequently used.
For more information on standardized testing, refer to the book on database design.

Standardized Format
Standardized formats are common testing methods for standardized data. Your data passed through the first test, it is considered to achieve the first standardized format; second pass test to achieve the second standardized format; The third standardized format is achieved by the third pass test.

How to format the standard:
1. List data
2. Determine at least one key for each table. Each table must have a primary key.
3. Determines the key of the relationships. The key of the relationships is the key that joins the two tables.
4. Examine the calculation data in the Support data list. Calculated data is usually not persisted in the database.
5. Put the data in the normalized format of the first pass:
6. Remove duplicate data from tables and relationships.
7. Create one or more tables and relationships with the data you have removed.
8. Put the data in a standardized format for the second pass:
9. Use more than one key to determine tables and relationships.
10. Removes data that depends only on the key part.
11. Create one or more tables and relationships with the data you have removed.
12. Put the data in a standardized format for the third pass:
13. Removes data that relies on other data in tables or relationships and is not a key.
14. Create one or more tables and relationships with the data you have removed.

Data and Keys
Before you start standardizing (test data), simply list the data and determine a unique primary key for each table. This key can consist of a field or several fields (a chain key).

A primary key is a set of fields in a table that uniquely distinguishes between rows. The primary key of the employee table is the Employee ID field. The primary keys in the Works in relationship include the Office code and employee ID fields. Give a key to each relationship in the database, extracting its key from each table it is connected to.


put the data in the standardized format for the first pass
To remove a repeating group
To test the normalization format for the first pass, remove the duplicate groups and put them in their respective tables.
In the example below, Phone number can be duplicated. (a worker can have more than one phone number.) Remove the duplicate group and create a new table named telephone. In telephone, create a relationship with office named associated with.

put the data in a standardized format for the second pass
Remove data that does not depend on the entire key.
See only those tables and relationships that have more than one key. To test the normalization format for the second pass, remove any data that does not depend on the entire key (all the fields that make up the key).
In this example, the original employee table has a key that consists of two fields. Some data does not depend on the entire key; For example, department name only depends on one of the keys (department ID). Therefore, the Department ID, the other employee data is not dependent on it, should be moved to a new table named Department, and a relationship named Assigned to for employee and Department.


put the data in a standardized format for the third pass
Remove data that is not directly dependent on the key.
To test the normalization format for the third pass, remove data that is not directly dependent on the key, but relies on other data.
In this example, the original employee table has data that depends on its key (Employee ID). However, office location and office phone depend on other fields, Office Code. They are not directly dependent on the employee ID key. Move this set of data, including Office Code, to a new table called Office, and create a relationship named works in for employee and office.





4. Consideration of relationships

When you have finished the standardization process, your design is almost complete. All you need to do is consider the relationship.

consider a relationship with data
Some of your relationship may contain data. This often happens in many-to-many relationships.



In this case, convert the relationship to a table. The relationship key still becomes the key in the table.

consider a relationship without data
To implement a relationship without data, you need to define a foreign key. A foreign key is one or more fields that contain a primary key in another table. External keys enable you to connect multiple table data at the same time.

There are a few basic principles that can help you decide where to put these keys:

One -to-manyIn a one-to-many relationship, the primary key in "a" is placed in "many". In this example, the foreign key is placed in the employee table.



one to oneAn external key can be placed into either table in a one-to-a relationship. If it has to be on one side and not on the other, it should be on the other side. In this example, the foreign key (Head ID) is in the department table, because this is required.



Many-to-manyIn a many-to-many relationship, create a new table with two foreign keys. Existing old tables are contacted through this new table.



5. Inspection design

Before you complete the design, you need to make sure it meets your needs. Check the behavior you defined at the outset, confirming that you can get all the data you need for the behavior:
Can you find a path to wait for all the information you need?
Whether the design meets your needs.
Are all the required data available?
If you answer all of the above questions, you're almost done with the design.

Final Design
The final design looks like this:




Designing table Properties for a database
The database design needs to determine what tables there are, and what fields are in each table. This section discusses how to specify properties for each field.

For each field, you must determine the field name, data type and size, whether to allow null values, and whether you want the database to limit the values allowed in the field.

Select field Name
Field names can be any combination of letters, numbers, or symbols. However, if the field name includes letters, numbers, or underscores, or does not begin with a letter, or if it is a keyword (see the keyword table), you must enclose the field name in double quotation marks when you use it.

Select a data type for a field
The data types supported by SQL anywhere include:
Integer (int, integer, smallint)
Decimal (decimal, numeric)
Floating point number (float, double)
Character type (char, varchar, long varchar)
Binary data type (binary, Long Binary)
Date/Time type (date, hour, timestamp)
User-defined Types

For information about data types, see the "SQL Anywhere data Type" section. The data type of the field affects the maximum size of the field. For example, if you specify smallint, this field can hold an integer of 32,767. The integer can hold 2,147,483,647 integers. For char, the maximum value of a field must be specified.

Long binary data types can be used to store such images (such as bitmaps) or text-editing documents in a database. These types of information are often called binary large objects, or blobs.

For a complete description of each data type, see "SQL Anywhere Data type".

null and NOT NULL

If a field value is required, you define this field as not NULL. Otherwise, the field value can be a null value, that is, you can have a null value. The default value in SQL is to allow null values; You should display the field as not NULL unless you have a good reason to set it to allow null values.

For a complete description of NULL values, see "null value". For its comparative usage, see "Search conditions".

Select constraint

Although the data type of a field restricts the data that can exist in a field (for example, you can only save numbers or dates), you might want to further constrain its allowable values.

You can limit the value of any field by specifying a "CHECK" constraint. You can use any valid condition that can occur in the WHERE clause to constrain the allowed values, although most check constraints use between or in conditions.

More Information

For more information on valid conditions, see "Search conditions". For information on how to specify constraints for tables and fields, see "Ensuring Data Integrity".

Example
The example database has a table named department, the field is dept_id, Dept_name, dept_head_id. It is defined as follows:


Note Each field is specified as "not NULL". In this case, the data for all fields in each record in the table is required.

Select Primary key and foreign key
A primary key is a field that uniquely identifies each record in the table. How your table has been normalized correctly, the primary key should be part of the database design.
A foreign key is a field or set of fields that contains a primary key value from another table. The external key relationship establishes one-to-one and one-to-many relationships in the database. If your design is properly standardized, the external key should be part of the database design.