Skapa databas

Rentals.VehicleID är inte unik utan är rimligtvis en FK som pekar på Vehicles.ID, du kan ju hyra ut samma fordon flera gånger eller hur ? Då kan inte Rentals.VehicleID vara unik.

Vehicles.FuelType och FuelPrices.FuelType borde ju varit en FK också till en FuelType tabell men det funkar som det är om du är noga med att ha samma värde där, fast inte idealt.

@Tryckvagen:

Jag tror inte att du ska se det som att du får ett färdigt svar. Du behöver så klart bygga ut med era specifika grejer.

Det kan man ha men det är enklare att samla alla kostnader som en bunt räkningar som jag gjort i tabellen "expense" (kostnad). Det stämmer dessutom bra för bokföring om man lägger till en kolumn för vilket konto som skall belastas och datum då man betalade m.m.

Man får fundera lite. Jag har inte tagit med inköpskostnaden, som ju ska spridas över tiden som bilen är i bruk. Kan inte riktigt avskrivningsregler för biluthyrningsföretag men troligtvis ska kostnaden för inköp spridas över ett antal år på samma sätt som allt annat man använder för professionen. Men jag tror att alla kostnader ska höra ihop med den fysiska bilen och inte med modellen.

Slutgiltiga priset beror på vilken bil (eller kategori enligt serafims modell), antal dagar och km som körts.

För en specifik car eller Vehicle..

Alltså beräknat av
day_price decimal(6,2), -- dygnspris, max 9999.99
km_price decimal(5,2), -- km-pris, max 999,99
discount decimal(4,2) -- långtidsrabatt, max 99.99

och

startdate timestamp without time zone not null, -- startdatum (och tid)
enddate timestamp without time zone not null, -- slutdatum (och tid)
meter_at_start integer not null, -- startmätarställning
meter_at_end integer, -- slutmätarställning

edit2: Missade att 'expense' är företagets kostnader
alternativt (nja)

kolla i expense tabellen (och om något skapade upp en expense vid varje slutförd uthyrning) och vi har en referens till en uthyrning.

eller enligt mitt alt. 2

DateOfDeparture
DistanceUponDeparture
DateOfReturn
DistanceUponReturn

alternativt

om detta kalkyleras vid återlämning av bilen.
DaysRented (calculated?)
TravelDistanceInMeter (calculated?)

och

RentalFeePerDay
RentalFeePerKm

*edit: stavning
*edit2: duuhhh

Beräknas utifrån priset i bilens kategori och tid + km. Kanske kategorin borde haft ett timpris också...

@Tryckvagen:

Först av allt:
Hoppa över index. Alla större databashanterare ordnar automatiskt index i sådan utsträckning att det täcker grundläggande behov. Problemställningen ger inte upphov till någon krånglig lösning och inte heller till någon "stor" databas. Alla sökningar och andra operationer på databasen (insättning och uppdatering) kommer gå fort utan extra index.

De flesta av frågorna som ställts i de svar du fått besvaras mer eller mindre automatiskt om man benar ut problematiken lite grand medan man bygger modellen. Problemet biluthyrning är, vad gäller databasmodell inte svårt, utan tvärtom faktiskt ganska trivialt.

Verktyget jag kommer använda är lite enklare än de flesta verktyg som numera används men jag har hållit på länge och upplever att resultatet lätt kan diskuteras med folk som inte är hemma på området och att nybörjare lätt kan lära sig att använda det och sedan gå vidare till mer komplexa verktyg som duger bättre för riktigt stora jobb.

Man börjar med att ta fram en modell och det mest grundläggande är en ER-modell (Entity-Relationship-modell = objekt- och sambandsmodell) där jag kommer rita rektanglar för objektklasser (= mängder av objekt / tabeller med objektbeskrivningar) och linjer för sambandsklasser (= mängder av samband mellan objekt / antingen tabeller med sambandsbeskrivningar eller främmande nycklar i objektklasser). Med främmande nyckel menas en referens från en tabell till en annan och regeln säger att om det finns en främmande nyckel ska den antingen inte ha något värde (= null) eller måste den ha ett värde som motsvarar ett objekt i den tabell den refererar till.
Allt det här lossnar nog medan vi går igenom problembeskrivningen och samtidigt bygger modellen. Jag kommer bygga en traditionell och mycket enkel ER-modell och man ska vara medveten om att det finns en mängd olika notationer och tekniker.

Det är viktigt att förstå att vi modellerar möjligheter, d.v.s. vad vi vill kunna representera och att det är i databasen som vi sedan realiserar en representation. Med andra ord kan det t.ex. finnas en bil av en viss modell om vi tar med det i modellen men det är inte säkert att vi utnyttjar den möjligheten i databasen (= kanske finns det ingen rad i biltabellen som motsvarar Volvo C70).

