Den gode database

DFDG er meget datatung. Modellering af data er derfor meget vigtig. Dette afsnit beskriver generelle retningslinjer for modellering af databasetabeller. Det primære fokus er tabeller hvor der er tæt på en 1:1 til entiteter i domænemodellen, dvs. fokus er ikke på styretabeller.

NB! Hvis du ikke arbejder med DFDG Classic bør du formodentlig se på STAR Foundation Metadata og STAR Foundation Historik siderne i stedet for denne.

Navngivning af databasetabeller og kolonner

Der anvendes engelske navne, med CamelCase. Tabeller bør placeres i Schema som følger opdelingen i Byggeblokke/domæner. Se også under History tabeller, hvor der tilføjes History til navnet.

Navngivningen af kolonner bør følge domænemodellens elementer og attributter (fra C# koden). Navnene oversættes til engelsk og CamelCase anvendes.

Fact- og historik-tabeller i DFDG Classic (Revisionslog)

(Ift. ny forretningsapplikationer / siloer er dette beskrevet i https://starwiki.atlassian.net/wiki/spaces/CITY/pages/1654718588 )

Generelt skal alle ændringer til data som indgår i sagsbehandling gemmes, så det er muligt at finde tilbage til tidligere versioner af data. Dette for alle tabeller der indeholder sagsrelaterede data. Systemtabeller og andre styringstabeller kan typisk undvære historiktabeller så længe registreringer logges. Se mere om historik og logning:

Det anbefales at der opereres med to typer tabeller:

Fact-tabeller,
History-tabeller, navngives som <Onlinetabelnavn> + History

Til alle Fact-tabeller oprettes en History tabel, hvori alle inserts, update og delete operationer registreres, samtidig med at den resulterende record ligger tilbage i Fact-tabellen samt i History-tabellen. Ved sletning fjernes recorden fra Fact-tabellen. Fact-tabellen indeholder på den måde alt der endte med at blive til noget.

Kolonner i Fact-tabellen

Tabellen indeholder følgende ekstra data udover de data som findes i online tabellen:

Kolonnenavn	Type	Detaljer	Forekomst	Beskrivelse

Kolonnenavn	Type	Forekomst	Beskrivelse
EntitetIdentifier	GUID (Primary key)	1	Entitet er entitetsnavnet
Alle kolonner fra entitet	EntitetType	1
Metadata-felter		1	Se liste af metadata-felter nedenfor

Kolonner i History-tabellen

Tabellen indeholder følgende ekstra data udover de data som findes i online tabellen:

Kolonnenavn	Type	Detaljer	Forekomst	Beskrivelse

Kolonnenavn	Type	Forekomst	Beskrivelse
EntitetHistoryIdentifier	GUID (Primary key)	1	Entitet er entitetsnavnet
Alle kolonner fra entitet	EntitetType	1
Metadata-felter		1	Se liste af metadata-felter nedenfor
Operation	tinyint	1	1=Created, 2=Update, 3=Delete, ved Delete sættes LastUpdatedByxxx felterne i den originale struktur.

Read operationer logges ikke i revisionsloggen, men logges i stedet i systemloggen i logfil, se:

Den gode logning (systemlog)

Metadata-felter der bør være i alle tabeller

(Ift. ny forretningsapplikationer / siloer er dette beskrevet i https://starwiki.atlassian.net/wiki/spaces/CITY/pages/1654718588 )

Kolonnenavn	Type	Detaljer	Forekomst	Beskrivelse

Kolonnenavn	Type	Detaljer	Forekomst	Beskrivelse
EntitetIdentifier	GUID (samme key som i online tabel)		1	Entitet er entitetsnavnet
ActiveOrganisationTypeIdentifier	OrganisationTypeIdentifierType Base: Byte		1	Den myndighed som der er registreret på vegne af (v/ AA: som brugeren har impersonated). Det er den ansvarlige myndighed
ActiveOrganisationCode	String	Length: 1-20	1	Koden som identificerer organisationen. Det kan være Jobcenternummer, et CVR nummer, en a-kassekode eller en kommunekode. Det er den ansvarlige myndighed
UserFullName	UserFullNameType Base: String	Length: 1-140	1	Brugers fulde navn, ved systemkald angives systemets og jobbets navn her.
RequestUserTypeIdentifier	RequestUserTypeIdentifierType Base: Byte		1	Kodeliste med brugertyperne: Citizen - Borger, f.eks. i selvbetjeningsløsninger CaseWorker - Sagsbehandler System - En systemproces, som f.eks. et batchjob, eller noget andet automatiseret der ikke kan henledes til en brugerhandling.
UserIdentifier	UserIdentifierType Base: String	Length: 1-255	1	Unik identifikation af brugeren, f.eks. en GUID, et medarbejder ID, system ID, bruger ID, certifikat ID, cpr-nummer, email (hvis den er unik) o.l. Afhængigt af RequestUserTypeIdentifier udfyldes feltet med: Borgers CPR nummer eller et andet unikt ID der identificerer borgeren Sagsbehandler ID, der kan spores tilbage i til brugeren, kan være en e-mail, et medarbejder id, eller lign System ID, unik identifikation af den proces eller batchjob der foretog handlingen.
UserEmail	EmailAddressIdentifierType String (E-mail)	Length: 2-256 Pattern: ([^>\(\)\[\]\\,;:@\s]{0,191}@[^>\(\)\[\]\\,;:@\s]{1,64})	0-1	Brugerens e-mail adresse
UserOrganisationTypeIdentifier	OrganisationTypeIdentifierType Base: Byte		1	Kodeliste som identificerer typen af organisationen som brugeren hører til. Dette er en kodeliste, dog som integer af historiske årsager.
UserOrganisationCode	String	Length: 1-20	1	Koden som identificerer organisationen som brugeren hører til. Det kan være et Jobcenternummer, CVR nummer, en a-kassekode eller en kommunekode.
CreatedDateTime				Erstattes af DFDGRegistrationDateTime
UpdatedDateTime				Erstattes af DFDGRegistrationDateTime
RegistrationDateTime				Udgår! (Eksternt registreringstidspunkt)
DFDGRegistrationDateTime	DateTime		1	Registreringstidspunkt i DFDG
CorrectionComment	String	Length: 0-1500	0-1

Felterne med bruger og organisation stammer fra sikkerhedsmodellen for DFDG, se STARs sikkerhedsmodel.

Ændringer pr primo 2019 med rødt og grønt

Ved konvertering fra CreatedDateTime+UpdatedDateTime til DFDGRegistrationDateTime bruges CreatedDateTime som datagrundlag for DFDGRegistrationDateTime for create metoder og UpdatedDateTime for update metoder.

ID'er (nøgler og fremmednøgler)

Generelt anvendes GUID'er som ID i tabeller. For tabeller, der er normaliseret ud i flere tabeller, hvor undertabeller blot anvendes som lister i hovedentiteten, kan det være en fordel at anvende integer ID'er istedet for GUID'er. Integer ID'er må dog ikke eksponeres via webservices.

Tabeller der indeholder persondata (CPR-nummer eller PersonId)

For tabeller der indeholder persondata, arbejdes mod at anvende PersonId på alle tabeller frem for CPR-nummer (PersonCivilRegistrationIdentifier).

PersonId er et for DFDG globalt heltal, der representerer borger og kan mappes til CPR via en mapningstabel. På sigt skal PersonId fastholdes på tværs af CPR-nummer skift, men på nuværende tidspunkt genereres et nyt PersonId for hvert unikt CPR-nummer, indtil PersonId er udbredt på flere tabeller.

På nye tabeller med persondata skal der anvendes PersonId istedet for CPR-nummer, ved ændring af eksisterende tabeller med CPR-nummer bør der omlæsse til PersonId.

Index'es

Lav et effektivt clustered index, f.eks. ved at inkludere CPR-nummer/PersonId i indexet.

Lav altid et clustered index. Tabeller uden clustered index kaldes Heaps.

De ligger spredt over disken, hvorend sqlserveren kan finde et ledigt hul og er ikke gemt i nogen som helst orden.
Det giver hurtige inserts, da SQL Server bare kan smide data hvor det skal være, men langsom select, update og delete

Kun i sjældne tilfælde er det hurtigere at have en heap end ikke. Det kunne f.eks. være hvis man har en staging tabel hvor data indsættes og bagefter trækkes alle rækker ud igen, men ellers ikke.

Som udgangspunkt skal alle foreign keys have indexes, så søgninger er mere effektive.

Det giver ikke altid mening med indexes på alle foreign keys. Hvis der er få rækker i den referede tabel, så kan indexes have en negativ overordnet effekt på databasens effektivitet.
Derfor skal eksempeltvis foreign keys til kodeliste tabeller ikke have indexes. Overvej derfor om et index er nødvendigt på en foreign key til en tabel med få rækker.
Benyt scriptet under Den gode database | Script til udtræk af foreign keys uden indexes og index kandidater til at finde foreign keys uden indexes.

Benyt SQLs indbyggede statistik til at finde index kandidater.

Brug scriptet underDen gode database | Script til udtræk af foreign keys uden indexes og index kandidatertil at finde index kandidater udfra SQLs indbyggede statistik.
Bid mærke i AvgUserImpact, AvgTotalUserCost og IndexAdvantage, og bedøm om et index på den foreslåede kolonne vil være til fordel.

Prime Directive: check genererede entiteter

Når man har oprettet database strukturen, og kørt Scaffold-DbContext, så:

check om entiteterne giver mening!!!

Mange til mange relationer

Når man laver mange til mange relationer, bør den tabel, som relationen gemmes i KUN indeholde foreign-key til de to tabeller, som har relationen. Og disse to foreign keys, skal udgøre relationtabellens primary key. Hvis relationstabellen har sin egen primary key, som ikke er de to foregn keys, så genererer scaffolderen en entitet til relationen og gør c# entiteterne mere komplekse, at arbejde med. Der er ingen grund til at der oprettes entiteter (i C#) til relationer, medmindre man specifikt har behov for det. EFCore kan sagtens finde ud af det, hvis relationen er sat korrekt op. Nedenstående eksempel, anviser hvordan man kan sikre at

Tabel Users:

CREATE TABLE [Users] (
  [Id] int NOT NULL IDENTITY,
  [Username] nvarchar(max) NOT NULL,
  [Description] nvarchar(max) NULL,
  CONSTRAINT [PK_Users] PRIMARY KEY ([Id]));

Tabel Roles:

CREATE TABLE [Roles] (
  [Id] int NOT NULL IDENTITY,
  [Rolename] nvarchar(max) NOT NULL,
  [Description] nvarchar(max) NULL,
  CONSTRAINT [PK_Roles] PRIMARY KEY ([Id]));

Tabel UserRole (mange til mange tabel):

CREATE TABLE [UserRole] (
    [UserId] int NOT NULL,
    [RoleId] int NOT NULL,
    CONSTRAINT [PK_UserRole] PRIMARY KEY ([UserId], [RoleId]),
    CONSTRAINT [FK_UserRole_Users_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [Roles] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_UserRole_Roles_UserId] FOREIGN KEY ([UserId]) REFERENCES [Users] ([Id]) ON DELETE CASCADE);

Dette resulterer i følgende 2 (ikke 3!) entiteter:

User:

Role:

Script til udtræk af foreign keys uden indexes og index kandidater

Følgende script trækker en liste på valgte database over foreign keys uden indexes og kandidater til index udfra SQLs indbyggede statistik over brugeradfærd og query costs.

Dokumentation

Alle tabeller og kolonner dokumenteres via Extended Properties, MS_Description.

Teknologi og udviklernoter

Versionsstyring

Modellen for databasen og de enkelte tabeller skal versionsstyres på lige fod med koden. Dette kan på Microsoft SQL og .NET platformen med fordel foregå ved et af følgende tekniske tiltag:

Scripting, hvor scripts versionsstyres
Entity Framework Migrations, hvor strukturen ligger i koden med mulighed for at opgradere

Stored procedures

Se: Den gode stored procedure

Fysisk arkitektur