I am learning database design and trying to model using Visio 2013. When I want to create database model, I came across the following database notation in Visio 2013:
1) Crow's Foot database notation
2) Chen's database notation
3) UML database notation
4) IDEF1X database notation
Why do we have many notations for designing a database and among them is the one that is being widely used in real database design?
Can anyone help me get this doubt clarified?
There are many factors that influenced the introduction of multiple notations. Existing notations might've been unknown to an author, limited to specific corporations or groups, or lacked certain expressive abilities. Some notations were even driven by aesthetic concerns.
Crow's foot notation was introduced by Dr. Gordon Everest. It's widely used in table diagrams to represent foreign key constraints (along with the relative cardinalities of rows in the two tables). It's also commonly used in network data models to represent binary relationships between entities.
Entity-Relationship modeling notation was introduced by Peter Chen to visually represent entity-relationship models. Unlike Crow's foot diagrams, it supports n-ary relationships and attributes on relationships.
UML is a modeling language for describing software systems. UML class diagrams are often (incorrectly?) used for database modeling. Like Crow's foot notation, class diagrams support only binary relationships without attributes, limiting it to network data models.
IDEF1X was developed by the US Air Force as part of a family of modeling notations. It, too, supports only binary relationships without attributes, though it has more semantic elements than Crow's foot diagrams.
Of the notations above, Chen's notation stands out as the only one supporting n-ary relationships with attributes. It's better suited for conceptual data models, but it's not logically complete. Crow's foot and IDEF1X are closer to physical data models. UML class diagrams are better suited for modeling systems than data.
An important modeling notation you missed is Object-Role modeling. It supports n-ary relations and focuses on representing facts rather than entities. It's not as common as the other notations above, perhaps because it's based on solid logic and relational theory rather than the old but intuitive network data models.