Modeling a database using an object Relationship Diagram

I. Overview

It is very likely that you are planning a database-driven website, and you are almost certain that you have browsed the database-driven website. In the past, some websites relied on CGI scripts and text file storage to achieve data persistence, but now we can access a large number of different relational databases, object-relational databases, and object-oriented databases.

For Web applications, relational databases are a powerful support tool, thanks to their high availability and performance. In addition, relational databases are relatively easy to use. It is not difficult to find a database system with complete functions, open source code, and running on multiple platforms. You can use Perl, Java, PHP, and other server-side scripting languages to link relational databases with web sites.

With the development of the website scale, it is increasingly dependent on databases, usually relational databases. A large number of pages and services need to write information to the database table or extract information from the database. For most websites, database tables quickly become a key part of the website architecture and the hub of website operation. To facilitate and easily manage large volumes of data, user accounts, news, content, and statistics can all be stored in the relational database management system (RDBMS ).

Using graphs to manage data models is efficient and convenient. For RDBMS, an image describing a data model is usually called an entity relationship diagram (ERD ). Using ERD to describe the data model can help you define data requirements in advance, so that you can make effective plans for future changes and easily improve the planning as the website develops.

This article introduces ERD modeling tools and concepts. The article provides some graph examples, but they do not aim to provide accurate or comprehensive data design examples. They are designed to use two modeling tools as an example to introduce data modeling symbols. There are significant differences between different tools, but they have the same basic concept. The legends in this article are obtained from the trial versions of powerdesigner and Visio 2000 professional. You can find links to these tools and other similar products at the end of this article.

2. Are you using modeling tools?

Many small websites use the ascii SQL (Structured Query Language) script file for data modeling. This approach is most effective when there are few developers or ideally only one person. However, data models will soon evolve into a complex structure-in this case, Case (Computer Aided Software Engineering, Computer-Aided Software Design) tools, graphs about all data information, and a centralized knowledge base can greatly help you manage the data layer of a web site.

2.1 When to use SQL?

Even if you want to use SQL to directly manage the data mode (physical database), the graph can effectively help you understand and improve the system. However, if you have a very limited budget or time, you may not be able to use a complex new modeling tool. On the contrary, in this case, you should use a simple graphical tool to record the basic situation of the data mode and then gradually convert it to a complex data modeling tool.

If the database type you are designing is uncommon (or non-standard), it may be wise to avoid using some complex case tools, because the reverse engineering capabilities and some automatic functions of these tools may not work in your environment. The so-called automatic function here refers to the ability of the modeling tool to automatically generate appropriate SQL commands for the target database based on the graphics and attribute information of the input model. Reverse engineering is such a capability. The modeling tool extracts entity and link information from an existing table based on the deployed physical data mode.

2.2 Transfer to modeling tools

Converting from a simple drawing tool to a data modeling tool is not a complex process. Most data modeling tools work like a standard drawing tool. See Figure 1a and Figure 1b. These are two interface instances of data modeling tools. You can create and arrange tables, define relationships, and specify other information (column type, length, and key) here ).

　

　

Figure 1a: powerdesigner Interface

　

　

　

Figure 1b: Visio Interface

The main challenges for turning to data modeling tools are:

• Learn how to use modeling symbols.
• Use data modeling tools to describe existing data models without losing any key information.
• Find a tool that provides comprehensive support for your database, such as when generating SQL statements and creating a data model from the existing data mode through reverse engineering.

Some entry-level data modeling tools (see the reference resources later in this article) have only a small number of advanced features. This is good, but it also has drawbacks-they are easy to learn and use, but when you accumulate more experience, they may no longer meet your growing needs. However, there is generally no major problem with upgrading or replacing tools, especially when new tools can accurately and completely reverse engineer existing data models, the process of upgrading or replacing the tool is particularly simple.

3. ERD modeling symbols

This article uses the Information Engineering Symbol of Martin. This is the symbol used by powerdesigner. The symbols used by oracle designer are similar to those used by oracle designer. You can view the description of various ERD symbols in AIS modeling summary. The basic ERD drawing specifications are intuitive and easy to understand. You can define objects (tables) to describe the relationships between objects. When entering the table and link details, each tool has different practices. However, as far as the tools I have encountered are concerned, the basic concepts are the same among most software packages. The following describes the main graphic elements and settings you must understand.

Table 3.1

All well-structured data modeling tools allow you to specify a wide range of association information for the table. This information includes (but is not limited ):

• Table description, annotation, and object (table) title.
• Column, column type, length, default value, and mandatory condition.
• Primary Key, index, and uniqueness constraints.

To specify this information, you generally need to enter the table attribute window, as shown in Figure 2a and Figure 2B.

　

2a: Properties window of the table in powerdesigner

　

Figure 2B: Table attribute window in Visio

　

Once the attribute information of the new table is entered, the chart is updated to display the new or changed table information you provided. The following figure shows an instance of a table. The attribute information of this table is shown in Figure 2a and Figure 2B. In Figure 2a and Figure 2B, many columns are defined as (m) andatory (mandatory), (p) rimary (Primary Key), and (d) isplayed (displayed) columns. The following figure shows some attributes entered for the table.

　

Figure 3A: powerdesigner table

　

Figure 3B: Visio table

　