OBS också att alla delar av modellen ska vara namngivna. Så som den här modellen kommer se ut kommer alla sambandklasser försvinna vid övergången till databasstruktur och därför namnger jag sambandsklasserna med bara sammansättningen av första bokstäverna i de objektklasser de sammanbinder. Att namnge sambandsklasser är svårt och jag är lite lat, normalt skulle man ge dem beskrivande namn.

Nu kör vi

1.
Centralt i modellen blir bilar och kunder så jag ritar in dessa som objektklasser (med namnet i singularis)

http://semidako.se/biluthyrning/modell1.png

2.
Varje bil är av en viss modell medan det för varje modell kan finnas mer än en faktisk bil, vilket man ser genom gaffeln i ena änden av sambandsklassen jag kallat "BiMo" (ett beskrivande namn kunde varit "BilModell", men jag kommer vara lat och det kommer bli svårare med namngivningen):

http://semidako.se/biluthyrning/modell2.png

3.
Katgorisera för att kunden ska kunna välja bil lättare: t.ex. antal passagerare, kombi, lyx, m.m. Samma resonemang gäller för gaffeln i änden mot Modell och ingen gaffel i änden mot Kategori

http://semidako.se/biluthyrning/modell3.png

4.
Vi lägger till Bokning där vi vill låta kunden kunna boka en bil av en viss kategori med möjligheten för kunden att välja en viss bilmodell.
Vi lägger också till Uthyrning som ju gäller en viss bil av de som är möjliga enligt bokningen och eftersom uthyrning är en viktig del av verksamheten och dess bokföring så måste varje uthyrning kunna identifieras unikt. Dessutom kopplar vi uthyrningen till den bokning som låg till grund för uthyrningen.

http://semidako.se/biluthyrning/modell4x.png

5.
Vill vi kunna representera kostnader. Det enklaste sättet är att koppla kostnader i form av fakturor och utlägg till varje enskilt fordon. Samtidigt kan vi representera telefonnummer till kunder på liknande sätt:

http://semidako.se/biluthyrning/modell5.png

6.
I de flesta fall skriver man in alla egenskaper (personnummer, namn, adress, m.m.) direkt i modellen men jag tycker att modellen då snabbt blir plottrig och svårläst så de senaste 30 åren har jag alltid istället gjort en tabell som beskriver delarnas egenskaper, en s.k. egenskapsmatris. Så gör jag nu också. Jag kommer inte sätta ut datatyper förrän det är dags att förvandla modellen och egenskapsmatrisen till en databasstruktur och sedan vidare till en uppsättning kommandon i SQL för att realisera databasen.
Jag skriver dessutom ut vilka egenskaper som kommer att vara primärnycklar.
Här kan man lägga till att det kan finnas fler än en möjlighet till primärnycklar. Alla dessa kallas kandidatnycklar och av de befintliga möjligheterna väljer man en till primärnyckel. De övriga kandidatnycklarna är viktiga men eftersom det i den här databasen inte kommer finnas mer än en kandidatnyckel per tabell så nöjer jag mig med att påpeka detta. I ett senare skede kommer jag att göra en del ändringar och då kommer jag att använda det som nu kallas primärnyckel till att säkra upp databasen vad gäller dubbletter och referenser till andra tabeller. OBS att jag endast ger exempel på egenskaper. Missar säkert en del men det kan man ju komplettera efter behov.

Egenskapsmatrisen:

Numera brukar man välja bort att använda informationsbärande egenskaper för att kunna få referensintegritet genom att istället använda stora heltal som en slags serienummer. Jag kommer att använda "id" som namn (inom parentes om aktuell beteckning för primärnyckeln kan användas och om inte kommer jag att sätta den gamla primärnyckeln som "unik". "unik" kommer då användas för att se till att samma värde inte används mer än en gång. Då blir det istället:

Miss av mig så, för att kunna hantera internationella kunder ändrar jag Telefon så att landsnumret kommer med:

Sambandsklasserna kommer inte ha några egna egenskaper i en så enkel modell men alla kommer få en primärnyckel som består av sammanslagningen av primärnycklarna i de objektklasser som de binder samman, här använder jag "tabellnamn_id" för id som hör till tabellen "tabellnamn".

Nu överför vi modellen och egenskapsmatrisen till en databasstruktur. Som tur är finns det en del ganska enkla regler. Här använder jag bara de som behövs.

Jag använder mig av beteckningen:
tabellnamn: (P(primärnyckel), övriga egenskaper) Om unik(x) används U(x)

Regel 1: Alla Objektklasser som innehåller mer än bara primärnyckel ska bilda en tabell.

Bil: (P(id),U(regnr), färg, mätarställning)
Modell: (P(id), U(modellbeteckning), märke, dörrantal, bränsletyp, bränsleåtgång, bränslemått)
Kategori: (P(id), U(kategoribeteckning), passagerantal, kombi, maxlast, dygnspris, km-pris, långtidsrabatt)
Uthyrning: (P(uthyrningsnummer), startdatum, slutdatum, startmätarställning, slutmätarställning, kostnad)
Kund: (P(id), U(personnummer), förnamn, efternamn, adress, postnummer, postort)
Telefon: (P(id), U(telnr, landskod))
Bokning: (P(bokningsnummer), startdatum, slutdatum)
Kostnad: (P(verifikationsnummer), datum, typ, summa, kommentar)

Regel 2: Sambandsklasser av typen 1:N (de med gaffel i en ände) försvinner men den delen av primärnyckeln som är mot 1-änden (utan gaffel) blir en egenskap i objektklassen på N-sidan (gaffel-änden) och faktiskt en främmande nyckel där.

KoBi försvinner och Bil_id blir främmande nyckel i Kostnad:

Kostnad: (P(verifikationsnummer), datum, typ, summa, kommentar, FK(bil_id))
o.s.v.:

Bil: (P(id),U(regnr), färg, mätarställning, FK(modell_id))
Modell: (P(id), U(modellbeteckning), märke, dörrantal, bränsletyp, bränsleåtgång, bränslemått, FK(kategori_id))
Kategori: (P(id), U(kategoribeteckning), passagerantal, kombi, maxlast, dygnspris, km-pris, långtidsrabatt)
Uthyrning: (P(uthyrningsnummer), startdatum, slutdatum, startmätarställning, slutmätarställning, kostnad, FK(bil_id), FK(kund_id))
Kund: (P(id), U(personnummer), förnamn, efternamn, adress, postnummer, postort)
Telefon: (P(id), U(telnr, landskod), FK(kund_id))
Bokning: (P(bokningsnummer), startdatum, slutdatum, FK(modell_id), FK(kategori_id), FK(kund_id))
Kostnad: (P(verifikationsnummer), datum, typ, summa, kommentar, FK(bil_id))

En sak kvar: UtBo som inte har någon gaffel och därför klassas som 1:1-sambandsklass, d.v.s. en bokning motsvaras av en och endast en uthyrning. Här får man resonera sig fram lite grand.
Eftersom bokningar kommer till utan att det finns en uthyrning men uthyrningar (nästan) alltid motsvaras av en föregående bokning (utom när en kund kommer in på uthyrningskontoret och frågar efter en bil som inte ska ut till kund och det råkar finnas en tillgänglig) så föredrar jag att skapa referensen till bokningen i Uthyrning. Så jag lägger till en extra främmande nyckel i Uthyrning:

Bil: (P(id),U(regnr), färg, mätarställning, FK(modell_id))
Modell: (P(id), U(modellbeteckning), märke, dörrantal, bränsletyp, bränsleåtgång, bränslemått, FK(kategori_id))
Kategori: (P(id), U(kategoribeteckning), passagerantal, kombi, maxlast, dygnspris, km-pris, långtidsrabatt)
Uthyrning: (P(uthyrningsnummer), startdatum, slutdatum, startmätarställning, slutmätarställning, kostnad, FK(bil_id), FK(kund_id), FK(bokning_id))
Kund: (P(id), U(personnummer), förnamn, efternamn, adress, postnummer, postort)
Telefon: (P(id), U(telnr, landskod), FK(kund_id))
Bokning: (P(bokningsnummer), startdatum, slutdatum, FK(modell_id), FK(kategori_id), FK(kund_id))
Kostnad: (P(verifikationsnummer), datum, typ, summa, kommentar, FK(bil_id))

Nu är vi klara att skapa SQL-satserna för att bygga databasen. Här definierar jag databasen efter hur det skulle bli i PostgreSQL. Det skiljer sig lite från MySQL och andra. Man får läsa på lite om man använder en annan databashanterare. Vi kan be om kommentarer angående hur det skiljer sig från MySQL... Eftersom många databashanterare inte är förtjusta i svenska tecken blir det engelska i definitionerna men t.ex. PostgreSQL hanterar diakritiska tecken utan problem, d.v.s. tecken som har, sett från engelska, underliga accenter som t.ex. trema ovanför a och o (= ä och ö). Jag sätter kommentarer direkt i koden, allt som står efter '--' på en rad i SQL blir en kommentar. Jag tar tabellerna i en ordning som gör att jag inte gör våld på främmande nycklar.

create table category ( -- kategori
  id bigserial not null primary key,
  category character varying(50) not null unique, -- kategoribeteckning
  passenger_no integer not null, -- passagerantal
  combi boolean, -- kombi (sant = 't', falskt = 'f') men man kan använda 'true' och 'false'
  load_capacity integer, -- maxlast
  day_price decimal(6,2), -- dygnspris, max 9999.99
  km_price decimal(5,2), -- km-pris, max 999,99
  discount decimal(4,2) -- långtidsrabatt, max 99.99
);

create table model ( -- modell
  id bigserial not null primary key,
  model character varying(50) not null unique, -- modellbeteckning
  make character varying(50) not null, -- märke
  door_no integer not null, -- dörrantal
  fuel_type character varying(50) not null, -- bränsletyp
  fuel_consumption integer not null, -- bränsleåtgång
  fuel_measure character varying(10) not null, -- bränslemått
  category_id bigint not null references category(id) -- främmande nyckel till kategoritabellen
);

create table car ( -- bil
  id bigserial not null primary key,
  regnr character varying(8) not null unique, -- regnr
  color character varying(20), -- färg
  meter integer not null default 0, -- mätarställning
  model_id bigint not null references model(id) -- främmande nyckel till modelltabellen
);

create table expense ( -- kostnad
  voucher_id bigserial not null primary key, -- verifikationsnummer
  type character varying(50), -- typ av utgift
  sum decimal(10,2) not null, -- summa med högst tio siffror varav två är decimaler (= max 99999999.99)
  comment text, -- kommentar
  car_id bigint references car(id) -- främmande nyckel till biltabellen
);

create table client ( -- kund
  id bigserial not null primary key,
  pnr character varying(13) not null unique, -- personnummer
  first_name character varying(50) not null, -- förnamn
  family_name character varying(50) not null, -- efternamn
  address character varying(50) not null, -- adress
  postcode integer not null, -- postnummer
  district character varying(50) not null -- postort, vanligt i england men vanligt med 'city' i USA
);

create table phone ( -- telefon
  id bigserial not null primary key,
  phone_no bigint not null, -- telnr
  country_code character varying(10) not null default '+46', --landskod default till Sverige
  client_id bigint not null references client(id), -- främmande nyckel till kundtabellen
  unique(phone_no, country_code) -- om flera egenskaper tillsammans ska vara unik kombination gör man så
);

create table booking ( -- bokning
  booking_no bigserial not null primary key, -- bokningsnummer
  startdate timestamp without time zone not null, -- startdatum (och tid)
  enddate timestamp without time zone not null, -- slutdatum (och tid)
  model_id bigint references model(id), -- främmande nyckel till modelltabellen
  category_id bigint not null references category(id), -- främmande nyckel till kategoritabellen
  client_id bigint not null references client(id) -- främmande nyckel till kundtabellen
);

-- märk att modellreferensen inte är tvingande medan kategori- och kundreferenserna är det

create table rental ( -- uthyrning
  rental_no serial not null primary key, -- uthyrningsnummer
  startdate timestamp without time zone not null, -- startdatum (och tid)
  enddate timestamp without time zone not null, -- slutdatum (och tid)
  meter_at_start integer not null, -- startmätarställning
  meter_at_end integer, -- slutmätarställning
  cost decimal(7,2), -- kostnad, beräknas vid återlämn. av bil
  car_id bigint not null references car(id), -- främmande nyckel till biltabellen
  client_id bigint not null references client(id), -- främmande nyckel till kundtabellen
  booking_id bigint references booking(booking_no) -- främmande nyckel till bokningdtabellen
);

Slutligen: Kan man få svar på alla frågor??

Ja, det kan man. Utmaning till alla: formulera utgående ifrån de här givna tabelldefinitionerna alla de frågeställningar som finns i ursprungsinlägget.
Förutsättningar: kort sträcka är mindre än 10 mil.

Jag återkommer med SQL queries själv (måste jobba lite också )

Ställ gärna frågor på innehållet, Jag svarar så gott jag kan.

Du får fylla med samma värden när du lägger in datan. Det är ju inte så viktigt just nu vad som skulle räknas ut eller plockas in automatiskt.

Vill man ha sådana kolumner som räknar automatiskt eller uppdateras automatiskt är det antingen en computed column eller en trigger man använder sig av men det är att komplicera det hela och det har nog komplicerats tillräckligt i denna tråd.

Ett tips är ju att köra allt på https://sqlfiddle.com eller var det ett krav att du skulle exportera ut det i något format ? Hur ska det hela presenteras ?

Vilket schema kör du nu förresten ? zoomsters eller serafins ? Eller en egen ?

Samma ordning som jag definierade tabellerna så kommer du inte få krångel med främmande nycklar.

Grundtanke: Bilar måste finnas innan man kan hyra ut något och kunder innan någon kan boka dem eller hyra dem.

Grundregel: börja alltid med tabeller utan främmande nycklar = Category och Client