Data modeling
Text books
A very good book on Data Modeling - essential for ETL and Data Engineering jobs.
The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling Subsequent Edition by Ralph Kimball
Database model
A database model is the theoretical foundation of a database and fundamentally determines in which manner data can be stored, organized, and manipulated in a database system. It thereby defines the infrastructure offered by a particular database system. The most popular example of a database model is the relational model.
Dataflow diagram DFD
DFD is a graphical representation of the flow of data through an information system, modeling its process aspects. it is a preliminary step used to create an overview of the system which can be later elaborated.
Data model
A high-level data model in business or for any functional area is an abstract model that documents and organizes the business data for communication between functional and technical people. It is used to show the data needed and created by business processes.
Data modeling
Data modeling in software engineering is the process of creating a data model for an information system by applying formal data modeling techniques. Data modeling is a process used to define and analyze data requirements needed to support the business processes within the scope of corresponding information systems in organizations. Therefore, the process of data modeling involves professional data modelers working closely with business stakeholders, as well as potential users of the information system.
Relations between tables
When to use which?
To determine relationships in SQL:
- One-to-Many (1:N): One entity instance relates to many instances of another entity.
- Many-to-One (N:1): Many instances of one entity relate to one instance of another entity.
- Many-to-Many (N:M): Many instances of one entity relate to many instances of another entity.
One to one
One user can have only one car and one car can only belong to one user.
@startuml
' hide the spot
' hide circle
' avoid problems with angled crows feet
skinparam linetype ortho
entity "User" as e01 {
*user_id : number <<generated>>
--
*name : text
description : text
andSomeOtherFields : text
}
entity "Car" as e02 {
*car_id : number <<generated>>
--
*user_id : number <<FK>>
other_details : text
}
e01 ||..|| e02
@enduml
@startuml
skinparam titleFontSize 14
title
User table
|= user_id |= user_name |
| 1 | a name |
| 2 | b name |
| 3 | c name |
end title
@enduml
@startuml
skinparam titleFontSize 14
title
Car table
|= car_id |= user_id |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
end title
@enduml
One to many
The general rule in databases is to stick to one-to-many relationships, and if you ever encounter a many-to-many relationship and truly need to keep it, then create an intermediary table between the two.
One user can have many cars. But a given car can only belong to one user.
@startuml
' hide the spot
' hide circle
' avoid problems with angled crows feet
skinparam linetype ortho
entity "User" as e01 {
*user_id : number <<generated>>
--
*name : text
description : text
andSomeOtherFields : text
}
entity "Car" as e02 {
*car_id : number <<generated>>
--
*user_id : number <<FK>>
other_details : text
}
e01 ||..|{ e02
@enduml
@startuml
skinparam titleFontSize 14
title
User table
|= user_id |= user_name |
| 1 | a name |
| 2 | b name |
| 3 | c name |
end title
@enduml
@startuml
skinparam titleFontSize 14
title
Car table
|= car_id |= user_id |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
end title
@enduml
Many to many
Typically a many-to-many relationship requires an intermediate table between the two entities. For instance, if you want a many-to-many relationship between Students and Classes (a Student can have more than one Class, and a Class can have more than Student) then you need a ClassStudent table to establish the many-to-many relationship.
One user can have many cars. A car can belong to many users.
@startuml
' hide the spot
' hide circle
' avoid problems with angled crows feet
skinparam linetype ortho
entity "User" as e01 {
*user_id : number <<generated>>
--
*name : text
description : text
andSomeOtherFields : text
}
entity "Car" as e02 {
*car_id : number <<generated>>
--
other_details : text
}
entity "UserCar" as e03 {
*id : number <<generated>>
--
*user_id : number <<FK>>
*car_id : number <<FK>>
}
e01 ||..|{ e03
e02 ||..|{ e03
@enduml
@startuml
skinparam titleFontSize 14
title
User table
|= user_id |= user_name |
| 1 | a name |
| 2 | b name |
| 3 | c name |
end title
@enduml
@startuml
skinparam titleFontSize 14
title
Car table
|= car_id |
| 1 |
| 2 |
| 3 |
end title
@enduml
@startuml
skinparam titleFontSize 14
title
UserCar table
|= user_id |= car_id |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
| 1 | 3 |
| 2 | 3 |
| 3 | 3 |
end title
@enduml