Database modeling with entity diagrams __ Database

database modeling with entity diagrams

I. Overview

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

Relational databases are a powerful support tool for WEB applications, thanks to their high availability, performance, and relational databases that are relatively easy to use. It is not difficult to find a database system that is functional, open source, and capable of running on a variety of platforms. You can link relational databases to Web sites in Perl, Java, PHP, and other server-side scripting languages.

As the size of the site grows, it is increasingly reliant on databases-often relational databases. A large number of pages and services need to write information to a database table or extract information from a database. For most Web sites, database tables quickly become a key part of the web's architecture and become the Life center of the site's operations. For easy and easy management of large capacity data, user accounts, news dynamics, content, and statistics can be saved to the relational database management system (relational DB Management system, RDBMS).

Using graph (Diagram) to manage data model has the advantages of high efficiency and convenience. For RDBMS, diagrams that describe the data model are often referred to as entity diagrams (Entity relationship Diagram, ERD). Using the ERD to describe the data model can help you define data requirements in advance, enabling you to plan for future changes and improve your planning as your site progresses.

This article introduces the ERD modeling tools and concepts. The article provides examples of graphs, but their purpose is not to provide an accurate or comprehensive example of data design. Their purpose is to introduce the data modeling notation with two modeling tools as an example. There are significant differences between the different tools, but their basic concepts are the same. The illustrations in this article are available from PowerDesigner and the trial version of Visio Professional, and you can find links to these tools and other similar products at the end of this article.

second, whether to use modeling tools.

Many smaller web sites use ASCII-style SQL (structured Query Language) script files for data modeling. This approach works best when the development team has fewer people, or ideally, only one person. However, the data model will soon evolve into a complex structure--in which case (Computer aided Software Engineering, computer-aided software design) tools, graphs of all data information, and a centralized knowledge base can greatly help you manage The data tier of the Web site.

2.1 when to use SQL .

Even when you are ready to use SQL directly to manage data schemas (physical databases), diagrams can also effectively help you understand and improve your system. However, if your budget or time is very limited, the use of complex new modeling tools may outweigh the gains. Instead, in this case, you should use a simple graphical tool to record the basics of the data pattern and then step into the complex data modeling tool.

If the type of database you are designing is uncommon (or non-standard), it may be wise to avoid using some complex case tools because the "reverse engineering" capabilities of these tools and some of the automated features may not work in your environment. This so-called automatic function refers to the ability of a 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 the ability to extract entity and relationship information from existing tables based on the physical data patterns that have been deployed.

2.2 into the modeling tool

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, which is an interface example of two data modeling tools. You can create and arrange tables here, define relationships, and specify other information (column type, length, key, etc.).

　

　

Figure 1a:powerdesigner's interface

　

　

　

Figure 1b:visio's interface

The main challenges of turning to data modeling tools are:

· Learn to use modeling symbols.

· Use the data modeling tool to describe the existing data model without losing any critical information.

· Look for a tool that provides comprehensive support for your database, such as when generating SQL, and creating data models from existing data schemas through reverse engineering.

Some entry-level data modeling tools (see the reference resources later in this article) have only a few advanced features. This is good, but there are drawbacks-they are easy to learn, but when you accumulate more experience, they may no longer meet your growing needs. However, there are generally no major problems with upgrade tools or replacement tools, especially when new tools are able to reverse-engineer existing data patterns accurately and completely, and the process of upgrading or replacing tools is particularly straightforward.

Third, ERD Modeling Symbols

This article uses Martin's information Engineering symbol. PowerDesigner uses this symbol, and Oracle's Designer product uses symbols that are similar to it. You can view the instructions for the various ERD symbols in the AIS modeling Summary. The basic ERD drawing specification is straightforward and straightforward. You can define entities (tables) that describe the relationships between entities. Each tool has a different approach when filling in the details of tables and relationships, but the basic concepts are common to most packages in terms of the tools I've encountered. The next section will describe the main graphical elements and settings you must understand.

3.1 table

All reasonably constructed data modeling tools allow you to specify rich association information for a table. This information includes (but is not limited to):

· Description of the table, annotations, and the title of the Entity (table).

· Columns, the type, length, default value, and mandatory condition of the column.

· Primary key, index, uniqueness constraint.

To specify this information, you typically need to enter the table's Properties window, as shown in Figure 2a and Figure 2b.

　

Properties window for table in 2a:powerdesigner

　

The Properties window for the table in Figure 2b:visio

　

Once you have entered the property information for the new table, the diagram will be updated to show the new or changed table information you have provided. The following graph shows an instance of a table with the property information 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 of the property information entered for the table.

　

Figure 3a:powerdesigner's Table

　

Figure 3b:visio's Table

　

Some nonstandard data types, such as PhoneNumber and PK, can be seen in Figure 3 A. Many data modeling tools allow you to define domains or custom data types that can be used by more than one column. Domains not only represent data types--They also typically contain information such as CHECK constraints, default values, value lists, and so on. If you want to update a field (for example, to define a new phone number format), all columns referencing that field in the model are automatically updated.

3.2 relationship

The Data modeling tool is less important if we only define the tables in the data schema. Relationships and dependencies between tables are often complex, and a tool to manage and display these relationships can be a great help. Important information that must be collected for a given relationship includes:

· Parent and child tables.

· A mandatory relationship between two tables. For example, a parent table might have a child table, but a child table must have a parent table.

· Relationship cardinality (cardinality). That is, a parent table can have 0 or more child tables, but a child table has and can only have one parent table.

· Notes, comments, and role descriptions for the relationship.

Most modeling tools define relationships by drawing lines between two or more tables. By default, a relationship is often defined as a one-to-many relationship, and it is optional for either side of the relationship. To modify a relationship, you must open the Properties window of the relationship and update the feature information for the entity relationship. Figure 4a and Figure 4b show some of the attributes that two different tools allow to define for a relationship:

　

Diagram 4a:powerdesigner The Relationship property settings interface

　

Diagram 4b:visio The Relationship property settings interface

　

The figure shows a one-to-many relationship-a typical parent-child association relationship. The relationship between the Department (Branch) and the 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 belong to only one department (1-1 mandatory relationships). Figure 5a and Figure 5b reflect the modified relationship.