Data Modeling - Entity (Object, Table)
Table of Contents
1 - About
- A concept to abstractly represent all instances of a group of similar “things”
- Class of persons, places, objects, events, or concepts about which we need to capture and store data.
- A person, place, event, or thing about which data is collected data is collected
Entity must be multiple occurrences (of instance) to be an entity An entity instance is a single occurrence of an entity.
An entity is commonly implemented by a table and an instance by a row.
In relational maths, the terms:
- Table means a relation.
- Row means a tuple.
- Column means an attribute.
2 - Articles Related
3 - 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
3.1 - 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.
3.1.1 - Fact Dimension / Master detail transaction table
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.
3.2 - 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.
3.3 - Component Entities
These entities are directly related to a transaction entity by a one-to-many relationship.
3.4 - 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.
3.5 - 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”.
3.6 - 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.
4 - Other Notations
4.1 - Mathematical Notation: A set
4.2 - Programming language: A collection
5 - Naming Convention
- The name of a database table is often plural (for example, owners, pets, specialities, and so on).
- The name of the column contains a prefix of its table name.