Logical Data Modeling - Entity Set (Class, Table)
About
An entity set is a set of entity.
An entity set is implemented:
- in a relational database by a table and an entity by a row
Not to confound with the entity of a language
Articles Related
Categories/classification of entities
At a high level:
- transaction entities correspond to fact tables
- component entities correspond to dimension tables
- classification tables correspond to hierarchy tables
Transaction/Facts Entities
These entities record details of business events (e.g., orders, shipments, payments, insurance claims, bank transactions, hotel bookings, airline reservations, and hospital admissions).
Generally, this tables are at the many end of a all one-to-many relationship.
Fact Dimension / Master detail transaction table
One exception to this rule is a fact dimension which has a one-to-many relationship (master detail) with the facts entities but still describe business events.
For instance, an Order Header and an Order Item table describe the same business event but with different levels of detail. The Order item table has the more detailed data whereas the Order header is grouped by order number and therefore its attributes can be in a dimensional model degenerated
This transaction entities are connected in a master-detail structure, they should be combined into a single fact table, as they represent different views of the same event. The split into “master” and “detail” is simply a requirement of normalization (1NF).
All attributes of the master record should be allocated down to the item level if possible (Kimball, 1995). For example, if discount is defined at the master (Order) level, the total discount amount should be allocated at the item level (e.g., in proportion to the price for each item). The same should be done to delivery charges, order level taxes, fees, etc.
Snapshot Entities
This entities recording a static level of some commodity at a point in time (e.g., account balances, customer sold and inventory levels). These do not record business events as such, but the effect of events on the state of an entity.
For instance, a stock level table which records a static level.
Component Entities
These entities are directly related to a transaction entity by a one-to-many relationship.
Classification / Lookup Entities
These entities are related to a component entity by a chain of one-to-many relationships. These define embedded hierarchies in the data model and are used to classify component entities.
Others
Some of the entities do not fit into any of these categories. Such entities do not fit the hierarchical structure of a dimensional model and therefore cannot be represented in the form of a star schema and must be “dimensionalized”.
Dates and Times Dimension
Date and/or Time appear as explicit dimensions in most star schemas to support different types of historical analyses. These are not normally represented as entities in operational systems.
Date and Time are usually represented as separate dimensions to reduce the size of the dimension tables (Kimball, 1996).
Dates must be explicitly modeled in a dimensional schema, whereas at the operational level they are represented as data types.
Other Notations
Mathematical Notation: A set
The mathematical concept of a set is just a group of unique items, meaning that the group contains no duplicates.
Programming language: A collection
A (Collection|Container) Data Type (Set, Bag, Sequence)
Naming Convention
- The name of a entity is often plural (for example, owners, pets, specialities, and so on).