Skapa en databas för recept i SQL

Jag försöker alltid effektivisera mitt arbete, så att jag får så mycket som möjligt gjort på kortast möjliga tid. Det är enormt viktigt för mig. Därför brukar jag kombinera arbeten, så att det ena får det andra gjort. Även om jag tycker om att koppla av framför en bra film en stund, känns det ibland frustrerande att slösa för mycket tid på det. Då passar jag på att göra andra saker samtidigt, som att vika tvätt. När jag lär mig programmering letar jag efter liknande lösningar.

Eftersom jag har ett stort intresse av att skapa nyttiga och goda recept, har jag valt att min första databas i SQL ska bli min receptsamling. Antagligen hade det varit betydligt enklare att skapa en databas med författare och deras romaner, men den har jag ingen nytta av senare. Fördelen med att ge sig i kast med ett verkligt problem i praktiken, är att man lättare förstår vilka metoder som fungerar eller inte.

Det som är problematiskt, är inte att skapa databasen med tillhörande tabeller och fält i språket SQL. Det svåra är att komma fram till vilka tabeller som ska finnas, vilka fält var och en ska ha och hur de ska relatera till varandra.

Jag börjar med att skapa fyra tabeller där första fältet i alla är en primärnyckel, för att vara säker på att kunna skilja på de olika raderna. Först vill jag ha tabellen ”Recept” där jag lägger in namnet på rätten, t ex ”Suzannes Cheesecake”. Nästa tabell som ska finnas är ”Receptgrupp” där jag har namnet på typ av rätt, t ex ”Dessert”. Primärnyckeln i den tabellen ska relatera till en främmande nyckel i tabellen ”Recept”. Sedan vill jag ha tabellen ”Livsmedel” där jag samlar alla råvaror som jag kommer att ha med i mina recept, t ex ”Grädde” eller ”Philadelphiaost”. I tabellen ”LivsmedelGrupp” finns typen av livsmedel, t ex ”Mjölkprodukter”. Primärnyckeln i den tabellen ska relatera till en främmande nyckel i tabellen ”Livsmedel”.

Databasdiagram
Från början tänkte jag också att tabellen ”Recept” skulle relatera till tabellen ”Livsmedel” med ”LivsmedelId” som främmande nyckel, men det var ingen bra idé.

Med dessa fyra tabeller har jag den uppsättning av tabeller och den information som jag vet att jag behöver. Om vi tar ”Suzannes Cheesecake” som exempel, så har den sin plats i tabellen ”Recept” och den relaterar till tabellen Receptgrupp och där finns ”Dessert” som en receptgrupp. Sedan finns råvarorna ”Grädde” och ”Philadelphiaost” i tabellen  ”Livsmedel” och eftersom den tabellen också relaterar till tabellen ”Recept”, med den främmande nyckeln ”LivsmedelId”, kan jag hämta de råvaror jag behöver från tabellen ”Livsmedel” genom att först leta upp ”Grädde”, ta värdet på ”LivsmedelId” för den raden och lägga in det värdet i ”LivsmedelId” i tabellen ”Recept”.

Det är här det blir fel, eftersom jag måste hämta ”LivsmedelId” för både ”Grädde” och ”Philadelphiaost” och dessa två värden kan inte läggas in i fältet ”LivsmedelId” i tabellen ”Recept”. Det beror på att varje ruta bara kan lagra ett värde, s k enkelt värde. Därför tar jag bort den främmande nyckeln ”LivsmedelId” i tabellen ”Recept” och därmed bryter jag förbindelsen mellan tabellerna ”Recept” och ”Livsmedel”. Därför måste jag skapa ytterligare en tabell, som ska vara en slags mellanhand, mellan dessa. Jag tänkte något i stil med detta:

diagram
Länken mellan tabellerna ”Recept” och ”Livsmedel” är borttagen och har ersatts med ytterligare en tabell som jag kallar ”Ingredienser”. I den finns det två främmande nycklar, en som hämtar värdet från primärnyckeln i tabellen ”Recept” och en som hämtar värdet från primärnyckeln i tabellen ”Livsmedel”.

