I. Overview
It is likely that you are now planning a database-driven website; and almost certainly, you have already viewed the database-driven website. In the past, some websites rely on CGI scripts and text files to implement data persistence, but now we can access a large number of different relationships, object-relational, object-oriented databases.
For web applications, relational databases are a powerful support tool, which is appreciated by their high availability, performance, and relatively, relatively easy to use. It is not difficult to find a database system that is perfect, source code is open, and the database system that can run on a variety of platforms is not difficult. You can use Perl, Java, PHP, and other server-side scripting languages to connect the relational database and web site.
With the development of the site, it is also increasingly dependent on the database - usually the relationship between the relationship database. A large number of pages and services need to write information to the database table, or extract information from the database. For most websites, the database table quickly became a key part of the website architecture and became the life center of the website. To facilitate large-capacity data, user accounts, news dynamics, and statistics can be saved to the relational database management system (RDBMS).
The Diagram Management Data Model has an efficient and convenient advantage. For RDBMS, a diagram describing the data model is often referred to as an entity relationship (Entity Relationship Diagram, Erd). Describe the data model with ERD to help you predefine data needs in advance, so that you can make effective planning for future changes, and can improve our plan with the development of our website.
This article will introduce ERD modeling tools and concepts. The article provides examples of examples, but their purpose is not to provide precise or comprehensive data design examples. Their purpose is to introduce data modeling symbols for two modeling tools. Between different tools, the symbols of the graph have significant differences, but their basic concepts are the same. This article is obtained from the trial version of PowerDesigner and Visio 2000 Professional, you can find links to these tools and other similar products from the end of this article.
Second, is it modeling tool?
Many smaller websites use the SQL (Structure Language) script file file in the ASCII form. This method is most effective when the development group is less, or when it is most ideal. However, the data model will soon develop into a complex structure - in this case, the Case (Computer Aided Software Engineering, Computer Assistive Software Design) Tools, the map about all data information, the centralized knowledge base can greatly help You manage the data layer of the web site.
2.1 When is SQL?
Even when you are ready to directly manage data modes (physical databases) with SQL, the map can effectively help you understand and improve your system. However, if your budget or time is very limited, complicated new modeling tools may not be lost. Instead, in this case, you should use a simple graphics tool to record the basic situation of the data mode and gradually convert to a complex data modeling tool.
If you are designing database types (or non-standard), avoid using certain complex CASE tools may be sensible, because these tools "reverse engineering" capabilities and certain automatic features may not be in your environment Play a role. The so-called automatic functionality here is the ability to generate the ability to generate the appropriate SQL command for the graphics and attribute information of the input model. The reverse engineering is such a capability that the modeling tool extracts entities and relationship information from existing tables according to the already deployed physical data mode. 2.2 Transfer to Modeling Tools
Converting to data modeling tools from simple drawing tools is not a very complex process. Most data modeling tools work moderation is like a standard drawing tool, see Figures 1A and 1B, which is an interface example of two data modeling tools. You can create and row list, define a relationship, and specify other information (types, lengths, keys, etc.).
Figure 1A: Interface of PowerDesigner
Figure 1B: Visio interface
The main challenge to the steering data modeling tool is:
Learn to use modeling symbols. Under the premise that no critical information is lost, the existing data model is described with the Data Modeling Tool. Find a tool for your database to provide a fully supported tool, such as generating SQL, establishing a data model from existing data patterns by reverse engineering.
Some entry-level data modeling tools (see refer to the reference resources later) only a small amount of advanced features. This is good, but there are also drawbacks - they are easy to learn, but when you have accumulated more experience, they may no longer meet your growing needs. However, upgrade tools or replacement tools generally do not have large problems, especially when new tools can accurately, complete reverse projects, especially when the existing data mode is, especially the process of upgrading or replacing tools.
Third, ERD modeling symbol
This article uses Martin's Information Engineering symbol. PowerDesigner is using this symbol, and the symbols used by Oracle's Designer products are similar to it. You can view the description of the various ERD symbols in AIS Modeling Summary. Basic ERD drawing specification is very intuitive and easy to understand. You can define an entity (table) to describe the relationship between each entity. When filling out the details of the table and the relationship, each tool is different; but on the tool I have encountered, the basic concept is in communication between most packages. The next content will introduce the primary graphical elements and setup methods you must understand.
3.1 table
All constructive data modeling tools allow for designing rich association information for tables. This information includes (but not limited to):
Description, annotation, and entity (table) title. Columns, columns, length, default, and mandatory conditions. Primary key, index, unique constraint.
To specify this information, you usually need to enter the property window, as shown in FIGS. 2A and 2B.
2A: Properties window in PowerDesigner
Figure 2b: Properties window in Visio
Once the attribute information of the new table is entered, the graph will be updated, showing the new or changed table information you provide. The following graph shows an example of a table, and the attribute information of this table is as shown in Figures 2A and 2B. In FIGS. 2A and 2B, many columns are defined as (m) and (m-enabled), (P) RIMARY (primary keys) and (d) columns. The figure below shows some attribute information input to the table.
Figure 3A: Table of PowerDesigner
Figure 3b: Visio
Some non-standard data types can be seen in Figure 3A, such as phonenumber and pk. Many data modeling tools allow define domains or custom data types, which can be used for more than one column. The domain not only represents data types - typically, they also include information such as check constraints, default, value lists. If you want to update a domain (eg, define a new phone number format), all columns that reference this domain will be automatically updated. 3.2 relationship
If we only define tables in data modes, data modeling tools are not so important. The relationship between the tables, the dependency is often complicated, and there is a tool that manages and displays these relationships will bring a lot of help. For a given relationship, important information that must be collected includes:
Parent table and child table. The mandatory relationship between two tables. For example, a parent table may have a sub-table, but the child table must have a parent table. Relational base (Cardinality). That is, a parent table can have zero or more sub-tables, but a sub-table is available and can only have a parent table. Notes, opinions, and roles of relationships.
Most modeling tools define relationships by drawing connecting between two or more tables. By default, relationships are often defined as a pair of multi-relations, and it is optional for any party in relationships. To modify the relationship, you must turn on the property window to update the feature information of the entity relationship. 4A and 4B show two different tools allow for partial properties defined by the relationship:
Figure 4A: PowerDesigner relationship property setting interface
Figure 4b: Visio relationship property setting interface
The figure shows a one-to-many relationship - a typical parent-sub-association relationship. The relationship between the department (BRANCH) and Emplyee is forcibly. It means that a department must have at least one employee (1-n forced relationship); on the other hand, it means that an employee must belong to a department (1-1 enforcement). Figures 5a and 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. The mandatory relationship is represented by a solid vertical line (rather than ellipse). Some tools represent an optional relationship with a dotted line. This side of the relationship belongs to "Multi" is expressed in a pattern similar to bird claws, and the base of the relationship is displayed close to the end it described.
You may have noticed that the Employee table does not define the key columns. This figure is still in the "Conceptual Design" phase. Since then, it is essential from the conversion between the conceptual map to the physical data model. Most tools distinguish between concepts and physical data models - concept data model description information, but does not pay attention to detail issues such as indexing and mandatory reference integrity.
Sometimes you might want to define the table of self-reference. Self-references are generally used to describe layered relationships. As shown in the graphics below, most data modeling tools can handle such relationships. Note that in this example, the employee can have zero or one level - it allows you to handle some special situations, such as the president no direct superior.
Figure 6A: Self-reference table in PowerDesigner
Figure 6b: Self-reference table in Visio
Four, planning
Definition forms and relationships are only part of the challenge, and the figure is clearly understood. Although some tools provide automatic layout capabilities, I have not seen a perfect implementation. On the contrary, your goal should be complied with the rules of "Peacock Southeast" (here "Peacock" is the symbol of "more" in relationships, it is three branches connected to the table, like a bird feet) . In other words, the child table should be located right and below the parent table. This arrangement makes it more convenient to logically organize and understand data models. Most importantly, the highest level of table should appear in the upper left corner, which makes the lower level table appear on the lower right corner of the page. For the sake of clarity, it is also important to reduce the number of intersections in the figure. As Eberhardt Rechtin emphasizes in the Art of Systems Architecting, "a good design often looks very comfortable." If you arrange, your data model looks very chaotic, then it may be telling your data model itself having some worthless problems. Figure 7A: Complete ER 图 (PowerDesigner)
Figure 7B: Complete ER (Visio)
V. From the map to the database
Depending on the package you choose to create a data model, modeling tools may generate SQL commands or directly modify database modes based on the model. This feature brings great convenience; and this approach has many advantages compared to SQL scripts using ASCII format. Some models of modeling tools are suitable for a large number of database types, such as PostgreSQL, MySQL, Oracle, DB2, and more. For simple database modifications, the change operation can be done directly from the modeling tool through ODBC. Database modifications also allow for incrementally (for example, alter commands, or create commands, and update commands for specific tables). When you first use modeling tools, you can view SQL generated by modeling tools to see if you can trust and recognize the interpretation of the modeling tool on the data model. After a while, you will be familiar with the explanation of modeling tools to various relationships and table details.
[Conclusion] Data modeling is a good software engineering practice. It helps you plan data needs before formal writing program code. Data modeling is equally useful in maintenance and improvement of the data layout of the system. Some tools can make this process very simple and can bring great help when you manage and design a database system. However, according to the different features you need, the price of modeling tools has great differences. The best way to easily and use data modeling technology without the budget deficit, starting from a small tool and then gradually in-depth and improved.
Six, reference and resources
■ Tools
Sybase PowerDesigner - a high-end data modeling tool. You can download a 45-day trial version. Erwin - a high-end data modeling tool. Download the trial version. Rational Rose Enterprise - A high-end UML tool, just a database modeling support. Download the trial version. Visio Professional - A low price of drawing tools that can be used to generate data models, UML maps, etc. Enterprise Edition also supports two-way engineering capabilities for various databases. You can order a 60-day trial version of the CD. Dezign - an extremely low price ERD modeling tool. You can download a restricted trial version. Erd Tool List - A list of links and resources for various databases and UML modeling tools.
■ Reference
Database Design for the Web Designing Databases Erd Notation Summary On-line Database Books Diagramming Methods Introduction To Er Model
