Know How to Read an ER Model

From WikiContent

(Difference between revisions)
Jump to: navigation, search
Line 1: Line 1:
-
Reading a data model is like reading a map; some elements of a map are instantly recognizable and understandable while a professional cartographer can discern more information than a casual user or observer. Reading an ER model is like other information we learn, it helps to start with a wide view and to learn basic information then continue learning finer points and intricacies. Here are four essential elements to understanding an ER Model.
+
Software applications store data in databases. Database administrators, system architects and developers design the layout. The ER model is the visual of how the data is stored. An ER model is an entity relationship model.
-
+
Being able to read and ER model is like reading a map; some elements of a map are more readily understandable. Learning to read an ER model is like other information we learn, it helps to start with a over view and continue learning with understanding the finer points and intricacies.
-
1. Tables
+
-
Begin by identifying the core tables of the data model; core tables store the most frequently used information of the application the model supports. Become familiar with the data stored on these tables as they represent the most important data aspects of the application. Note the naming conventions of the tables and the data fields. Over time, you may be able to discern discrepancies in naming conventions.
+
An entity is also referred to as a table, tables store data. For example, imagine a database that stores customer information. The tables in a customer database might consist of a customer table, an address table and a phone number table.
 +
The R of the ER model refers to the relationships, the relationships the tables and data have to each other. Each table has columns. Each column represents a data field, from the application, the same data entry fields we see in an application are the fields we’ll find on the ER model.
-
2. Columns
+
Using our customer database example, the columns on a customer table might include: company name, company website and company president. The address table might include these columns of data: street name, suite number, city, state and zip code.
-
Look for the tables and columns with the highest counts; this is the data that grows in the application. Recognize that for each query pulling data from the highest-count tables and columns, these are the areas of the application where performance may pose an issue. Frequently accessed data is where the application may benefit from building a cache. Consider at what frequency interval that cache needs to be updated.
+
As each company gets added or created through the application, a row of information for that company gets added to the tables. Columns and rows in a database can be compared to an Excel file where the column headers represent what data is stored and each row of the spreadsheet represents the data itself. Tables in a database are made up of columns and rows.
 +
A primary key is used to uniquely identify each row in each table. The primary key is comprised of a single column or set of columns. No two rows can have the same value for the primary key. An example would be using the customer name field as the primary key on the customer table.
-
 
+
A foreign key is a referential constraint between two tables. The foreign key identifies a column or a set of columns as a reference to another table. Using the customer database example, a column on the address table is needed to refer back to the customer table. This relationship links the data and the tables together.
-
3. Data Types
+
Having a sense of the data model is like knowing the primary highways through a state or province. Once you get familiar with reading a data model, you’ll find you won’t want to work on an application without seeing the model. It would be like leaving your house without at GPS or road atlas – the data model is navigation tool into the inner working of an application.
-
 
+
-
Observe the data types used to store information. Ask yourself: do the data types used have limitations or boundaries that could pose an issue in the application? Is the data type large enough? Or is the data type too large which can cause overall application storage issues in the future. Are there data types used that are not searchable – will this be acceptable to the end users of the application?
+
-
 
+
-
 
+
-
4. Keys and Relationships
+
-
 
+
-
Become aware of the data relationships. What are the primary keys? What are the foreign keys? Being able to see and visualize the relationships in the data mode helps you to see the complexities of the queries and joins needed for searching and report writing. When you learn the “spread” of the tables in the database, you can plan for the complexities of CRUD transactions. (CRUD, create, read, update, delete.)
+
-
 
+
-
 
+
-
Having a sense of the data model is like knowing the primary highways through a state or province. Knowing the most populated areas tables and columns compares to knowing the most populated cities on a map and being able to anticipate traffic issues and performance issues. Primary and foreign keys help you to see how information relates to each other. Much like how to navigate from one highway to a surface road and then down to the specific address or data you need to reach. Once you get familiar with reading a data model, you’ll find you won’t want to work on an application without seeing the model. It would be like leaving your house without at GPS or road atlas – the data model is navigation tool into the inner working of an application.
+

Revision as of 04:55, 21 December 2009

Software applications store data in databases. Database administrators, system architects and developers design the layout. The ER model is the visual of how the data is stored. An ER model is an entity relationship model.

Being able to read and ER model is like reading a map; some elements of a map are more readily understandable. Learning to read an ER model is like other information we learn, it helps to start with a over view and continue learning with understanding the finer points and intricacies.

An entity is also referred to as a table, tables store data. For example, imagine a database that stores customer information. The tables in a customer database might consist of a customer table, an address table and a phone number table.

The R of the ER model refers to the relationships, the relationships the tables and data have to each other. Each table has columns. Each column represents a data field, from the application, the same data entry fields we see in an application are the fields we’ll find on the ER model.

Using our customer database example, the columns on a customer table might include: company name, company website and company president. The address table might include these columns of data: street name, suite number, city, state and zip code.

As each company gets added or created through the application, a row of information for that company gets added to the tables. Columns and rows in a database can be compared to an Excel file where the column headers represent what data is stored and each row of the spreadsheet represents the data itself. Tables in a database are made up of columns and rows. A primary key is used to uniquely identify each row in each table. The primary key is comprised of a single column or set of columns. No two rows can have the same value for the primary key. An example would be using the customer name field as the primary key on the customer table.

A foreign key is a referential constraint between two tables. The foreign key identifies a column or a set of columns as a reference to another table. Using the customer database example, a column on the address table is needed to refer back to the customer table. This relationship links the data and the tables together. Having a sense of the data model is like knowing the primary highways through a state or province. Once you get familiar with reading a data model, you’ll find you won’t want to work on an application without seeing the model. It would be like leaving your house without at GPS or road atlas – the data model is navigation tool into the inner working of an application.

Personal tools