Nu har vi alltså dessa tabeller:

  1. Recept – Primärnyckeln ”ReceptId”. Namnet på rätten i ”ReceptNamn”. Främmande nyckeln ”ReceptGruppId” som hämtar värdet från primärnyckeln i tabellen ”ReceptGrupp” och därmed fås information om vilken typ av rätt det är.
  2. ReceptGrupp – Primärnyckeln ”ReceptGruppId” som bestämmer värdet i den främmande nyckeln i tabellen ”Recept”. Namnet på typen av rätt i ”ReceptGruppNamn”.
  3. Livsmedel – Primärnyckeln ”LivsmedelId”. Namnet på livsmedlet i ”LivsmedelID”. Främmande nyckeln ”LivsmedelGruppId” som hämtar värdet från primärnyckeln i tabellen ”LivsmedelGrupp” och därmed fås information om vilken livsmedelsgrupp det är.
  4. LivsmedelGrupp – Primärnyckeln ”LivsmedelGruppId” som bestämmer värdet i den främmande nyckeln i tabellen ”Livsmedel”. Namnet på gruppen av livsmedel i ”LivsmedelGruppNamn”.
  5. Ingredienser – Främmande nyckeln ”ReceptId” som hämtar värdet från primärnyckeln i tabellen ”Recept”. Främmande nyckeln ”LivsmedelId” som hämtar värdet från primärnyckeln i tabellen ”Livsmedel”. Mängden av ingrediensen i ”Vikt”.

Datatyper

Samtliga numeriska fält kommer enbart kunna lagra ett heltal, så de kommer således att vara av typen INT.  Antagligen skulle det räcka med att dessa fält är av datatypen SMALLINT, men jag håller mig till INT innan jag har fördjupat mig i datatyper. För textfälten står valet mellan VARCHAR och NVARCHAR. Eftersom jag inte har tid att fastna i ytterligare en detalj, utgår jag helt sonika från att NVARCHAR är det bästa alternativet tills jag har mer kött på benen. Det fungerar trots allt med vilken som.

Skapa databasen med tillhörande tabeller

Skapa databasen:

CREATE DATABASE Recept;

Sen talar jag om att jag ska använda databasen jag nyss skapade:

USE Recept;

I SQL Server Management Studio, kan jag i stället välja databas i rullgardinsmenyn ”Available databases”, men jag rekommenderar att man använder ovanstående metod. Då slipper man göra samma misstag som jag gjorde i början. Jag modifierade fel databas, i tron att rätt var vald.

Första tabellen: LivsmedelGrupp

Det är viktigt att skapa tabellerna i rätt ordning. Det går t ex inte att skapa en tabell med en främmande nyckel till en tabell som inte är skapad ännu. Tabellerna ”LivsmedelGrupp” och ”ReceptGrupp” saknar främmande nycklar, så det går att börja med vilken som helst av dessa två. När de är skapade fungerar det att skapa tabellerna ”Livsmedel” och ”Recept” som har en främmande nyckel som refererar till primärnyckeln i ”LivsmedelGrupp” respektive ”ReceptGrupp”.  Tabellen ”Ingredienser” måste skapas sist eftersom den har två främmande nycklar som refererar till tabellerna ”Recept” och ”Livsmedel”.

I tabellerna ”Livsmedel”, ”LivsmedelGrupp”, ”Recept” och ”ReceptGrupp” insåg jag att jag vill ha UNIQUE på fälten för namn, för att inte riskera att skriva in några onödiga dubbletter. Visserligen kan ett recept på en maträtt förekomma många gånger, med lite variationer. Då vill jag ha med det i namnet så att alla receptnamn ändå blir unika, t ex ”Morfars köttfärssås” och ”Suzannes köttfärssås”. Jag kommer också att använda IDENTIY(1,1), som sköter den unika uppräkningen av värden i fälten med primärnycklar, i dessa fyra tabeller.

CREATE TABLE LivsmedelGrupp(
    LivsmedelGruppId int NOT NULL IDENTITY(1,1),
    LivsmedelGruppNamn nvarchar(100) UNIQUE, 
    CONSTRAINT PK_LivsmedelGrupp PRIMARY KEY(LivsmedelGruppId)
);

I den här tabellen är det inga konstigheter. Bara fältet med primärnyckeln och fältet för namn på livsmedelsgrupper. Primärnyckeln kommer att relatera till  den främmande nyckeln i tabellen ”Livsmedel” nedan.

Andra tabellen: Livsmedel

