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.
- 1 Navngivning af databasetabeller og kolonner
- 2 Fact- og historik-tabeller i DFDG Classic (Revisionslog)
- 3 Metadata-felter der bør være i alle tabeller
- 4 ID'er (nøgler og fremmednøgler)
- 5 Tabeller der indeholder persondata (CPR-nummer eller PersonId)
- 6 Index'es
- 7 Dokumentation
- 8 Teknologi og udviklernoter
- 8.1 Versionsstyring
- 8.2 Stored procedures
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 |
|---|---|---|---|---|
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 |
|---|---|---|---|---|
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:
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 |
|---|---|---|---|---|
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 | Base: Byte | 1 | Kodeliste med brugertyperne:
| |
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:
|
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 https://starwiki.atlassian.net/wiki/spaces/FYS/pages/13926405/Den+gode+database#Script-til-udtr%C3%A6k-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 undertil 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.
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