Modelowanie struktury danych dla bazy relacyjnej

Jest na blogu artykuł przedstawiający prototypowania relacyjnej bazy danych z użyciem Entity Framework. Skupia się on na technicznej stronie pracy pokazując szybki sposób uruchomienia bazy.

Dziś trochę bardziej skomplikowany przykład (chociaż wciąż prosty), w którym skupię się na modelowaniu danych. Pokażę też, jak wykorzystać Entity Framework do odwzorowania modelu w aplikacji .NET, utworzenia bazy na serwerze i wdrażania zmian w potoku CI/CD.

Zadanie

Przykład, jak zawsze, będzie praktyczny. Teorię ograniczamy do minimum. Biblioteki są pełne podręczników z teorią. Praktyka developera polega na tym, że rozwiązuje zadania.

Zadanie brzmi:
Zaprojektuj struktury danych, które obsłużą system ankietowy. Ankieta może składać się z wielu pytań, a odpowiedzi mogą być tekstowe

Dość ogólne i pole do interpretacji jest duże. Powiedzmy, że po sesji QA powstała tabelka z wymaganiami.

Projekt struktury danych

Mamy tu wymagania funkcjonalne (będą przydatne później) oraz wymagania co do struktury danych (#16, #17, #18, #19). Jest też jedno wymagania techniczne (#1). Po odfiltrowaniu tego, co decyduje o strukturze danych rysują się następujące encje i relacje (bez opcjonalnych #19 i #19):

• użytkownik
• ankieta
  • użytkownik tworzy ankietę
  • może zaprosić innych użytkowników do jej wypełnienia, czyli należy do użytkowników przy czym jeden jest “właścicielem”
• pytanie
  • należy do ankiety
  • ankieta może mieć wiele pytań (>=0)
• odpowiedź na pytanie
  • należy do pytania
  • każdy zaproszony użytkownik może udzielić innej odpowiedzi

Zapisując to jako schemat tabel mamy:

   ┌─────────────────────────────┐
   │ users (użytkowicy)          │
   ├─────────────────────────────┤
┌─1│ PK id          int identity │
│  │ UQ name        nvarchar     │
│  └─────────────────────────────┘
│  ┌─────────────────────────────┐     ┌────────────────────────────────┐
│  │ user_poll (tabela pośrednia)│     │ polls (ankiety)                │
│  ├─────────────────────────────┤     ├────────────────────────────────┤
│  │ PK,FK id_poll  int          │*───1│ PK id          int identity    │1─┐
├─*│ PK,FK id_user  int          │     │    title       nvarchar        │  │
│  │       is_owner bit          │     │    description nvarchar NULL   │  │
│  └─────────────────────────────┘     └────────────────────────────────┘  │
│  ┌─────────────────────────────┐     ┌────────────────────────────────┐  │
│  │ answers (odpowidzi)         │     │ quetions (pytania)             │  │
│  ├─────────────────────────────┤     ├────────────────────────────────┤  │
│  │ PK id          int identity │  ┌─1│ PK id          int identity    │  │
│  │ FK id_question int          │*─┘  │ FK id_poll     int             │*─┘
└─*│ FK id_user     int          │     │    question    nvarchar(1000)  │
   │    answer      nvarchar     │     └────────────────────────────────┘
   └─────────────────────────────┘

Każda tabela jest w 3 postaci normalnej (3NF) a właściwie nawet spełnia wymagania postaci normalnej Boyce’a-Codda (BCNF).

Wymagania dodatkowe

Struktura danych będzie wykorzystana w aplikacji webowej (wymaganie #1) a backend wykorzystuje Entity Framework. Wiąże się z tym potrzeba dodatkowych tabel przechowujących, np:

• historię migracji
• klucze szyfrujące dane cookie, które odpowiada za spełnienie wymagania #3
• dodatkowa tabela przechowująca ostrzeżenia administracyjne (o tym później)

Wprawdzie nie ma tego w tabeli z wymaganiami, ale analiza UX wskazała, że przyda się kilka dodatkowych funkcji

• możliwość zmiany kolejności pytań w ankiecie,
• czas udzielenia odpowiedzi,
• czasy aktualizacji innych encji,
• soft delete,
• i kilka innych.

Zostały one uwzględnione i ostateczny diagram tabel wygląda następująco:

Rysunek 1. Diagram tabel

Definicja w EF

Następnym krokiem jest odwzorowanie projektku w EF Core. Dla małych kontekstów można umieścić definicje encji w nadpisanej metodzie OnModelCreating(). Pokazałem to w innym moim artykule na tym blogu.
Tu mamy więcej encji i przejrzyściej będzie umieścić definicje w oddzielnych plikach dla każdej encji.

DbContext

W pliku określamy potrzebne DbSety tak, jak zawsze i dodajemy:

protected override void OnModelCreating(ModelBuilder modelBuilder) =>
    modelBuilder.ApplyConfigurationsFromAssembly(typeof(PollsDbContext).Assembly);

I w Infrastructure umieszczamy pliki:

internal sealed class AnswerConfiguration : IEntityTypeConfiguration<Answer>
{
    public void Configure(EntityTypeBuilder<Answer> builder)
    {
        builder.ToTable("answers", t => t.HasCheckConstraint("ck_answers_answer_not_empty", "LEN([answer]) > 0"));

        builder.HasKey(a => a.Id).HasName("pk_answers");
        builder.Property(a => a.Id).HasColumnName("id").UseIdentityColumn();
        builder.Property(a => a.QuestionId).HasColumnName("id_question");
        builder.Property(a => a.UserId).HasColumnName("id_user");
        builder.Property(a => a.Text).HasColumnName("answer").HasMaxLength(PollLimits.AnswerMaxLength).IsRequired();
        builder.Property(a => a.ModifiedAt).HasColumnName("modified_at").HasDefaultValueSql(DbDefaults.UtcNow);

        builder.HasOne(a => a.Question).WithMany(q => q.Answers)
            .HasForeignKey(a => a.QuestionId).HasConstraintName("fk_answers_questions")
            .OnDelete(DeleteBehavior.NoAction);
        builder.HasOne(a => a.User).WithMany()
            .HasForeignKey(a => a.UserId).HasConstraintName("fk_answers_users")
            .OnDelete(DeleteBehavior.NoAction);

        builder.HasIndex(a => new { a.QuestionId, a.UserId }).IsUnique().HasDatabaseName("ux_answers_question_user");
        builder.HasIndex(a => a.UserId).HasDatabaseName("ix_answers_user");

        builder.HasQueryFilter(a => a.Question.DeletedAt == null && a.Question.Poll.DeletedAt == null);
    }
}

A dotnet przy starcie aplikacji zrobi za nas robotę. Odnajdzie klasy implementujące IEntityTypeConfiguration i utworzy modele encji.

Utworzenie bazy na serwerze

API nie powinno być zaangażowane w proces wdrożenia samej bazy danych. Dokładanie tam akcji typu MigrateUp() jest antywzorcem. Najzdrowiej jest po prostu wykonać polecenie:

CREATE DATABASE polls;

Można to obudować skryptem sprawdzającym, czy baza istnieje.

Migracje

Do wykonania migracji w CI/CD mamy dwie podstawowe opcje:

1. CLI i dotnet ef migrations add <name> oraz dotnet ef database update <name>
2. Podobnie jak powyżej dodajemy migrację, ale tworzymy skrypt sql komendą dotnet ef migrations script --idempotent

Ta druga opcja jest przeze mnie zalecana. Wygenerowany skrypt zawiera wszystkie migracje od “czystej” bazy do ostatniej migracji i jest idempotentny. Razem z narzędziem sqlcmd tworzą świetny tandem do oskryptowania migracji w CI/CD.

Pierwsza opcja wymaga po prostu zbudowania aplikacji w kontenerze i uruchomienia polecenia dotnet ef database update <name>. Tylko po co tworzyć kontener z sdk tylko w tym celu?

Aplikacja demo

Cała ta robota nie może zostać w szufladzie. Do zilustrowania, jak zaprojektowana baza działa w rzeczywistości wykonałem .NET API, czyli backend. Jak się powiedziało B jak Backend, to trzeba też powiedzieć A jak Angular 😉 Front-end utylizujący API wykonany jest w Angular. Zapraszam do zapoznania się z implementacją: Ankieta demo - kliknij

Nie, baza się nie wyłoży. Są limity 😉