Jag lägger till IDENTITY i primärnyckeln och UNIQUE i fältet ”LivsmedelNamn”. Annars är det precis som illustrationen ovan.

CREATE TABLE Livsmedel(
    LivsmedelId int NOT NULL IDENTITY(1,1),
    LivsmedelNamn nvarchar(100) UNIQUE, 
    LivsmedelGruppId int NOT NULL,
    CONSTRAINT PK_Livsmedel PRIMARY KEY(LivsmedelId),
    CONSTRAINT FK_Livsmedel_LivsmedelGrupp FOREIGN KEY(LivsmedelGruppId)
    REFERENCES LivsmedelGrupp(LivsmedelGruppId)
);

Egentligen är det inget märkvärdigt i den här tabellen heller. Den är ganska lik den förra tabellen, med ett fält för id och ett för namn för livsmedel. Det som är extra här, är den främmande nyckeln som relaterar till primärnyckeln i tabellen ”LivsmedelGrupp” där värdet inhämtas.

Tredje tabellen: ReceptGrupp

Även den här tabellen är som illustrationen ovan, bortsett från tillägget av IDENTITY och UNIQUE.

CREATE TABLE ReceptGrupp(
    ReceptGruppId int NOT NULL IDENTITY(1,1),
    ReceptGruppNamn nvarchar(100) UNIQUE, 
    CONSTRAINT PK_ReceptGrupp PRIMARY KEY(ReceptGruppId)
);

Här är ytterligare en enkel tabell med id och namn på receptgrupper, t ex dessert, varmrätt och så vidare. Primärnyckeln kommer att relatera till den främmande nyckeln i tabellen ”Recept”.

Fjärde tabellen: Recept

Förutom IDENTITY och UNIQUE, kommer jag också att komplettera med fältet ”Beskrivning”, annars blir det ju inget komplett recept. Då uppstår frågan vilken datatyp som jag ska använda. Jag har redan kommit fram till att jag ska använda datatypen NVARCHAR för textfält. När det gäller beskrivningen måste jag dock tänka på att den kommer innehålla betydligt fler tecken än namnet på t ex ett recept eller en ingrediens. Med datatypen NVARCHAR kan vi lagra 4000 tecken, så om beskrivningen är längre än det måste vi använda NVARCHAR(MAX).  De flesta av mina recept har en beskrivning med 1000 tecken, vilket innebär att NVARCHAR ganska troligt räcker för mina behov. Dessutom är det inga stora problem att ändra datatypen till en som kan lagra mer än innan. Därför väljer vi NVARCHAR här också och för säkerhets skull 4000 tecken.

CREATE TABLE Recept(
    ReceptId int NOT NULL IDENTITY(1,1),
    ReceptNamn nvarchar(100) UNIQUE,
    Beskrivning nvarchar(4000),
    ReceptGruppId int NOT NULL,
    CONSTRAINT PK_Recept PRIMARY KEY(ReceptId),
    CONSTRAINT FK_Recept_ReceptGrupp FOREIGN KEY(ReceptGruppId)
    REFERENCES ReceptGrupp(ReceptGruppId)
);

Även här finns id och namn på recepten. Sen den främmande nyckeln som relaterar till primärnyckeln i tabellen ”Receptgrupp”, där värdet inhämtas.

Femte tabellen: Ingredienser

Eftersom inga av fälten i tabellen ”Ingredienser” kommer att vara unika, kan de inte vara primärnyckel. Däremot är det troligt att båda fälten tillsammans kommer att vara unika. Man kan dock inte ha mer än en primärnyckel per tabell, utan måste skapa en primärnyckel för flera kolumner. Även om dessa två fält oftast kommer att vara unika, finns det en möjlighet att det blir en krock. Ett recept på våfflor kan t ex innehålla grädde både i våffelsmeten och som tillbehör. Men att skriva en ingrediens två gånger i ett recept verkar dumt. Då är det bättre att antingen skriva den totala mängden grädde i ingredienslistan och dela upp det i beskrivningen eller använda sig av underrubriker.

Även om jag inte har med det i illustrationen ovan, ska vi låta fälten ”ReceptId” och LivsmedelId” i tabellen ”Ingredienser” vara en primärnyckel. Dessutom ska vi lägga till fältet ”underrubriker”, där vi kan fylla i vilken underrubrik ingrediensen tillhör, t ex ”Sås” eller ”Tillbehör”. Eftersom vissa ingredienser som t ex ägg räknas i antal snarare än i vikt, ska vi också lägga till fältet ”Antal”. Det innebär att vi antingen fyller i vikt eller antal för en ingrediens.

