Database design

Conception de la base de données

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
Conception de la base de données

Data modeling

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

  • Tools: database models and schemas, e.g., relational model and star schema

3. Physical data model: describes physical storage

  • Tools: partitions, CPUs, indexes, backup systems and tablespaces
1 https://en.wikipedia.org/wiki/Data_model
Conception de la base de données

Conceptual - ER diagram

ER diagram of song example

Entities, relationships, and attributes

Logical - schema

Logical schema of song example

Fastest conversion: entities become the tables

Conception de la base de données

Other database design options

Another example of a potential ER diagram of song example

Determining tables

Another example of a potential ER diagram of song example

Conception de la base de données

Beyond the relational model

Dimensional modeling

Adaptation of the relational model for data warehouse design

  • Optimized for OLAP queries: aggregate data, not updating (OLTP)
  • Built using the star schema
  • Easy to interpret and extend schema
Conception de la base de données

Elements of dimensional modeling

Fact table and dimension tables of song example

Organize by:

  • What is being analyzed?
  • How often do entities change?

Fact tables

  • Decided by business use-case
  • Holds records of a metric
  • Changes regularly
  • Connects to dimensions via foreign keys

Dimension tables

  • Holds descriptions of attributes
  • Does not change as often
Conception de la base de données

Let's practice!

Conception de la base de données

Preparing Video For Download...