Databasplanering – veterinärmottagningen David Djurvän

Story: Från Excelkaos till klinikplattform

David Djurvän är nyexaminerad djurdoktor och har valt att starta Djurväns Vård & Omtanke AB. På sin tidigare arbetsplats levde all information i manuellt namngivna Excel-filer som Besok_lista_final_v3_LAST.xlsx och Hundregister_nyaste.xlsx. Resultatet blev dubbelbokningar, förlorade journaler och noll spårbarhet.

David vill:

• slippa manuell kopiering mellan kalkylblad
• se alla djur som hör till en kund och vice versa
• logga diagnoser, behandlingar och involverad personal
• kunna svara på frågor i realtid (“Vilket var Fidos senaste besök?”, “Vilka besök innehöll diagnosen Parvovirus?”)

Kravbild & användarfall

Inventering av Excelröran

Så här såg mapparna ut när David tog över:

En typisk “rad” (3 flikar hopklistrade) såg ut så här:

Problem: adressen upprepas för varje besök, flera personalnamn i samma cell, diagnoser och symptom blandas.

Normalisera röran steg för steg

Steg 0 – Den platta Excel-tabellen

Vi börjar med en enda tabell Besok:

Problem:

• Flera ägare kan heta likadant (ingen unik identifierare).
• Djur repeterar ägaruppgifter.
• Kolumnen Personal innehåller flera värden.
• Symptom och diagnoser blandas och kan förekomma i olika stavningar.

Steg 1 – Första normalformen (1NF)

• Separera upprepade värden till egna rader.
• Bryt ut ägare och kontaktinfo från djur.
• Ge varje post ett unikt ID.

Förbättring: varje rad innehåller nu ett värde per kolumn, men data repeteras fortfarande.

Steg 2 – Andra normalformen (2NF)

• Identifiera funktionella beroenden: ägarens adress beror på ägaren, inte på besöket.
• Skapa separata tabeller för ägare (Agare) och djur (Djur).
• Inför primärnycklar (OwnerId, AnimalId) och främmande nycklar (Animal.OwnerId).

Besöken refererar nu endast till AnimalId och OwnerId.

Steg 3 – Tredje normalformen (3NF)

• Introducera referenstabeller för diagnostik och personal.
• Registrera många-till-många relationer via kopplingstabeller.
• Lägg till audit-kolumner.

Resultat: varje faktum lagras på ett ställe, relationer hanteras via nycklar.

Datamodellens huvudtabeller

Relationer i ord:

• En ägare kan ha flera djur → en-till-många (Owners → Animals).
• Ett djur gör många besök → en-till-många (Animals → Visits).
• Ett besök kan involvera flera anställda och en anställd kan jobba på flera besök → många-till-många med VisitStaff.
• Ett besök kan få flera diagnoser och samma diagnos kan gälla flera besök → många-till-många med VisitDiagnoses.

Fullt SQL-DDL för den normaliserade databasen

PRAGMA foreign_keys = ON;

