Data Modeling

A data model is one of the most important tools in the design process, but it has to be done right. A common misconception is that a data model is a picture of a database. That is partly true, but a model can do so much more. A great data model covers pretty much everything about a database and serves as the primary documentation for the life cycle of the database. Aspects of the model will be useful to developers, users, and the my self also database administrators (DBAs) who maintain the system. The basic concept of data modeling, in which a representation of your database will be produced that shows the objects involved in the database design and how they interrelate.

It is really a description of the exterior and interior parts database, with a graphical representation being just one facet of the model. Best of all, using a good tool, you can practically design the basics of a system live, right with your clients as they describe what they want hopefully, someone else is gathering client requirements that are not data-structure related. Data modeling and introduce the language

I prefer for data modeling IDEF1X methodology to model and document the following:

*     Entities/tables

**    Attributes/columns

***  Relationships

**** Descriptive information

In the process of creating a database, we will start out modeling entities and attributes, which do not follow very strict definitions, and refine the models until we end up producing tables and columns we will primarily refer to entities during the modeling exercises, unless we’re trying to demonstrate something that would be created in SQL Server. The same data modeling language will be used for the entire process of modeling the database, with some changes in terminology. After introducing IDEF1X, and the Chen Entity Relationship Model (ERD) methodology.

Data Modeling

Data modeling is a skill at the foundation of database design. In order to start designing databases, it is very useful to be able to effectively communicate the design as well as make it easier to visualize. Several popular modeling languages are available to use, and each is generally just as good as the others at the job of documenting a database design. The major difference will be some of the symbology that is used to convey the information. When choosing my data modeling methodology, I looked for one that was easy to read and could display and store everything required to implement very complex systems.

The modeling language is Integration Definition for Information Modeling (IDEF1X) – or more info visit here

Data modeling methodology may be a personal choice, economics, company standards, or features usually influence tool choice, there are hundred’s of few here:

* AllFusion ERwin Data Modeler, to know more click here

* Toad Data Modeler, to know more click here

* ER/Studio, to know more click here 

* Visible Analyst DB Engineer, to know more click here

* Visio Enterprise Edition, to know more click here

In IDEF1X, singular entity/table names lead to the ability to read the names of relationships naturally. But honestly, plural or singular naming might be worth afew long discussions with fellow architects, but it is certainly not something to get burned at the stake over. If the organization you find yourself beholden to uses plural names, that doesn’t make it a bad place to work. The most important thing is to be consistent and not let your style go all higgledy-piggledy as you go along. For naming entities:

* Entity names should never be plural. The primary reason for this is that the name should refer to an instance of the object being modeled, rather than the collection. This allows you to easily use the name in a sentence. It is uncomfortable to say that you have an “automobiles row,” for example—you have an “automobile row.” If you had two of these, you would have two automobile rows.

* The name given should directly correspond to the essence of what the entity is modeling. For instance, if you are modeling a person, name the entity Person. If you are modeling an automobile, call it Automobile. Naming is not always this straightforward, but keeping the name simple and to the point is wise. If you need to be more specific, that is fine too. Just keep it unlike this explanation.

Entity names frequently need to be made up of several words. During the conceptual and logical modeling phases, including spaces, underscores, and other characters when multiple words are necessary in the name is acceptable but not required. For example, an entity that stores a person’s addresses might be named Person Address, Person Address, or using the style I have recently become accustomed to and the one I’ll use in this expl: sample database, Person Address. This type of naming is known as Pascal case or mixed case. (When you don’t capitalize the first letter, but capitalize the first letter of the second word, this style is known as camel Case.) Just as in the plural/singular argument, there really is no “correct” way; these are just the guidelines that I will follow to keep everything uniform.

