Databasedesign

Spoergsmaalet handler om databasedesign.

Princippet i databasedesign maa vaere at designe databasen efter strukturen af de data man skal behandle, ikke efter hvilke dropdown lister og lignende man har brug for lige nu.  Naar man har sine data i den rigtige struktur kan man lave, i princippet, alle mulige forespoergsler og bruge disse til at fylde alle mulige drop-down lister og andre grafiske elementer.  Saa jeg foreslaar i denne omgang at fokusere paa hvilke 'ting,' entiteter, du skal holde rede paa og hvilke data elementer disse entiteter indeholder.  Som hovedregel skal du saa have en tabel for hver entitet.

Du synes at have entiteten 'Projekt' med projektnummer og uden tvivl navn og maaske beskrivelse og andre dataelelementer.

Saa synes du at have 'Levering' hvor noget leveres for et project fra et sted til et andet sted paa en dato med en chauffoer og en vogn.  Hvad er det for ting der leveres?  Har projekterne noget at goere med at fremstille/producere noget (for eksempel selvbyggersaet til garager og sommerhuse) og er leveringerne saa del-projekter?  Eller henter du ogsaa underdele hos grosister og leverer til dit lager som du saa samler i byggesaet og efterfoelgende lever hos kunder?

Og saa, som du siger, 'Kunde'.  Spoergsmaal:  Hoerer et projekt altid til en enkelt kunde?  Er et projekt, for eksempel, at levere et garageset type A3 til kunde nummer 123?  Saaledes at et projekt bliver en slags indkoebsordre?  Eller er der adskillige kunder til et project?  Saasom et project at saette op et sommerhusomraade med en kunde for hvert sommerhus?

Har du chauffoerer ansatte og ejer du en vognpark du skal holde rede paa?  Eller bruger du vognmandsfirmaer der saa soerger for vogn?

Hvis vi nu tager det simple tilfaelde hvor projekter altid hoerer til en enkelt kunde og du bruger vognmandsfirmaer og du leverer delprojekter fra din fabrik/lager til kundernes adresse saa snakker vi om saa nogenlunde disse tabeller:

Kunde
nummer, navn, adresse. 

Projekt
nummer, navn, beskrivelse, kundenummer.
Projektets kundenummer svarer til nummer for en kunde (der er 'fremmednoegle.')

Vognmandsfirma
nummer, navn

Levering
nummer, dato, projektnummer, beskrivelse, vognmandsnummer
projectnummer og vognmandsnumer svarer til et projects nummer og er vognmandsfirma nummer.

Saa naar man kender projektnummeret kender man kunden og dermed leveringsadressen.

Du kan saa traekke leveringer for projekt nummer 271 ud med denne forespoergsel:  SELECT Levering.beskrivelse FROM Levering JOIN Project ON Levering.projektnummer = Projekt.nummer WHERE Projekt.nummer = 271.

Eller samtlige leveringer mellem dato1 og dato2 til Kunde nummer 72, en kunde der maaske har adskillige projekter loebende:

SELECT Projekt.nummer, Levering.beskrivelse FROM Kunde JOIN Projekt ON Kunde.nummer = Projekt.kundenummer JOIN Levering ON Projekt.nummer = Levering.projektnummer WHERE Levering.dato BETWEEN dato1 AND dato2.

Muligvis er din forretning mere kompliceret saa denne struktur ikke er tilstraekkelig.  I saa fald fortael lidt om formaalet og din virksomhed og forklar hvad du er noed til at holde styr paa som falder udenfor denne struktur, saa kan vi bygge det ind.

Tusind tak for dit lange svar. Jeg beskriver forretningsgangen senere da jeg er på vej ud af døren. Den er nu ikke så kompliceret ;) Jeg vender tilbage med en beskrivelse. Men mange tak ind til videre.

Okay. Here it comes :) Det drejer sig om en skovbrug virksomhed. Processen er således:

Vi har en kunde, som vi udfører en opgave for som vi tildeler et projektnummer (altså opgaven får et projektnummer). En kunde kan godt have flere projekter.

Typisk drejer det sig om at vi fælder nogle træer som bliver flishugget (af os selv eller en underentreprenør) og flisen bliver kørt væk til varmeværker (leveringssteder)

Til kørslen med flis har vi vores egen lastbil, men bruger også andre vognmænd og vi har også flere chauffører.