In Figure 3A, we can see some non-standard data types, such as phonenumber and PK. Many data modeling tools allow defining domains or custom data types for more than one column. Fields not only represent the Data Type-normally, they also contain check constraints, default values, value lists, and other information. If you want to update a domain (for example, define a new phone number format), all columns that reference this domain in this model will be automatically updated.

3.2 Relationship

If we only define tables in the data mode, the data modeling tool is not that important. The relationships and dependencies between tables are often complex. A tool to manage and display these relationships will be of great help. Important information that must be collected for a given link includes:

• Parent and Child tables.
• The forced relationship between two tables. For example, a parent table may have a child table, but a child table must have a parent table.
• Cardinality ). That is, a parent table can have zero or more sub-tables, but a sub-table can have only one parent table.
• Comments, comments, and Role descriptions about the relationship.

Most modeling tools define relationships by drawing lines between two or more tables. By default, a link is often defined as a one-to-many relationship, and it is optional for any party in the relationship. To modify a link, you must open the link attribute window to update the feature information of the object link. Figure 4A and Figure 4b show some attributes that can be defined for a link by two different tools:

　

Figure 4A: relationship property setting interface of powerdesigner

　

Figure 4b: relationship property setting page of Visio

　

The figure shows a one-to-many relationship, a typical parent-child association. The relationship between a department (Branch) and an employee (emplyee) is mandatory. It means that a department must have at least one employee (1-N mandatory relationship); on the other hand, it means that an employee must belong to and can only belong to one department (1-1 mandatory relationship ). Figure 5A and figure 5B reflect the modified relationship.

　

Figure 5A: Relationship between two tables in powerdesigner

Figure 5b: Relationship between two tables in Visio

　

This figure shows how to convert information into symbols. A forced link is represented by a solid vertical line (rather than an elliptic. Some tools use dotted lines to indicate optional links. The side of the link that belongs to "multiple" is represented by a figure similar to the bird's claw. The base of the link is displayed near the end it describes.

You may have noticed that the employee table does not have a foreign key column defined. This graph is still in the "conceptual design" phase-since then, the conversion from Conceptual Graphs to physical data models is essential. Most tools distinguish between conceptual and physical data models-the need for conceptual data models to describe information, but do not focus on details, such as indexing and mandatory Integrity of reference.

Sometimes you may need to define a self-referenced table. Self-referenced tables are generally used to describe hierarchical relationships. As shown in the following figure, most data modeling tools can process such relationships. Note that in this example, an employee can have zero or one superior-which enables you to handle special situations, such as the President's absence of direct superiors.

Figure 6a: Self-referenced table in powerdesigner

Figure 6b: Self-referenced table in Visio

Iv. Planning of Graphs

Defining tables and relationships is only part of the challenge. It is equally important to clearly understand the graph. Although some tools provide automatic layout capabilities, I have not yet seen a complete implementation. On the contrary, your goal should be to follow the "Peacock Flying southeast" rule (here the "peacock" is the symbol representing the "more" in the relationship, it is the three forks connected to the table, like a bird's claw ). In other words, the child table should be at the right and bottom of the parent table. This arrangement makes it easier to logically organize and understand data models. The most important and highest level table should appear in the upper left corner, so that the lower level table appears in the lower right corner of the page. For clarity, it is also important to reduce the number of crossover lines in the graph. As Eberhardt rechtin emphasizes in the art of systems tuning ting, "a good design often looks comfortable ". If your data model looks messy no matter how it is arranged, it may tell you that the data model itself has some noteworthy issues.

　

Figure 7A: complete ERTU (powerdesigner)

 

Figure 7b: complete ERTU (visio)
　

5. From graph to database

Depending on the software package you selected to create a data model, the modeling tool may generate SQL commands based on the model or directly modify the database mode. This feature brings great convenience. Compared with SQL scripts in ASCII format, this method has many advantages. The functions of some modeling tools are suitable for a large number of database types, such as PostgreSQL, MySQL, Oracle, DB2, and so on. For simple database modification, the modification operation can be completed directly from the modeling tool through ODBC. Database changes can also be performed in incremental mode (for example, the alter command or create command, and the update command for a specific table ). When you use the modeling tool for the first time, you can view the SQL generated by the modeling tool to see if you can trust and approve the interpretation of the data model by the modeling tool. After a while, you will be familiar with the modeling tool's interpretation of various relationships and table details.

[Conclusion] data modeling is a good software engineering practice. It helps you plan data requirements before writing code. Data Modeling is also useful in the process of maintaining and improving the data layout of the system. Some tools can make the process very simple and help you manage and design the database system. However, the price of modeling tools varies greatly depending on the features you need. Without a budget deficit, the best way to easily master and use data modeling technology is to start with a small tool and then gradually deepen and improve.

　

Vi. references and resources

■ Tools

• Sybase powerdesigner-a high-end data modeling tool. You can download a 45-day trial.
• Erwin-a high-end data modeling tool. You can download the trial version.
• Rational Rose enterprise-a high-end UML tool with proper Database Modeling Support. You can download the trial version.
• Visio Professional-a low-cost drawing tool that can be used to generate data models and UML diagrams. The Enterprise Edition also supports two-way engineering capabilities for various databases. You can order a 60-day trial CD.
• Dezign-an extremely inexpensive ERD modeling tool. You can download a limited trial version.
• ERD tool list-a list of links and resources for various database and UML modeling tools.

■ Reference

• Database Design for the Web
• Designing Databases
• ERD notation Summary
• On-line database books
• Diagramming Methods
• Introduction to ER Modeling