Regardless of any style choices you make, very few abbreviations should be used in the logical naming of entities unless it is a universal abbreviation that every person reading your model will know. Every word ought to be fully spelled out, because abbreviations lower the value of the names as documentation and tend to cause confusion. Abbreviations may be necessary in the implemented model because of some naming standard that is forced on you or a very common industry standard term. Be careful of assuming the industry-standard terms are universally known. For example, at the time of this writing, I am helping breaking in a new developer at work, and every few minutes, he asks what a term means—and the terms are industry standard.

If you decide to use abbreviations in any of your names, make sure that you have a standard in place to ensure the same abbreviation is used every time. One of the primary reasons to avoid abbreviations is so you don’t have to worry about different people using Description, Descry, Desc, Descrip, and Descriptn for the same attribute on different entities. In SQL Server Integration Services SSIS packages, I commonly name each control with a three- or four-letter prefix to help identify them in logs. However, with database objects, questioning whether a name refers to a column or a table is rare.


Just as with entity names, there is no need to include Hungarian notation prefixes or suffixes in the attribute or implementation names. The type of the attribute can be retrieved from the system catalog The aspects of attributes on your data model:


* Primary keys

* Alternate keys

* Foreign keys

* Domains

* Attribute naming

Relationships: Up to this point, the constructs we have looked at have been pretty much the same across most data modeling methodologies. Entities are always signified by rectangles, and attributes are quite often words within the rectangles. Relationships are where things start to diverge greatly, as many of the different modeling languages approach representing relationships graphically a bit differently. To make the concept of relationships clear, I need to go back to the terms “parent” or father and “child or son.”  Relationships are a key topic in database design but not a completely simple one

* Entity, Child: The entity in a specific connection relationship whose instances can be related to zero or one instance of the other entity (parent entity)

* Entity, Parent: An entity in a specific connection relationship whose instances can be related to a number of instances of another entity (child entity)

* Relationship: An association between two entities or between instances of the same entity In IDEF1X, every relationship is denoted by a line drawn between two entities, with a solid circle at one end of that line to indicate where the primary key attribute is migrated to as a foreign key. Relationships come in several different flavors that indicate how the parent table is related to the child.

* Identifying, where the primary key of one table is migrated to the primary key of another. The child will be a dependent entity.

* Nonidentifying, where the primary key of one table is migrated to the non primary key attributes of another. The child will be an independent entity as long as no identifying relationships exist.

* Optional identifying, when the nonidentifying relationship does not require a child value.

* Recursive relationships, when a table is related to itself.

* Subtype or categorization, which is a one-to-one relationship used to let one entity extend another.

* Many-to-many, where an instance of an entity can be related to many in another, and in turn, many instances of the second entity can be related to multiples in the other.

Physical Data Model are to be check …
A. Are all needed data described within the proper entities?
B. Are the types – sizes of the fields defined as appropriate?
C. Are any existing data structure taken into consideration?
D. Are the relationships between entities clearly described?
E. Are the cardinalities clearly defined?
F. Are primary, fk and secondary keys defined? a real life case, where non-semantic items (e.g. auto-incremental numbers) are used as primary keys.
G. Are the entities normalized up to the 2nd or 3rd normal form?

Other modeling methodologies that you will likely run into with tools you may use when looking for database information on the Web. You will see a lot of similarities among them—for example, most every methodology uses a rectangle to represent a table and a line to indicate a relationship. You will also see some big differences among them, such as how the cardinality and direction of a relationship is indicated.One of the most popular methodologies uses multiple lines on one end and several dashes to indicate the same things. Still others use an arrow to point from the child to the parent.

* Information engineering (IE): The other main methodology, which is commonly referred to as the crow’s feet method

* Chen Entity Relationship Model (ERD): The methodology used mostly by academics,though you can run into these models online

* Visio: A tool that many developers have handy that will do an admirable job of helping you to design a database

* Management Studio database diagrams: The database object viewer that can be used to view the database as a diagram right in Management Studio

AgileData site, click here

IBM’s Rational documentation site click here, and many others tools out there.

Microsoft Certified Solutions Associate (MCSA)