Ibland skriver man varken antal eller vikt för en ingrediens och därför kan både ”Vikt” och ”Antal” ha ett NULL-värde.

Obs. Jag ska justera nedanstående senare, eftersom jag tycker att det lämpar sig att fältet ”Underrubriker” får standardvärdet ”Main” när det inte finns några underrubriker som ”Tillbehör” och ”Sås”. Visserligen går dessa fält att särskilja med operatorn IS NULL, men det känns mer korrekt att använda DEFAULT.

CREATE TABLE Ingredienser(
    ReceptId int NOT NULL,
    LivsmedelId int NOT NULL,
    Vikt int, 
    Antal int,
    Underrubriker nvarchar(100)
    CONSTRAINT PK_Ingredienser PRIMARY KEY (ReceptId, LivsmedelId),
    CONSTRAINT FK_Recept_Ingredienser FOREIGN KEY(ReceptId)
    REFERENCES Recept(ReceptId),
    CONSTRAINT FK_Livsmedel_Ingredienser FOREIGN KEY(LivsmedelId)
    REFERENCES Livsmedel(LivsmedelId)
);

Finns det någon nackdel att använda dessa två kolumner som en primärnyckel? Borde jag i stället ha skapat fältet ”IngrediensId” och deklarerat den kolumnen som primärnyckel? Som jag har förstått det ska man helst inte använda främmande nycklar som primärnycklar, men just i det här fallet känns det som att det är en bra lösning. Så jag följer magkänslan.

Fylla databasen med innehåll

Givetvis måste jag fylla tabellerna ”Livsmedel”, ”LivsmedelGrupp” och ”ReceptGrupp”, eftersom dessa behövs när jag ska lägga in mina recept. För att göra det använder jag mig av uttrycket INSERT INTO.

Fylla tabellen ReceptGrupp

INSERT INTO ReceptGrupp 
    (ReceptGruppNamn) 
VALUES
    ('Mellanmål'), ('Dessert'), ('Förrätt'), ('Varmrätt'), ('Sallad'), ('Godis och Snacks');

Att lägga in det här innehållet var inga svårigheter.

Fylla tabellen LivsmedelGrupp

INSERT INTO LivsmedelGrupp 
    (LivsmedelGruppNamn) 
VALUES
    ('Fisk och skaldjur'), ('Fläskkött'), ('Nötkött'), ('Fågel'), ('Ägg'), ('Mejeriprodukter'), 
    ('Spannmål och ris'), ('Frukt'), ('Smör och matolja'), ('Godis och snacks'), ('Svamp'), 
    ('Drycker och Alkohol'), ('Grönsaker'), ('Bönor och baljväxter'), ('Kryddor'), ('Potatis och rotfrukter');

Även det här innehållet var lätt att lägga in. Jag behövde inte ta hänsyn till relationen med andra tabeller varken för tabellen ”ReceptGrupp” eller ”LivsmedelGrupp”. Riktigt så enkelt är det inte att lägga in råvaror i tabellen ”Livsmedel”. Då måste jag nämligen leta upp rätt livsmedelsgrupp i tabellen ”LivsmedelGrupp” och hämta värdet i ”LivsmedelGruppId” på samma rad. Detta värde ska sedan läggas in i  den främmande nyckeln ”LivsmedelGruppId” i tabellen ”Livsmedel”. Då går det inte använda ovanstående metod. I stället måste jag använda uttrycket INSERT INTO SELECT. Det innebär att jag lägger in data i en tabell, samtidigt som jag kopierar data från en annan.

Fylla tabellen Livsmedel

INSERT INTO Livsmedel 
    (LivsmedelNamn, LivsmedelGruppId) 
SELECT
    'Makrillfilé', LivsmedelGruppId
FROM
    LivsmedelGrupp
WHERE
    LivsmedelGruppNamn = 'Fisk och Skaldjur';

Jag fyllde på med några livsmedel till inom olika livsmedelsgrupper, så att det finns något att arbeta med. Nu när jag har fyllt på med innehåll i båda tabellerna, kan jag hämta en lista med livsmedel och se vilken livsmedelsgrupp de tillhör.

