Data Modeling & Design
This article discusses the three types of data models: Relational, Dimensional, and Associative entity. The process for developing a data model starts by identifying the different types of business entities and their properties. After this is complete, the data model is drafted, showing how entities are related to each other. Next, it maps data attributes to entities. The model is validated, and any needed changes are made to ensure accuracy.
Conceptual data model
A Conceptual Data Model is used to create an information solution for the business. This model shows the context of data, which can be very useful when providing information solutions to clients. The key to building a Conceptual Data Model is to distinguish between identifying relationships and cardinality. An entity cannot exist without its parent. The order in which the child entities are stored should be reflected in the Conceptual Data Model.
Cardinality is a rule that identifies the relationship between entities and sub-classes. It specifies the number of rows required for each entity, and it indicates the relationship type. Entities are structured abstractions of concepts, while relationships are streamlined representations of them. They should follow the principles of cardinality to avoid potential data model traps. When choosing the entity types, remember that the cardinality constraints should be true. By following these principles, your data model should be more consistent and general.
A conceptual data model is a high-level representation of the business's data. It helps identify key entities and relationships, and it is usually platform-independent. Conceptual data models help organizations envision their future data model even beyond their current capabilities. Because the Conceptual Data Model is based on a business perspective, it helps identify gaps in current capabilities and justify investments. These models are a key part of the data modeling and design process.
Developing a conceptual data model involves a systematic approach to designing a system. The process is often described as a series of layers, each layer refining the understanding and specifying specific design features. In each layer, there are key roles that indicate who has what responsibility in the data management process. For example, solid BLUE links denote direct relationships between two data silos, while dashed RED lines indicate indirect relationships. Dotted green lines indicate relationships between multiple silos.
Relational data model
In relational data modeling, data are organized into tables where specific elements link to other information. These elements are known as entities and can have a one-to-one or a one-to-many relationship. Relational databases are often used in point-of-sale systems. The entity-relationship model helps to visually represent the relationships between different system elements and aligns technical and non-technical stakeholders.
A relationship-based data model is created by identifying key properties that differentiate entities. A draft entity-relationship model depicts the connections between entities. Finally, data attributes are mapped onto the entities. After completing the physical data model, the data architect and database engineer validate the model to ensure its accuracy and make necessary changes. Then, the model is created and made available to the relevant business unit. The final version of the relationship-based model must be validated and tested for accuracy and consistency.
A relational data model supports all types of relationships between data. The many-to-many relationship, for example, requires decomposition into an additional table. These relationships are linked to each other via primary and foreign keys. In some cases, a candidate key can also be used as a primary key. This model provides a high level of flexibility and is ideal for transaction-processing applications. However, it can be costly to implement if you are unable to implement all the features your business requires.
A relational data model allows for complex relationships, and is a popular choice in relational databases. This model is a flexible alternative to other models and was introduced by E. F. Codd in the 1970s. In addition to its flexibility, relational databases are flexible and scalable, and are commonly written in Structured Query Language (SQL).
Dimensional data model
A dimensional data model (DDM) is an approach that stores hierarchies in a table. A location dimensional table, for example, stores hierarchies from city to country, state to county, and so on. Each row of the table contains an individual lookup for that location. In other words, one row represents one student, while another row represents a school. This way, it is possible to create complex data models with hundreds or even thousands of dimensions.
The DDM involves several tables associated with one fact table. Each table is surrounded by its own set of dimension tables. Foreign keys can refer to the measured values in the dimension tables. This means that you can perform queries on each table separately, drill down, and roll up. The number of dimension tables associated with a fact table is inversely proportional to the granularity of the measurement values. Hence, if the granularity is high, the fact table will contain many more dimension tables.
A dimensional model is also called a star schema. A star schema has a central table, which is called the fact table. Another table is called the dimension table, and has a single or multiple joins. A simple dimensional model would include a business selling different products to different markets. It would also show the performance of the business over time. But there is a downside to this approach. It's more rigid than it is intuitive, and its use is limited by business needs.
The term "snowflake dimension" refers to a set of normalized tables for a single business entity. The snowflake dimension, for instance, uses three tables related to each other. It stores fewer rows in a model than a fact table. The degenerate dimension would only increase model size and clutter the Fields pane. This type of model is used when the data in the model is not self-descriptive.
Associative entity data model
Associative entities convey information about attributes and relationships. They are considered both entities and relationships and typically have many relationships. Regardless of the relationship, an associative entity should have a distinguishable attribute from the identifier entity. This allows the entity to be a participant in many relationships, which are separate from associated entity relationships. In this way, associative entities are often the most efficient and flexible data modeling and design approach.
The relationships between entities reflect business rules. For example, citizenship is an example of a many-to-many relationship. A person may be a citizen of several countries. A FIBO graph would depict a Person and Country, both of which have a "Citizenship" object property. This type of connection is made possible through the domain and range properties of an entity. Thus, a triple of Person, has Citizenship, and country can be constructed from any other three-entity pair.
The many-to-many relationship can be resolved automatically with an associative entity. A many-to-many relationship can also be resolved manually. In order to resolve a many-to-many relationship, you simply right-click a relationship and select "Create Association Entity." If you are using Dimensional Modeling notation, these relationships are referred to as fact tables.
An Associative entity data model is a great way to design a relational database. Entities are composed of two types of data: strong and weak. An associative entity is a set of entities associated together. Using an associative entity model, you can easily represent relationships within an entity. And when you're creating a complex model, the associative entity data model will help you build the schema for your database.
Hierarchical data model
The use of a hierarchical data model is common in physical models of databases, such as disk storage systems. It helps to prevent data from being reorganized in a single-level model because it avoids using one-to-many relationships that increase redundancy. A good example of a hierarchical model is a college student database system. Each student may take as many courses as they want.
To develop a hierarchical data model, developers need to understand the subject matter. For example, in a health-care delivery scenario, developers can separate logic for clinician office visits from logic for visits to a primary care clinic. However, they will likely still need a table of non-primary care providers when performing other data analytic tasks. This data modeling and design approach can be expensive and time-consuming, so developers must be aware of the costs involved.
Using a hierarchical data model is especially helpful in relational databases. It can describe many real-world relationships. For example, a department may have several courses, multiple professors, and many students. The database structure can be structured in this way, making it more efficient to index and retrieve data. This method also works well with linear data storage mediums. It allows for easy addition and deletion of information.
The hierarchical data model is a tree-like representation of one-to-many relationships. It uses one root records, each of which maps to one or more child tables. The hierarchy data model first came about in the mid-60s and gained widespread use in banking and other applications. Although less efficient than more modern database models, it is still widely used, particularly in geographic information systems and XML systems.