In our work, my colleagues and I often see how companies are faced with the problem of data management - when there are a lot of tables and queries and it is very difficult to manage all this. In such situations, we recommend modeling the data. To understand what it is, I translated an article-review about data modeling from Towards Data Science, in which, in addition to basic terms and concepts, you can find an illustrative example of the use of data modeling in retail. Forward under the cut!
If you look at any software application, you will see that, at a fundamental level, it organizes, processes, and presents data to fulfill business requirements.
A data model is a conceptual representation for expressing and communicating business requirements. It visually shows the nature of the data, the business rules that govern the data, and how the data will be organized in the database.
Data modeling can be compared to building a house. Let's say ABC needs to build a guest house (database). The company calls the architect (data modeler) and explains the building requirements (business requirements). The architect (data modeler) develops the plan (data model) and submits it to ABC. Finally, ABC calls in civil engineers (DBAs and DBAs) to build a house.
Key terms in data modeling
Entities and attributes. Entities are “things” in the business environment that we want to store data about, such as products, customers, orders, and so on. Attributes are used to organize and structure data. For example, we need to store certain information about the products we sell, such as selling prices or available quantities. These pieces of data are attributes of the Product entity. Entities are usually database tables, and attributes are columns of those tables.
Relationship . The relationship between entities describes how one entity is related to another. In the data model, entities can be related as one-to-one, many-to-one, or many-to-many.
. « », , « » « ».
: 2 — . , .
, « »:
ER . ER- , .
, . :
— . , , , - -. :
, , , . , , — . :
:
vs
- . — , . , . 3- . OLTP .
. , .
ABC 200 . , « », «», « », « » .. 20 000 . (SKU). 6 000 -, . - (UPC). POS- 2 : , , .
, . , , ..
.
1. -
, POS-. , , , . , , .
2:
. . , . :
Date Dimension
Product Dimension
Store Dimension
Promotion Dimension
, (, , .), .
3:
, . :
4:
. :
Date Dimension:
Product:
Store:
Promotion:
Sales Fact:
.
(, ).
: * .
: , .
: - .
:
Step 5: creating physical tables in the database
Using a data modeling tool or custom scripts, you can now create physical tables in the database.
I think it has become quite obvious now that data modeling is one of the most important tasks in developing a software application. And it lays the foundation for organizing, storing, retrieving, and presenting data.