Meningen er så, at man kan slå et projektnummer op og se:
Datoen for kørslen, Afhentningsadressen, leverinssted(varmeværk) chauffør, vognmand, Nettovægt, RM, Vandpct for hvert læs kørt.

Så skal det virke sådan at jeg kan se hvor meget man har leveret til et bestemt varmeværk eller hvor meget man har hentet på et bestemt projektnummer eller hvor meget man har kørt i en bestemt periode osv.. Men udregningerne kommer vi til, i første omgang ville det være fedt at få arrangeret elementerne :)

Håber det gav et bedre overblik over hvad jeg vil frem til.

Ok, det synes tydeligt nok.

Det centrale emne synes at vaere TRANSPORT. 

En transport finder sted paa en dato.  (jeg antager at hvis en transport finder sted over flere dage, for eksempel du exporterer flis til Langtbortistan eller koerselen forsinkes og foerst ankommer en dag senere, saa registreres startdatoen.)

En transport er for et project (jeg antager at det ikke forekommer at flere smaa projekter deler en lastvogn.) (fordi et project er for en enkelt kunde kender man kunden naar man kender projektet.)

En transport er fra en adresse.  (Jeg gaar ud fra at kundens postadresse ikke er vigtig og at der for et enkelt projekt kan vaere mere end en afhentningsadresse, for eksempel en kunde der skal have faeldet traer i sin haven og ogsaa paa grunden hvor kundens forretning ligger.)  (Du synes ikke at have brug for statistikker over hvor mange transporter der er fra hver enkelt adresse.)

En transport er til en modtager.  (Jeg gaar ud fra at hver modtager kun har en leveringsadresse.  Det er vel saa ligegyldigt om modtageren er et varmevaerk eller en papirfabrik eller et firma der saelger flis som ukrudtsafdaekning i haver.)

En transport har en nettovaegt, en RM (rummeter?) og en vandprocent.

Og saa har nogen udfoert transporten.  Du har en lastvogn, men med den nye database faar du maaske saa meget success at du maa anskaffer dig nogle flere.  Hvis du bruger en vognmand gaar jeg ud fra at du registrerer firmaet, men vognen og chauffoeren er i saa fald ligegyldigt.  Hvis du bruger egen vogn registrere du vognen og chauffoeren, men saadan noget som saerskilt statistik over chauffoererne, brug af vognen, eller brug af de enkelte vognmandsfiamer er ikke del af det nuvaerende projekt.

Og saa er det, af database-tekniske aarsager, nyttigt med et unikt ID nummer for hver transport.  Normalt bruger man auto-nummer saaledes at databasen selv nummererer transporterne.

En anden vigtig entitet er et PROJEKT.  De skal have et nummer (auto-nummer) og er for en kunde og har vel en kort beskrivelse.

Og KUNDE.  For dette projekt synes et autonummer og et navn at vaere tilstraekkelig.  Du kan saa efter behov fylde ud med fornavn, efternavn, firmanavn, telefonnummer, adresse hvis du ogsaa vil bruge databasen til fakturering og til at soege efter kontakt-telefonnumre.

Og MODTAGER.  De har et auto-nummer, et firmanavn, vej og nummer, postnummer hvis noedvendigt, by.  Du kan saa efter behov fylde paa med telefonnummer, kontaktperson, ..

Saa det bliver til de foelgende fire tabeller:

Kunde
id, navn

Projekt
id, kundenummer, beskrivelse
Projektets kundenummer svarer til (har fremmednoegle til) en id i kundetabellen.

Modtager
id, navn, gade-nummer, by

Transport
id, dato, projektnummer, fra-gade-nummer, fra-by, modtagernummer, vaegt, RM, vandprocent, transportoer (chauffoer eller vognmand), vogn (tom hvis vognmand)
projektnummer har fremmednoegle til id i Projekt, modtagernummer har fremmednoegle til id i Modtager

Med disse tabeller paa plads kan du faa svar paa disse (og mange andre) spoergsmaal:

For hvert projekt en liste over transporter med dato, afhentningsadresse, leveringssted, transportoer, vogn, vaegt, RM,  vandprocent:

SELECT t.dato, t.fra_gade_nummer, t.fra_by, m.navn, t.transportoer, t.vogn, t.vaegt, t.RM, t.vandprocent FROM Transport t JOIN Modtager m ON t.modtagernummer = m.id JOIN Projekt p ON t.projektnummer = p.id WHERE p.id = [det paagaeldende projektnummer]

Levering (total vaegt, total RM) til en bestemt modtager:

SELECT m.navn, SUM(t.vaegt) as vaegt, SUM(v.RM) as RM FROM Projekt p JOIN Modtager m ON p.modtagernummer = m.id WHERE m.navn = [det paagaeldende varmevaerk] GROUP BY m.id

Hentet (antal koersler, total vaegt, total RM) for et projekt:

SELECT id, COUNT(*) as antal, SUM(vaegt) as vaegt, SUM(RM) as RM FROM Projekt WHERE id = [projektnummeret] GROUP BY id

Hvor meget man har koert (antal koersler, total vaegt, total RN) mellem dato1 og dato2:

SELECT COUNT(*) as antal, SUM(vaegt) as vaegt, SUM(RM) as RM FROM Projekt WHERE dato BETWEEN [dato1] AND [dato2]

o.s.v.

Jeg har her foreslaaet den minimale tabelstruktur til det du beskrev.  Det kan du saa udbygge efter behov.  Hvis du forudser ogsaa at skulle lave statistikker over hvad hver vognmandsfirma maa du lave en vognmandstabel med i, navn, adresse o.s.v., og saa i Transport bruge et vognmandsnummer.  Eller hvis du har mange transporter fra de samme adresser kan du lave en tabel med adresser og saa i Transport bruge addressenumre.  o.s.v.

Tusind tak for det hurtige og udførlige svar ;) Jeg bliver desværre nødt til at være lidt besværlig nu.

Det ville faktisk være rart hvis jeg havde en tabel hvor jeg kunne have kundens navn, bopælsadresse, postnummer, by, tlf. hvis det kan flettes ind på en eller anden måde.

Så kunne jeg senere hen lave en statistik over under hvilket postnummer vi har flest opgaver eller lignende.

Og jo, et projekt har næsten altid flere læs (eller transporter) og kan også have flere afhentningssteder.

Så glemte jeg en anden ting. Til hvert læs leveret på et varmeværk modtager jeg en følgeseddel som har et nummer (kørselsnummer) og en kvittering fra varmeværket som også har et nummer. Disse to numre vil jeg også gerne skrevet ind, så jeg kan finde et læs efter et følgeseddelnummer eller kvit_nr.

Endnu mere tricky bliver det nu: Når vi kører et læs til et varmeværk, så kan vi godt hente et halvt læs hos et projektnummer og et halvt læs hos et andet projektnummer, dvs. at et kvit_nr kan godt høre sammen med flere projekter, men et projektnummer kan KUN have et kvit_nr.

Og under hver transport skriver jeg også ind hvor mange KM der er kørt fra afhentningsstedet til varmeværket.

Kan man flette det ovenstående ind i dit tidligere svar?

Nu har du også hjulpet mig lidt ud over tabelopbygningen ved at skrive forespørgslerne også, så hvordan gør vi lige med points? Skal jeg oprette et spørgsmål til og acceptere dit svar? Har ikke helt styr på hvordan man gør det :)

Nedenfor giver jeg kommentarer til dine kommentarer.  Jeg gaar ud fra at du, naar du har studeret mine indlaeg, selv kan bygge tabelstrukturen videre hvis du kommer i tanker om yderligere krav (isaer hvis du kikker paa et par database design tutorials.)  Points gives som bekendt ved at du accepterer mit svar.  Hvis du vil give yderligere points maa du oprette et nyt spoergsmaal "Points til Christian_Belgien" og i det nye spoergsmaal referer til dette spoergsmaal.

Jeg beskrev de antagelser jeg gjorde for at du kunne rette de der ikke stemmer med dit projekt.  Og det gjorde du saa.

For at starte med det nemme:  Du har brug for oplysninger over kunder der gaar videre end de specifikke formaal du naevner i denne traad.  Jamen som jeg forklarede, det ordner du ved at tilfoeje ekstra felter til Kunde tabellen.  Altsaa, for eksempel:

Kunde
id, navn (som du maaske vil dele i fornavn og efternavn), adresse, postnummer, by, telefon, .... (hvis nogle kunder er moms registrerede saa moms nummer for fakturering), (alt andet du har brug for)