CREATE TABLE Owners (
    OwnerId INTEGER PRIMARY KEY,
    FirstName TEXT NOT NULL,
    LastName TEXT NOT NULL,
    Phone TEXT,
    Email TEXT UNIQUE,
    PreferredContactMethod TEXT CHECK (PreferredContactMethod IN ('phone','email','sms')),
    Notes TEXT,
    CreatedAt TEXT NOT NULL DEFAULT (datetime('now')),
    UpdatedAt TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE OwnerAddresses (
    OwnerAddressId INTEGER PRIMARY KEY,
    OwnerId INTEGER NOT NULL,
    AddressLine1 TEXT NOT NULL,
    AddressLine2 TEXT,
    PostalCode TEXT NOT NULL,
    City TEXT NOT NULL,
    Country TEXT NOT NULL DEFAULT 'Sverige',
    IsPrimary INTEGER NOT NULL DEFAULT 1,
    ValidFrom TEXT NOT NULL DEFAULT (date('now')),
    ValidTo TEXT,
    FOREIGN KEY (OwnerId) REFERENCES Owners(OwnerId)
);

CREATE TABLE Species (
    SpeciesId INTEGER PRIMARY KEY,
    Name TEXT NOT NULL UNIQUE
);

CREATE TABLE Breeds (
    BreedId INTEGER PRIMARY KEY,
    SpeciesId INTEGER NOT NULL,
    Name TEXT NOT NULL,
    CONSTRAINT UQ_Breeds UNIQUE (SpeciesId, Name),
    FOREIGN KEY (SpeciesId) REFERENCES Species(SpeciesId)
);

CREATE TABLE Animals (
    AnimalId INTEGER PRIMARY KEY,
    OwnerId INTEGER NOT NULL,
    SpeciesId INTEGER NOT NULL,
    BreedId INTEGER,
    Name TEXT NOT NULL,
    Sex TEXT CHECK (Sex IN ('hane','hona','okand')) DEFAULT 'okand',
    BirthDate TEXT,
    ChipNumber TEXT UNIQUE,
    Color TEXT,
    IsNeutered INTEGER DEFAULT 0,
    Notes TEXT,
    CreatedAt TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (OwnerId) REFERENCES Owners(OwnerId),
    FOREIGN KEY (SpeciesId) REFERENCES Species(SpeciesId),
    FOREIGN KEY (BreedId) REFERENCES Breeds(BreedId)
);

CREATE TABLE Staff (
    StaffId INTEGER PRIMARY KEY,
    FirstName TEXT NOT NULL,
    LastName TEXT NOT NULL,
    Role TEXT NOT NULL,
    LicenseNumber TEXT,
    Phone TEXT,
    Email TEXT,
    Active INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE Visits (
    VisitId INTEGER PRIMARY KEY,
    AnimalId INTEGER NOT NULL,
    OwnerId INTEGER NOT NULL,
    VisitDate TEXT NOT NULL,
    VisitTime TEXT NOT NULL,
    Reason TEXT,
    VisitNotes TEXT,
    FollowUpDate TEXT,
    CreatedByStaffId INTEGER,
    CreatedAt TEXT NOT NULL DEFAULT (datetime('now')),
    UpdatedAt TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (AnimalId) REFERENCES Animals(AnimalId),
    FOREIGN KEY (OwnerId) REFERENCES Owners(OwnerId),
    FOREIGN KEY (CreatedByStaffId) REFERENCES Staff(StaffId)
);

CREATE TABLE VisitStaff (
    VisitStaffId INTEGER PRIMARY KEY,
    VisitId INTEGER NOT NULL,
    StaffId INTEGER NOT NULL,
    RoleAtVisit TEXT,
    PrimaryContact INTEGER NOT NULL DEFAULT 0,
    FOREIGN KEY (VisitId) REFERENCES Visits(VisitId),
    FOREIGN KEY (StaffId) REFERENCES Staff(StaffId),
    CONSTRAINT UQ_VisitStaff UNIQUE (VisitId, StaffId)
);

CREATE TABLE Symptoms (
    SymptomId INTEGER PRIMARY KEY,
    Code TEXT UNIQUE,
    Name TEXT NOT NULL,
    Description TEXT
);

CREATE TABLE VisitSymptoms (
    VisitId INTEGER NOT NULL,
    SymptomId INTEGER NOT NULL,
    Severity INTEGER CHECK (Severity BETWEEN 1 AND 5),
    Notes TEXT,
    PRIMARY KEY (VisitId, SymptomId),
    FOREIGN KEY (VisitId) REFERENCES Visits(VisitId),
    FOREIGN KEY (SymptomId) REFERENCES Symptoms(SymptomId)
);

CREATE TABLE Conditions (
    ConditionId INTEGER PRIMARY KEY,
    Code TEXT UNIQUE,
    Name TEXT NOT NULL,
    Description TEXT
);

CREATE TABLE VisitDiagnoses (
    VisitId INTEGER NOT NULL,
    ConditionId INTEGER NOT NULL,
    DiagnosisType TEXT CHECK (DiagnosisType IN ('preliminar','bekraftad')) DEFAULT 'bekraftad',
    Notes TEXT,
    PRIMARY KEY (VisitId, ConditionId),
    FOREIGN KEY (VisitId) REFERENCES Visits(VisitId),
    FOREIGN KEY (ConditionId) REFERENCES Conditions(ConditionId)
);

CREATE TABLE Treatments (
    TreatmentId INTEGER PRIMARY KEY,
    Code TEXT UNIQUE,
    Name TEXT NOT NULL,
    Description TEXT,
    DefaultDurationDays INTEGER
);

CREATE TABLE VisitTreatments (
    VisitId INTEGER NOT NULL,
    TreatmentId INTEGER NOT NULL,
    Dosage TEXT,
    Instructions TEXT,
    PRIMARY KEY (VisitId, TreatmentId),
    FOREIGN KEY (VisitId) REFERENCES Visits(VisitId),
    FOREIGN KEY (TreatmentId) REFERENCES Treatments(TreatmentId)
);

CREATE TABLE MedicalRecords (
    MedicalRecordId INTEGER PRIMARY KEY,
    AnimalId INTEGER NOT NULL,
    RecordDate TEXT NOT NULL,
    Summary TEXT NOT NULL,
    AuthorStaffId INTEGER,
    FOREIGN KEY (AnimalId) REFERENCES Animals(AnimalId),
    FOREIGN KEY (AuthorStaffId) REFERENCES Staff(StaffId)
);

CREATE VIEW LatestVisitPerAnimal AS
SELECT a.AnimalId,
       a.Name AS AnimalName,
       MAX(v.VisitDate) AS LatestVisitDate
FROM Animals a
JOIN Visits v ON v.AnimalId = a.AnimalId
GROUP BY a.AnimalId;

Pseudokod: Nytt besök från incheckning till journal

function registerVisit(ownerInput, animalInput, visitInput):
    ownerId = findOwner(ownerInput.personalIdentifier)
    if ownerId is null:
        ownerId = insertOwner(ownerInput)
        insertOwnerAddress(ownerId, ownerInput.address)

    animalId = findAnimal(ownerId, animalInput.chipNumber)
    if animalId is null:
        speciesId = findOrCreateSpecies(animalInput.species)
        breedId = findOrCreateBreed(speciesId, animalInput.breed)
        animalId = insertAnimal(ownerId, speciesId, breedId, animalInput)

    visitId = insertVisit(animalId, ownerId, visitInput)

    for staff in visitInput.staffMembers:
        linkVisitToStaff(visitId, staff.staffId, staff.role, staff.isPrimary)

    for symptom in visitInput.symptoms:
        symptomId = findOrCreateSymptom(symptom.code, symptom.name)
        addSymptomToVisit(visitId, symptomId, symptom.severity, symptom.notes)

    for diagnosis in visitInput.diagnoses:
        conditionId = findOrCreateCondition(diagnosis.code, diagnosis.name)
        addDiagnosisToVisit(visitId, conditionId, diagnosis.type, diagnosis.notes)

    for treatment in visitInput.treatments:
        treatmentId = findOrCreateTreatment(treatment.code, treatment.name)
        addTreatmentToVisit(visitId, treatmentId, treatment.dosage, treatment.instructions)

    return visitId

Sökexempel (SQL)

• Senaste besök för ett djur:

  SELECT v.VisitDate, v.VisitTime, v.Reason
  FROM Visits v
  WHERE v.AnimalId = :animalId
  ORDER BY v.VisitDate DESC, v.VisitTime DESC
  LIMIT 1;
  

• Alla besök för en ägare:

  SELECT v.VisitDate, a.Name AS Djur, v.Reason
  FROM Visits v
  JOIN Animals a ON a.AnimalId = v.AnimalId
  WHERE v.OwnerId = :ownerId
  ORDER BY v.VisitDate DESC;
  

• Besök med viss diagnos:

  SELECT v.VisitDate, o.FirstName || ' ' || o.LastName AS Agare,
         a.Name AS Djur, c.Name AS Diagnos
  FROM VisitDiagnoses vd
  JOIN Visits v ON v.VisitId = vd.VisitId
  JOIN Conditions c ON c.ConditionId = vd.ConditionId
  JOIN Animals a ON a.AnimalId = v.AnimalId
  JOIN Owners o ON o.OwnerId = v.OwnerId
  WHERE c.Name = 'Korsbandsskada';
  

• Besök där en specifik veterinär deltog:

  SELECT v.VisitDate, v.VisitTime, a.Name AS Djur, o.LastName AS Agare
  FROM VisitStaff vs
  JOIN Visits v ON v.VisitId = vs.VisitId
  JOIN Animals a ON a.AnimalId = v.AnimalId
  JOIN Owners o ON o.OwnerId = v.OwnerId
  WHERE vs.StaffId = :staffId;
  

Exempeldata: 20 besök i normaliserat format

Tabellen visar hur en normaliserad databas gör det trivialt att generera rapporter: samma ägare kan filtreras, senaste besök hittas, personal kan analyseras. I databasen skulle dessa rader ligga fördelade över Owners, Animals, Visits, VisitStaff, VisitDiagnoses och VisitTreatments.

Checklista vid implementation

• Sätt upp migrations (t.ex. 001_create_core_tables.sql).
• Lägg in referensdata för Species, Breeds, Symptoms, Conditions, Treatments.
• Lägg index på ofta sökta kolumner (VisitDate, OwnerId, ConditionId).
• Lägg triggers för att uppdatera UpdatedAt-kolumner.
• Skapa vyer för vanliga rapporter (senaste besök per djur, dagens schema, obesvarade uppföljningar).

Med denna modell lämnar David Excel bakom sig och får en spårbar, sökbar och framtidssäker databas som stödjer klinikens vardag.

Databasplanering — exempel (kort sammanfattning)

Fullständigt veterinärexempel finns i engelska dokumentet “database_planning_example.md”. Här följer en pedagogisk summering:

• Börja med den verkliga historien (Excel-kaos) för att förstå problem.
• Identifera entiteter (Owners, Animals, Visits, Staff, Conditions, Treatments).
• Normalisera: bryt ut oberoende listor till egna tabeller (addresses, phones).
• Designa relationer: 1:N och M:N hanteras via foreign keys och junction tables.
• Implementera constraints, index och vyer för vanliga rapporter.
• Planera migrations, seed-data och testskript.

Övningsuppgift: läs det fullständiga exemplet och rita ett ER-diagram. Implementera ett minimalt schema i SQLite och skriv fem queries som speglar vanliga frågor kliniken behöver.