Database design

Database Design

Lis Sulmont

Curriculum Manager

What is database design?

Determines how data is logically stored
- How is data going to be read and updated?
Uses database models: high-level specifications for database structure
- Most popular: relational model
- Some other options: NoSQL models, object-oriented model, network model
Uses schemas: blueprint of the database
- Defines tables, fields, relationships, indexes, and views
- When inserting data in relational databases, schemas must be respected

Process of creating a data model for the data to be stored

1. Conceptual data model: describes entities, relationships, and attributes

Tools: data structure diagrams, e.g., entity-relational diagrams and UML diagrams

2. Logical data model: defines tables, columns, relationships

3. Physical data model: describes physical storage

¹ https://en.wikipedia.org/wiki/Data_model

ER diagram of song example

Entities, relationships, and attributes

Logical schema of song example

Fastest conversion: entities become the tables

Another example of a potential ER diagram of song example

Determining tables

Another example of a potential ER diagram of song example

Adaptation of the relational model for data warehouse design

Fact table and dimension tables of song example

Organize by:

Fact tables

Dimension tables

Database Design