Soege efter antal projekter per postnummer:

SELECT k.postnummer, COUNT(*) FROM Kunde k JOIN Projekt p ON k.id = p.kundenummer ORDER BY k.postnummer

Projekter med flere laes og flere afleveringssteder er allerede daekket med tabelstrukturen

Kilometer i transporten ordnes ved i tabellen Transport at indfoere et felt for kilometer.

Saa faar du nummererede foelgesedler og kvitteringer fra varmevaerkerne. Jeg paapeger, at hvis hvert enkelt varmevaerk nummererer sine egne foelgesedler og kvitteringer og starter ved nummer 1, saa bliver numrene ikke unikke, men det goer vel ikke noget.  Du tilfoejer til Transport tabellen felterne foelgeseddel og kvittering (udover kilometer).  Hvis du saa staar med en foelgeseddel nummer 123 fra Lille Udby varmevaerk og skal finde transporten er du saa noedt til at kombinere modtagernummer og foelgeseddel nummer, saasom:

SELECT t.id FROM Transport t JOIN Modtager m ON t.modtagernummer = m.id WHERE m.navn = 'Lille Udby Varmevaerk' AND t.foelgeseddel = 123

Men saa bliver det tricky naar et laes kan koere til flere projekter/kunder.  Hvordan bestemmer du saa hvor meget der hoerer til hvert projekt?  For det er vel paa det modtagende varmevaerk at laesset vejes og maales og vandprocenten faststilles?  Skoenner du at laesset skal deles 1/2 - 1/2 eller maaske i nogle tilfaelde 1/3 - 2/3?  Du siger "..et projektnummer kan KUN have et kvit_nr."  Det er formodenligt saaledes at hvert projekt har et kvit_nr for hvert laes der hoere til projektet, men at der for hvert laes kun er et kvit_nr per projekt skoent et laes kan hoere til flere projekter.

Men hvis et laes kan hoere til flere projekter, saa maa du dele Transport tabellen i en Transport og en Afhentning tabel hvor en transport har en eller flere afhentninger.  Projektnummer, afhentningsadresse, vaegt, og RM kommer saa til at hoere til Afhentningstabellen mens resten hoerer til Transport tabellen (jeg gaar ud fra at der ikke kan konstateres forskelle i vandprocent mellem afhentninger fordi det hele blandes paa varmevaerket.)  Saa:

Transport
id, dato, modtagernummer, vandprocent, transportoer, vogn, kilometer, foelgeseddel, kvittering

Afhentning
id, transportnummer, projektnummer, fra_adresse, fra_postnummer, fra_by, vaegt, RM
transportnummer har fremmednoegle til Transport.id, og projektnummer har fremmednoegle til p.id

Saa skal forespoergslerne naturligvis ogsaa tilpasses, for eksempel afleveringer per projekt bliver saaledes:

SELECT t.dato, a.fra_adresse, a.fra_by, m.navn, t.transportoer, t.vogn, a.vaegt, a.RM, t.vandprocent FROM Transport t JOIN Modtager m ON t.modtagernummer = m.id JOIN Project p ON t.id = p.transportnummer JOIN Projekt p ON a.projektnummer = p.id

Det er kæmpe stor hjælp det der ;) Jeg går I gang med at sætte det op.

Med hensyn til de delte læs, så sidder der en måler på lastbilen som måler antal rm og på varmeværket bliver nettovægten registreret. Vognmanden udfylder en følgeseddel til hvert projektnummer hvor hen afhenter og skriver det antal rm han har hentet og når han senere får vægt_kvittering så kan han regne sig tilbage og dele vægten ud til de to eller tre steder han har hentet fra. Man jeg kan godt følge dit forslag til at dele transporttabellen op i 2 (transport og afhentning) og ja, vandprocenten er den samme.

Jeg citerer dig lige: " Du siger "..et projektnummer kan KUN have et kvit_nr."  Det er formodenligt saaledes at hvert projekt har et kvit_nr for hvert laes der hoere til projektet, men at der for hvert laes kun er et kvit_nr per projekt skoent et laes kan hoere til flere projekter" Dette har du helt ret i :)

Jeg accepterer dit svar og opretter helt sikkert et nyt til dig i det videre forløb :) Nu har jeg noget at arbejde med :)