SELECT 
    Livsmedel.LivsmedelNamn,
    LivsmedelGrupp.LivsmedelGruppNamn
FROM 
    Livsmedel
    INNER JOIN LivsmedelGrupp
    ON Livsmedel.LivsmedelGruppId = LivsmedelGrupp.LivsmedelGruppId;

 

Select Resultat

 

Till vänster listas alla livsmedel och till höger visas korrekt livsmedelsgrupp för dem. Resultatet blev precis som jag förväntade mig.

 

 

 

 

Fylla tabellen Recept

För att lägga in en maträtt i tabellen ”Recept” använder jag också uttrycket INSERT INTO SELECT. Jag letar upp rätt typ av rätt i tabellen ”ReceptGrupp” och hämtar värdet i ”ReceptGruppId” på samma rad. Detta värde ska sedan läggas in i den främmande nyckeln ”ReceptGruppId” i tabellen ”Recept”. Alltså, jag lägger in data i en tabell, samtidigt som jag kopierar data från en annan.

INSERT INTO Recept 
    (ReceptNamn, ReceptGruppId) 
SELECT
    'Suzannes Cheesecake', ReceptGruppId
FROM
    ReceptGrupp
WHERE
    ReceptGruppNamn = 'Dessert';

Nu kan vi lista maträtter tillsammans med vilken typ av rätt de tillhör.

select 
    recept.receptnamn,
    receptgrupp.receptgruppnamn 
from 
    recept 
    inner join receptgrupp
    on recept.receptgruppId = receptgrupp.receptgruppid;

Fylla tabellen Ingredienser

För att fylla på med ingredienser till varje recept, utgår jag från att jag ska lägga in 200 g Philadelphiaost som ingrediens till ”Suzannes Cheesecake” och även här använder jag uttrycket INSERT INTO SELECT. Jag ska alltså lägga in värden i fälten ”ReceptId”, ”LivsmedelId” och ”Vikt” (om jag hade lagt in ägg, hade jag i stället valt fältet ”Antal” )  i tabellen ”Ingredienser”. För att få fram vilka värden som ska in i dessa fält, måste vi välja ”ReceptId” i tabellen ”Recept”, ”LivsmedelID” i tabellen ”Livsmedel” och för fältet ”Vikt” skriver vi helt enkelt ”200”.  Eftersom receptet Cheesecake inte kommer att ha några underrubriker och eftersom Philadephiaost inte räknas i antal, läggs inget in i fälten ”Antal” eller ”Underrubriker” den här gången.

INSERT INTO Ingredienser
    (ReceptId, LivsmedelID, Vikt) -- Fält att lägga in värden i.
SELECT -- De tre värdena som ska kopieras till de tre fälten i tabellen "Ingredienser".
    Recept.ReceptId, 
    Livsmedel.LivsmedelId,
    '200'
FROM -- Tabellerna att hämta värdena från.
    Recept,
    Livsmedel 
-- Hämtar värdet för "ReceptId" i tabellen "recept" på samma rad där "Suzannes Cheesecake" = "ReceptNamn"
-- och värdet för "LivsmedelID" i tabellen "Livsmedel" på samma rad där 
WHERE "Philadelphiaost" = "LivsmedelNamn".
   Recept.ReceptNamn = 'Suzannes Cheesecake' AND Livsmedel.LivsmedelNamn = 'Philadelphiaost';

För att kontrollera att allt fungerar som det ska skriver vi det här:

SELECT
    Recept.ReceptNamn, 
    Livsmedel.LivsmedelNamn,
    Ingredienser.Vikt
FROM
    Recept
    INNER JOIN Ingredienser
    ON Recept.ReceptId = Ingredienser.ReceptId,
    Livsmedel 
WHERE
    Livsmedel.LivsmedelId = Ingredienser.LivsmedelId;

Eftersom rätterna listas med tillhörande ingredienser och vikt, fungerar det som det ska.

delresultat
Här listas de tre kolumnerna med recept, ingredienser och vikt.

 

Nu finns nog allt jag behöver i min receptdatabas. Det är endast underrubriker och tillhörande tabeller som saknas. men det får bli nästa gång.

Kommentera

E-postadressen publiceras inte. Obligatoriska fält är märkta *