[Access] Database ontwerp

[Verwijderd]

Goed, aangezien mijn cd collectie de spuigaten uitloopt kwam ik op het grandioze idee om hier maar eens wat aan te doen mbv een database en alle cd's netjes een nummertje geven met onderscheid tussen games, movies en music cd's etc.

Nu ben ik al een tijdje aan het brainstormen met wat voor info ik erin wil hebben alleen ik twijfel nog steeds of mijn ontwerp wel klopt. Het is alweer een aantal jaartjes geleden dat ik voor het laatst heb genormaliseerd dus als iemand dit wil controlen voordat ik verder ga, dan graag!

Dit is wat ik tot nu toe heb:

cd(cdID, musicID, gameID, movieID, name)
music(musicID, artistID, albumID, trackID, infoID)
game(gameID, titleID, genreID, infoID)
movie(movieID, titleID, genreID, infoID)

musicArtist(artistID, name)
musicAlbum(albumID, name)
musicTrack(trackID, albumID, track)
musicInfo(infoID, lenght, bitrate)

gameTitle(titleID, name)
gameGenre(genreID, name)
gameInfo(infoID, developer, publisher, year, plotSummary, totalcd, IGN)

movieTitle(titleID, name)
movieGenre(genreID, genre)
movieInfo(InfoID, lenght, plotSummary, director, IMDB)

ERD (clickable):


Klopt dit wel een beetje of zouden jullie het anders doen. Is dit wel efficient? Ik kijk er namelijk zo raar tegenaan..

Alvast bedankt

[Emmeau]

Ik ben hier ook een tijdje (en nog steeds) mee bezig, het probleem waar ik nog steeds over aan het denken ben, is verzamel cd's. Die hebben meer dan 1 artiest.

Dan kom je al gauw op iets als
een cd heeft nummers
een nummer heeft 1 of meer artiesten

Ben er nog steeds niet over uit

[DJ-B]

Niet direct een antwoord op je vraag maar meer een opmerking.
Ga je alle attributen ook werkelijk gebruiken? Ik kan me voorstellen dat je weinig zin hebt om voor elke track ook de length in te vullen.

Verder lijkt mij het normaliseren in deze niet van enorm belang, en zal ik gewoon beginnen.

Succes

[AceRimmer]

Waarom zet je de titel en info in aparte tabellen gameTitle en gameInfo?
Ik denk niet dat het zin heeft exact dezelfde informatie hieruit voor meerdere games te gebruiken.
gameGenre daarentegen is wel ok, er zijn meerdere games met exact hetzelfde genre.

Hetzelfde geldt voor movieTitle en movieInfo.

[Arnaud]

Dit is waarschijnlijk 's werelds meest gemaakte database-ontwerp. Als je hier op GOT naar zoekt (of met Google) krijg je honderden kant-en-klare ontwerpen die rekening houden met alle mogelijkheden waar je nooit zelf aan zou hebben gedacht. Emmeau noemt er al eentje op die je eenvoudig kunt oplossen met een "koppeltabel". Oftewel een tabel die een 1-op-n relatie heeft met zowel de artiesten-tabel als de cd-tabel. Een nog betere oplossing is om een cd als een samenstelling van allerlei tracks te zien. Op die manier kan een track ook op meerdere cd's voorkomen.

[Stefke]

Ontwerp is totaal niet volgens normalisatie

De aparte tabellen gametitle, gameinfo, movietitle, movieinfo, musicalbum, musictrack, musicinfo zijn volstrekt overbodig, want allemaal uniek voor elke CD of track (en als dat niet is dan hoeven ze niet uniek te zijn, want elke CD is al uniek via CDid)

De tabellen game, movie en music zouden eigenlijk één tabel moeten zijn, wat je feitelijk ook gedaan heb in de tabel CD, alleen de uitsplitings naar de 3 subtabellen is overbodig (is eigenlijk dezelfde foutieve uitsplitsing als je gedaan hebt bij de hierboven genoemde tabellen)

Feitelijk heb je alleen de volgende tabellen nodig:

tabel_CD

CD relatie naar:
--tabel_CDgenre (game, movie, music)
--tabel_Track

Track relatie naar:
--tabel_Moviegenre (romantiek, actie etc)
--tabel_Gamegenre (FPS, RTS etc)
--tabel_Musicgenre (romantisch, pop etc)

--tabel_Artist (zanger, softwaretoko)

Musicinfo, tracklength en zo, moet gewoon als veld in de tabel track (want elke track heeft maar één keer die info), net als gametitle, gameinfo, movietitle, movieinfo, musicalbum, musictrack, musicinfo
Gametitle, CDtitle, movietitle, is allemaal hetzelfde veld in de tabel CD

Musicalbum is overbodig, omdat een CD en een musicalbum hetzelfde zijn (of het moet zijn dat je een uitsplitsing wil maken voor albums die uit meer CDs bestaan, dan kun je dat wel gebruiken, maar dan moet het een link zijn van Album naar CD, want een album bestaat uit 1 of meer CDs)

De reden dus dat het bijv. niet juist genormaliseerd is is omdat zoals ik al aangeef bijv gametitle, CDtitle, movietitle onnodig uitgesplitst zijn in meer tabellen. En nog wel wat dingen.

Helaas heb ik hier geen access dus ik kan niet effe simpel een sturctuurtje maken om te laten zien, dus ik zou zeggen: probeer nog es dan kijk ik nog effe

[ Voor 61% gewijzigd door Stefke op 18-09-2003 22:43 ]

[Verwijderd]

DJ-B schreef op 18 september 2003 @ 22:07:
Niet direct een antwoord op je vraag maar meer een opmerking.
Ga je alle attributen ook werkelijk gebruiken? Ik kan me voorstellen dat je weinig zin hebt om voor elke track ook de length in te vullen.

Waarschijnlijk niet, dit was meer om het idee een beetje duidelijker te maken. Een cd toevoegen moet snel en simpel zijn natuurlijk :)[/quote]

Arnaud schreef op 18 september 2003 @ 22:16:
Dit is waarschijnlijk 's werelds meest gemaakte database-ontwerp. Als je hier op GOT naar zoekt (of met Google) krijg je honderden kant-en-klare ontwerpen die rekening houden met alle mogelijkheden waar je nooit zelf aan zou hebben gedacht.

Now where's the fun in that?

Sorry, ik vind het een zeer onlogisch ontwerp

De tabellen gametitle, gameinfo, movietitle, movieinfo, musicalbum, musictrack, musicinfo zijn volstrekt overbodig, want allemaal uniek voor elke CD (en als dat niet is dan hoeven ze niet uniek te zijn, want elke CD is al uniek via CDid)

De tabellen game, movie en music zouden eigenlijk één tabel moeten zijn, wat je feitelijk ook gedaan heb in de tabel CD, alleen de uitsplitings naar de 3 subtabellen is overbodig (is eigenlijk dezelfde foutieve uitsplitsing als je gedaan hebt bij de hierboven genoemde tabellen)

Ongeveer hetzelfde wat AceRimmer dus zegt. Hmm.. dus eigenlijk zou je dan maar een paar tabellen overhouden met heel veel attributen als het klopt wat jij zegt. Hehe, ik ga maar even een paar aanpassingen maken.

bedankt allemaal.

[Stefke]

had nog wat bijgetypt dus mn quote klopt niet helemaal

En inderdaad maar een paar tabellen. Meer is het toch ook niet? Een album (=tabel) kan uit 1 of meer CDs (=tabel) bestaan, die weer uit 1 of meer tracks (=tabel), bijv: een spel (1 track) of een film (1 track) of een audioCD (meer tracks).

Albums kun je onderverdelen in categorien (= tabel albumscategorie) (game, movie, audio), artiesten/bedrijven maken 1 of meer CDs (= tabel) en verder is het alleen maar info die betrekking heeft op 1 specifiek item (album, CD, track)

Let op dus: de onderverdeling in game, movie of audio maak je dus op albums, niet op de CDs (of je moet albums hebben die uit audio en/of game en/of movie CDs bestaan, maar dat lijkt me niet ):+
Hetzelfde geld voor albums

Genres is wat minder strikt. Je kunt in principe voor albums een genretabel maken, voor CDs EN voor tracks, maar het is net zo goed mogelijk dezelfde tabel te gebruiken voor genre van albums, CDs en tracks. Afterall, een romantisch albums heeft romantische tracks.
Wel zou ik de mogelijkheid makne om meerdere genres aan 1 CD of track te hangen (dan moet je een tussentabel maken met dubbele sleutel) zodat je een meer op meer relatie krijgt.

Je kunt zelfs de genres voor films en music en games in 1 tabel gooien:

code:

genre       games     film     music
romantisch  x         x        x
aktie       x         x
FPS         x
pop                            x

En dan dus die ene tabel gebruiken voor albums, CDs en tracks

u see?

[ Voor 122% gewijzigd door Stefke op 18-09-2003 22:58 ]

[Verwijderd]

Nou stefijn, jij weet wel hoe je een heel ontwerp op z'n kop kan zetten.

Ik ga even alles bestuderen en als ik het zo bekijk dan duurt dat nog wel ff .

// edit:

Ok, ik volg je niet op elk punt maar dit is wat ik er op dit moment van maak:

ALBUM(albumID, genreID, catID) --> verder niks?

ALBUMCATEGORIE(catID, game, movie, music) --> dit volg ik niet helemaal, wat voor datatypes krijgen game, movie etc?

CD(albumID, cdID, trackID, title)

GENRE(genreID, romantiek, actie, fps, rts)

TRACK(trackID, tracklength, bitrate) --> en verder alle andere info zoals plotSummary, developer, publisher van game, music en films. hele waslijst.

Genres is wat minder strikt. Je kunt in principe voor albums een genretabel maken, voor CDs EN voor tracks, maar het is net zo goed mogelijk dezelfde tabel te gebruiken voor genre van albums, CDs en tracks. Afterall, een romantisch albums heeft romantische tracks.

Ik denk dat ik het maar hou op genre per album. Is het trouwens niet handiger om per cd soort (movie, music, game) een genre tabel te maken zoals je in je eerste suggestie aangeeft. Lijkt mij overzichtelijker. Ik zie de gedachtegang niet echt om dit allemaal in 1 tabel te stoppen.

Dit dus:

Track relatie naar:
--tabel_Moviegenre (romantiek, actie etc)
--tabel_Gamegenre (FPS, RTS etc)
--tabel_Musicgenre (romantisch, pop etc)

Wel zou ik de mogelijkheid makne om meerdere genres aan 1 CD of track te hangen (dan moet je een tussentabel maken met dubbele sleutel) zodat je een meer op meer relatie krijgt.

dat lijkt me inderdaad een goed idee. Zal zometeen ff een schema maken met de sleutels e.d.

//edit 2: hmm.. nieuwe erd laat ik even zitten totdat ik het verband zie tussen albumcategorie en album. Zou je kunnen uitleggen wat ik daarmee moet? ik zie er namelijk geen nut in.

[ Voor 93% gewijzigd door Verwijderd op 19-09-2003 00:16 ]

[Stefke]

Die albumcategorie doet waar jij in je ontwerp de onderverdeling hebt gemaakt in tabellen games, music en movies, nl. een splitsing in deze 3 typen. Het voordeel is bijvoorbeeld dat je de gegevens in 1 tabel maakt en zo bijv simpel een optelling kunt maken van je movies, games en music

music: 10
game: 5
movie: 12

Zon optelling is veel lastiger in jouw ontwerp, want dan moet je data uit allerlei tabellen bij elkaar gaan rapen

Datatype is ja/nee. Je kunt dan bij het keuzemenu waarbij je het genre aangeeft de keuze laten beperken op de albumcategorie (filteren op genres voor musicCDs als je een musicCD aan het bewerken bent)

[ Voor 51% gewijzigd door Stefke op 19-09-2003 08:41 ]

[Arnaud]

quote:
--------------------------------------------------------------------------------
Arnaud schreef op 18 september 2003 @ 22:16:
Dit is waarschijnlijk 's werelds meest gemaakte database-ontwerp. Als je hier op GOT naar zoekt (of met Google) krijg je honderden kant-en-klare ontwerpen die rekening houden met alle mogelijkheden waar je nooit zelf aan zou hebben gedacht.

--------------------------------------------------------------------------------
Now where's the fun in that?

Ik heb het niet over fun, ik heb het over nuttige ideëen waardoor je niet het wiel helemaal opnieuw hoeft uit te vinden. Zoals je ziet leek jouw eerste ontwerp in de verste verte niet op een goed ontwerp. De ideëen die hier langskomen zijn vele malen beter. Door naar deze tips te luisteren en enkele bestaande ontwerpen te bekijken kun je een veel beter eigen ontwerp maken. Ik denk dat je (minimaal) uit moet gaan van de volgende tabellen:
Albums. Een album is een verzameling van 1 of meerdere cd's die om 1 of meerdere redenen bij elkaar horen. Een album heeft een ID, een naam, een genre, een jaar van uitgave, etc.
CD's. Een cd is een onderdeel van een album en een verzameling van 1 of meerdere tracks die op een fysiek medium bij elkaar staan. Een cd heeft een ID, een naam, een genre, een type, 1 of meerdere uitvoerende artiesten (twijfelgeval, eigenlijk hoort dit op track-niveau), een producent, een uitgever, etc.
Tracks. Een track is een stuk van een cd met een duidelijk begin en einde. Een track heeft een ID, een naam, een genre, een type, 1 of meer uitvoerende artiesten, een lengte, een producent, etc.
Artiesten. Een artiest is de uitvoerende persoon van een track (en naar eigen inzicht eventueel ook van een cd). Een artiest heeft een naam, een geslacht, een geboortedatum, etc.
Genres. Een lijst met waarden waar sommige andere tabellen gebruik van kunnen maken om een keuze uit te maken. Dient vooral om typefouten te voorkomen, queries makkelijker te kunnen maken, etc. Dit is een voorbeeld van goed normaliseren. Afhankelijk van je kennis kun je met samengestelde sleutels of de "Stefijn-methode" zelfs ongeldige combinaties van genres met Albums, CD's en Tracks voorkomen
Types. Zie Genres.

Eventueel kun je nog een tabel Collecties toevoegen, waarbij een collectie dan weer bestaat uit 1 of meerdere albums.

Hou verder je kolomnamen algemeen zodat je als naam van een artiest van een Audio-cd Madonna kunt gebruiken maar voor een Game-cd bijvoorbeeld Max Payne.

Door het goed normaliseren verkrijg je automatisch een "drill-down"-structuur. Welke kolommen je dan in een tabel wilt plaatsen is verder niet interessant en verder ook niet moeilijk. Een haarkleur toevoegen bij een artiest is net zo makkelijk als een orkestnaam bij een track.

[Verwijderd]

Arnaud schreef op 19 September 2003 @ 10:47:
[...]

Ik heb het niet over fun, ik heb het over nuttige ideëen waardoor je niet het wiel helemaal opnieuw hoeft uit te vinden. Zoals je ziet leek jouw eerste ontwerp in de verste verte niet op een goed ontwerp. De ideëen die hier langskomen zijn vele malen beter. Door naar deze tips te luisteren en enkele bestaande ontwerpen te bekijken kun je een veel beter eigen ontwerp maken.

Dat er van die kant-en-klare databases zijn dan weet ik ook wel maar daar gaat het mij niet om. Ik wou sowieso weer eens aan de slag met access en een cd database leek mij de perfecte uitdaging (voor jou misschien niet). Dat mijn eerste ontwerp niet klopte had ik al op gerekend anders had ik dit topic in de eerste plaats nooit gemaakt. Bovendien vind ik het helemaal niet erg om fouten te maken, leer je alleen maar van en ik zie dit meer als een uitdaging .

Types. Zie Genres.

Zou je dit nog kunnen toelichten? Wat voor types bedoel je hiermee? track type? cd type? album type? bitrate oid?

Ik heb jullie ideeen bestudeerd en uitgewerkt en mijn tweede ontwerp ziet er als volgt uit:



Ik zit nog steeds met het probleem van de genre's, volgens mij gaat dit, zoals ik het nu heb, niet werken. Er zit namelijk ook geen verwijzende sleutel en ik zou niet weten wat voor info in die tussentabel moet komen. Het eerste advies van stefijn lijkt mij de makkelijkste oplossing (voor elke cd een aparte genre tabel) maar het combineren lijkt mij ook wel iets hebben. Morgen maar even het boek erbij pakken en kijken of ik daar nog wat wijzer van wordt .

Anyway, volgens mij lijkt dit al meer op een juiste normalisatie. Op- en aanmerkingen zijn uiteraard welkom .

[Arnaud]

Dat er van die kant-en-klare databases zijn dan weet ik ook wel maar daar gaat het mij niet om..........Bovendien vind ik het helemaal niet erg om fouten te maken, leer je alleen maar van

Het ging mij er ook niet om dat je zo'n kant-en-klare database ging gebruiken. Ik bedoelde juist dat als je er een paar had gedownload (en daarvan de datastructuur had bekeken) je zelf een datastructuur had kunnen ontwerpen die het beste uit de kant-en-klare databases had gecombineerd. Iets "from scratch" ontwerpen is vrijwel nooit de beste oplossing, learn-by-example!

Met type bedoelde ik Game, Movie, Music, etc. Jij hebt dit echter anders opgelost, maar ik denk toch dat je dit beter op kunt lossen zoals ik hieronder met genres uit zal leggen.

Zoals je nu Genre hebt opgelost is het niet goed genormaliseerd. Als je namelijk een Album hebt met GenreID=1, fps=True, rts=False, pop=True, rock=False dan heeft een CD met GenreID=1 dus ook fps=True, rts=False, pop=True, rock=False terwijl dit helemaal niet zo hoeft te zijn. Genres kun je het beste oplossen met een "koppeltabel". Als voorbeeld neem ik de tabel Albums en de tabel Genre. In Album staan 3 albums met albumID=1, albumID=2 en albumID=3. In Genre staan 2 velden: GenreID (Autonumber) en GenreNaam (Text). Er zijn op dit moment 4 Genres bekent: 1=fps, 2=rts, 3=pop, 4=rock. Je maakt vervolgens een koppeltabel Genres_Per_Album met 2 velden albumID (Number) is een n-1 relatie met albumID in de tabel Album en GenreID is een n-1 relatie met GenreID in de tabel Genre. Schematisch is dit dus ALBUMS (albumID) ---> Genres_Per_Album (albumID), Genres_Per_Album (genreID) <--- GENRE (genreID). In Genres_Per_Album komt dan iets te staan als 1,1 1,3 2,1 2,4 3,3 (Album1=fps en rts, Album2=fps en rock, Album3=pop). Zo'n aparte koppeltabel kun je voor iedere gewenste tabel die een relatie heeft met Genre aanmaken.

Als laatste tip wil ik je nog vragen om als eerste veld in een tabel altijd de Primary Key te zetten, als volgende velden alle Foreign Keys en pas daarna alle "Gewone" properties. Op deze manier is veel duidelijker hoe tabellen met elkaar verweven zijn en is het uitbreiden met een extra veld (onderaan) ook veel duidelijker.

Het ziet er inderdaad vele malen beter uit dan je eerste opzet, maar de koppelingen met GENRE zul je echt aan moeten passen.

[ Voor 5% gewijzigd door Arnaud op 20-09-2003 12:37 ]

[Stefke]

Het ziet er iig al een stuk beter uit niet?

[Verwijderd]

Het valt me op dat er heeltijd gebruik wordt gemaakt van id's. Dus AlbumID, GenreID, enz... Maar waarom eigenlijk? Het is veel handiger om hiervoor bijvoorbeeld de naam te gebruiken. Dus als primary key voor een Album zou je gewoon de naam van het album kunnen gebruiken. Vaak wordt gezegd dat string stukken trager zouden zijn. Volgens mij valt dat wel mee en weegt het niet op tegen de lagere onderhoudskosten.

En hoe komen jullie bij jullie database ontwerp? Door gewoon te vergelijken, proberen en te brainstormen? Ik heb hiervoor NIAM geleerd, een heel sterke analyse methode die een aantal stappen beschrijft om informatie te analyseren. Het resultaat hiervan (IGD) vergelijkbaar met ERD kan omgezet worden naar een DB-structuur. Als er dan een fout in je DB-structuur zit, dan is ALTIJD zo dat de NIAM of de IGD->DB stappen niet goed gevolgd zijn. Ik raad je aan om een analyse methode te gebruiken. NIAM is er hier een biedt heelveel houwvast. Andere methoden zijn vaak wat vrijer en hier kunnen dan meer fouten gemaakt worden. Deze zijn vaak handig na wat meer ervaring. Uiteindelijk, als je heel veel ervaring hebt, dan schrijf je zo'n ontwerp zo uit je hoofd op. (Maar dat duurt ff en is moeilijk om mee te beginnen )

Handige tekentool voor de NIAM igd's: http://www.niamtool.nl

[Varienaja]

Verwijderd schreef op 20 September 2003 @ 12:40:
Het valt me op dat er heeltijd gebruik wordt gemaakt van id's. Dus AlbumID, GenreID, enz... Maar waarom eigenlijk?

Er zijn twee bands die 'Nirvana' heten, daarom.

[Verwijderd]

Varienaja schreef op 20 september 2003 @ 12:49:
[...]
Er zijn twee bands die 'Nirvana' heten, daarom.

Ok, naam v.d. band is dus niet uniek, dan zou je kunnen kijken of iets anders uniek is (bandnaam + datum oprichting b.v.) of anders idd een id gebruiken. Maar ik zou dat echt als laatste redmiddel gebruiken en niet overal een id inzetten.

[bille]

ik vind dit een vrij statisch datamodel. Stel je krijgt nu een nieuwe website erbij met info over films.. in welke kolom in welke tabel ga je dat zetten dan, want om voor zoiets je datamodel aan te passen ..... Je zou kunnen kiezen om een iets dynamischer model te implementeren bijv:

tbl_object
-object_id
-objecttype_id

tbl_contentfield
-contentfield_id
-contentfield_content
-contentfieldtype_id
-object_id

tbl_contentfieldtype
-contentfieldtype_id
-contentfieldtype_description

tbl_contentfieldtype_objecttype
-contentfieldtype_objecttype_id
-contentfieldtype_id
-objecttype_id

tbl_objecttype
-objecttype_id
-objecttype_description

Zoals je ziet zitten er een N-op-N relaties in:
- contentfieldtype en objecttype

voor de rest is het heel simpel.. eerst definieer je een aantal objecttypes (film.. muziek etc) voor ieder objecttype bedenk je een aantal attributen dat je wil gaan opslaan, deze zet je in contentfieldtype. Vervolgens koppel je de attributen met de objecttypen in tbl_contentfieldtype_objecttype. Nu kan je content gaan toevoegen aan je objecten door per attribuut dat je hebt 1 record vullen in tbl_contentfield en daarbij sla je uiteraard ook op om welk contentfieldtype het gaat en bij welk object dit attribuut hoort.

Wil je opvragen welke type attributen 1 object heeft? daar is de tabel tbl_contentfieldtype_objecttype voor. Wil je alle content opzoeken van 1 object, dan haal je dat gewoon uit de content tabel, want daar staan ook de objectid's in.

owja: je moet wel de juiste indexes kiezen voor tbl_contentfield. Als je gemid. 15 attributen hebt per object en je hebt 200 objecten.. dan zijn dat dus 3000 records in je tbl_contentfield tabel. Opzich zou Access daar geen moeite mee moeten hebben, maar het zou eventueel kunnen schelen als je geen efficiente queries maakt voor dit datamodel.

[ Voor 11% gewijzigd door bille op 20-09-2003 13:03 ]

[Arnaud]

De belangrijkste reden voor het gebruiken van een ID als Primary Key is dat een ID (normaal gesproken een autonumber) altijd uniek is en een Naam niet.

De extra performance van een Integer ten opzichte van een string is vaak niet of nauwelijks interessant, maar juist bij de Primary Key wel omdat hier vrijwel altijd indexen op liggen. Ook bij het maken van queries over meerdere tabellen zijn normaal gesproken de Primary keys betrokken omdat die de 1-n verbinding bepalen.

Het verbaasd me eigenlijk dat iemand die zo'n officiële methode als NIAM heeft geleerd dit soort basisprincipes van databases niet heeft geleerd.

Persoonlijk bouw ik vooral kleinere databases (maximaal 20 hoofdtabellen, 50 koppeltabellen, 100 "opzoektabellen", een paar honderd tabel-relaties) en door mijn ervaring kan ik dat zo uit mijn hoofd opschrijven waarna ik met een paar twijfelgevalletjes nog een beetje ga schuiven. In een beperkt aantal gevallen maak ik zelfs bewust een paar keuzes die niet overeenkomen met de theorieën, maar die beter aansluiten bij het gebruik van de klant. Bijna alle theorieën draaien namelijk op het voorkomen van redundantie, terwijl performance bij klanten soms belangrijker is.

[Verwijderd]

Een deel van het NIAM igd zal er dan ongeveer zo uit gaan zien: http://home.hetnet.nl/~fammaarse/tmp/cd_collectie.jpg

De tabelstructuur die van dit (DEEL) volgens de IGD-->DB afgeleid kan worden is:

Track(MUSICCD_ID, TRACK_ID, song_artist*, song_title*)
musicAlbumCd(MUSICCD_ID, ALBUM)
gameCd(CD_ID)

veld* = verplicht veld
VELD = (onderdeel van) primary key

Nog lang niet alle informatie is geanaliseerd, dit is alleen bedoeld om een klein voorbeeldje te geven.

[bille]

re: Arnoud, als jij met ID kolommen werkt dan wordt je keihard gebitchslapped door iedere professor van de TU als je daar niet een goed beargumenteerdbare reden voor hebt. FEITELIJK is in de meeste gevallen een ID kolom redundant, daar er wel situaties zijn waarin het wordt toegestaan indien het tot performance verbeteringen kan leiden óf wanneer een object niet uniek identificeerbaar is door zijn attributen of een combinatie daarvan.

altijd uniek is en een Naam niet

Wat je daarbij vergeet is dat je ook een stuk businesslogica kan inbouwen JUIST door een PK of uniciteitsconstraint te maken over meerdere kolommen. Wie zegt dat je meerdere van dezelfde namen in de tabel MAG hebben? etc. Het gaat erom dat je eerst definieerd wat functioneel kan.. Kunnen er uberhaupt 2 boeken zijn met dezelfde titel / schrijver combinatie? Zo niet dan is het dus mogelijk om een PK te leggen over de titel en schrijver kolom, ondanks dat zowel de titel als de schrijver meerdere keren voorkomen.

[Verwijderd]

bille schreef op 20 September 2003 @ 12:58:
ik vind dit een vrij statisch datamodel. Stel je krijgt nu een nieuwe website erbij met info over films.. in welke kolom in welke tabel ga je dat zetten dan, want om voor zoiets je datamodel aan te passen ..... Je zou kunnen kiezen om een iets dynamischer model te implementeren bijv:
...

Dynamisch is mooi, maar heeft 1 groot nadeel. Onderhoudbaarheid. Het lijkt veel beter onderhoudbaar omdat je zo eenvoudig lijkt te kunnen uitbreiden. Maar hoe bewaar je de integriteit van de database? Je bouwt hier als het ware een tabel schema in een tabel schema. Een database heeft juist een tabel schema om daar constraints op te leggen om ervoor te zorgen dat een database niet corrupt raakt.

Arnaud schreef op 20 September 2003 @ 13:01:
De belangrijkste reden voor het gebruiken van een ID als Primary Key is dat een ID (normaal gesproken een autonumber) altijd uniek is en een Naam niet.

Zoals al gezegd, als er (geen combinatie) van veld(en) in een tabel uniek is, dan zal er een id gebruikt moeten worden. Maar het liefst zo min mogelijk.

De extra performance van een Integer ten opzichte van een string is vaak niet of nauwelijks interessant, maar juist bij de Primary Key wel omdat hier vrijwel altijd indexen op liggen. Ook bij het maken van queries over meerdere tabellen zijn normaal gesproken de Primary keys betrokken omdat die de 1-n verbinding bepalen.

Als in grote databases (Oracle e.g.) de primary key, of een kolom met een index erop een string is, dan wordt er onder water met integers gerekend. Dus mijn mening is dat hier best strings gebruikt mogen worden.

Het verbaasd me eigenlijk dat iemand die zo'n officiële methode als NIAM heeft geleerd dit soort basisprincipes van databases niet heeft geleerd.

NIAM is een analyse methode, heeft in principe niets met DB's te maken. Het is er wel erg handig voor, net zoals het handig is voor het afleiden van een class-structuur.

Persoonlijk bouw ik vooral kleinere databases (maximaal 20 hoofdtabellen, 50 koppeltabellen, 100 "opzoektabellen", een paar honderd tabel-relaties) en door mijn ervaring kan ik dat zo uit mijn hoofd opschrijven waarna ik met een paar twijfelgevalletjes nog een beetje ga schuiven. In een beperkt aantal gevallen maak ik zelfs bewust een paar keuzes die niet overeenkomen met de theorieën, maar die beter aansluiten bij het gebruik van de klant. Bijna alle theorieën draaien namelijk op het voorkomen van redundantie, terwijl performance bij klanten soms belangrijker is.

Perfect ontwerp is een streven, geen must

[Verwijderd]

bille schreef op 20 september 2003 @ 13:13:
re: Arnoud, als jij met ID kolommen werkt dan wordt je keihard gebitchslapped door iedere professor van de TU als je daar niet een goed beargumenteerdbare reden voor hebt. FEITELIJK is in de meeste gevallen een ID kolom redundant, daar er wel situaties zijn waarin het wordt toegestaan indien het tot performance verbeteringen kan leiden óf wanneer een object niet uniek identificeerbaar is door zijn attributen of een combinatie daarvan.

Dat is een hele mooie theorie maar in de praktijk is het toch een stuk handiger om queries en stored procedures te schrijven waarbij je maar op 1 kolom moet joinen en niet op een samengestelde PK.
Bovendien is een enkelvoudige PK ook veel eenvoudiger indien je vanuit een andere tabel deze als FK wilt toevoegen. Met een ID heb je maar 1 kolom van het type integer nodig om te linken, met een n-voudige PK heb je n kolommen nodig met god weet wat voor soort exotische datatypes.

[JaQ]

Verwijderd schreef op 20 September 2003 @ 12:40:
<<snip>>
En hoe komen jullie bij jullie database ontwerp? Door gewoon te vergelijken, proberen en te brainstormen? Ik heb hiervoor NIAM geleerd, een heel sterke analyse methode die een aantal stappen beschrijft om informatie te analyseren.
<<snip>>

Als je hebt opgelet op school, dan had je ook de ERD methode geleerd en bijna rechtstreeks ontdekt dat de relatiediagrammen (goh hoe zouden ze toch aan die naam komen ) die in access gemaakt worden, ontworpen worden volgens de ERD methode.

Ik wil niet gaan flamen, maar de NIAM methode is een geweldige theoretische ontwerp methode die je op een aantal opleidingen leert (vaak in combinatie met het geweldige student-tool FCO-IM). Zou er dan daadwerkelijk een reden zijn dat er bijna geen bedrijven zijn die NIAM als ontwerpmethode kiezen _{zie je wel dat sarcasme kan druipen}

Je leert al gauw dat joins op strings performance technisch gezien niet acceptabel zijn (een integer is in C gewoon sneller en de core van een RDBMS is over het algemeen in C geschreven), daarom wordt er dus gejoined op ID's en niet op logische sleutels. Ook zal je ontdekken dat het gewoon veel meer tijd kost om een werkend ontwerp te maken m.b.v. de NIAM methode t.o.v. de ERD methode.

De illusie dat er een "perfect" ontwerp bestaat voor iedere situatie zal je op een gegeven moment kwijt raken. Er zijn meer wegen naar Rome. (en als je dan echt eerlijk bent, dan zie je dat je in het door jou geproduceerde IGD een aantal uniciteit constraints mist )

zo.. dat is er uit, genoeg geouwehoer dus over wat wel of niet de "ware" database ontwerp methode is. Die discussie hoort (1) niet in dit topic thuis en (2) is net zo zinloos als een "welke scripttaal is beter php of asp" discussie.

edit:
nog maar wat toevoegingkjes

ik mag dan wel geen TU papiertje hebben in Informatica of Systeemanalyse, ik heb wel een HBO papiertje in systeemanalyse met als hoofdrichting databases en daarnaast nog een aantal certificaten van de Fachhochschule Hannover m.b.t. systeemanalyse. Ik denk dus dat ik best snap hoe je een database moet ontwerpen. (Ik verdien mijn brood al een drietal jaren o.a. als database ontwerper). Misschien dat ik mijn mond moet houden als een TU docent of student spreekt, ik heb immers geen TU papiertje, maar ik denk dat ik toch echt wel als "man van de praktijk" mag spreken.

Dat Oracle "strings' omzet naar integers en daar dus mee gaat joinen is waar, maar denk je dat dat omzetten geen CPU kost? (en dus tijd, precies de reden waarom je dus een integer kies?) Een processor die je bitchslapped omdat je een alfanumerieke primaire sleutelattribuut vervangt voor een numerieke (integer) primair sleutelattribuut in combinatie met een unieke sleutel op het alfanumerieke attribuut, kan gewoon simpel weg geen argument buiten redundantie om gebruiken. Het correcte tegenargument is performance. Zeker een samengestelde primaire sleutel (in een andere situatie dan een koppeltabel) moet ten aller tijde vervangen worden door een (1) numerieke primarie sleutelattribuut (wederom i.c.m. een unieke sleutel op de samengestelde sleutel) Ditmaal met als tweetal redenen, namelijk redundantie (1) en performance (2).

Als je dus gebitchslapped wordt door je professor, moet je je eens afvragen of dat komt omdat je een verkeerde ontwerpbeslissing hebt gemaakt, omdat je je ontwerpbeslissing niet hebt afgemaakt/doorgevoerd, of omdat je je beslissing niet beargumenteerd.

[ Voor 33% gewijzigd door JaQ op 21-09-2003 14:43 ]

[djluc]

Ik gebruik vrijwel altijd een ID veld. Het is namelijk mogelijk dat de gebruiker bijvoorbeeld een tikfout maakt in de naam van een boek. De naam wordt in de andere koppeltabellen als key gebruikt. Als je nu de naam verbeterd bestaat de relatie niet meer. Dan zou je dus weer een update moeten gaan doen om alle foreign key's te updaten. Dat lijkt mij niet praktisch.

[Gé Brander]

Wat betreft het wel/niet gebruiken van het ID veld:
Er zijn soms vier uitvoeringen van CD's met dezelfde titel. Bijvoorbeeld, Engelse, Nederlandse, Duitse en Belgische uitvoering. Allemaal anders maar wel een ander ID in de database....

[Verwijderd]

DrFrankenstoner schreef op 21 September 2003 @ 14:27:
[...]


Als je hebt opgelet op school, dan had je ook de ERD methode geleerd en bijna rechtstreeks ontdekt dat de relatiediagrammen (goh hoe zouden ze toch aan die naam komen ) die in access gemaakt worden, ontworpen worden volgens de ERD methode.

Ik wil niet gaan flamen, maar de NIAM methode is een geweldige theoretische ontwerp methode die je op een aantal opleidingen leert (vaak in combinatie met het geweldige student-tool FCO-IM). Zou er dan daadwerkelijk een reden zijn dat er bijna geen bedrijven zijn die NIAM als ontwerpmethode kiezen _{zie je wel dat sarcasme kan druipen}

Dat betekend nog niet dat ze aan de ERD methode vast blijven houden. Tenminste, als je het over dezelfde ERD hebt als ik. (b.v: http://www.smartdraw.com/...xamples/software/erd3.htm) Kan dat ook in Access??? Volgens mij zijn er hier dus 2 ERD's. Database schema in Access en het informatiemodel.
Het klopt dat veel bedrijven (en studenten ) niet voor NIAM kiezen. Er zitten namelijk voordelen en nadelen aan, waarbij 1 heel belangrijk nadeel is dat het tijdrovend is. Voordeel is dat het meer houwvast biedt wat vooral voor de beginneling erg handig is, maar ook voor de beste paarden die weleens willen struikelen . 'k ga NIAM hier niet lopen verdedigen, iedereen moet natuurlijk zelf weten waar hij/zij voor kiest.

Je leert al gauw dat joins op strings performance technisch gezien niet acceptabel zijn (een integer is in C gewoon sneller en de core van een RDBMS is over het algemeen in C geschreven), daarom wordt er dus gejoined op ID's en niet op logische sleutels. Ook zal je ontdekken dat het gewoon veel meer tijd kost om een werkend ontwerp te maken m.b.v. de NIAM methode t.o.v. de ERD methode.

Of het acceptabel is hang natuurlijk van de requirements af. Is performance belangrijk, dan kunnen id's gebruikt worden. Is onderhoudbaarheid belangrijk, dan kan er gekozen worden voor de strings als primary keys.

De illusie dat er een "perfect" ontwerp bestaat voor iedere situatie zal je op een gegeven moment kwijt raken. Er zijn meer wegen naar Rome. (en als je dan echt eerlijk bent, dan zie je dat je in het door jou geproduceerde IGD een aantal uniciteit constraints mist )

idd

zo.. dat is er uit, genoeg geouwehoer dus over wat wel of niet de "ware" database ontwerp methode is. Die discussie hoort (1) niet in dit topic thuis en (2) is net zo zinloos als een "welke scripttaal is beter php of asp" discussie.

Het lijkt me wel goed om de topicstarter enige informatie te geven over welke methoden er zijn + voordelen en nadelen en wat de voordelen en nadelen van b.v. id's zijn. De discussie over het genoemde ontwerp wordt hier op een iets abstracter niveau gevoerd, zodat het wat bruikbaarder en interessanter wordt voor ander lezers. Laten we er geen battle van maken, maar het alleen hebben over voor- en nadelen.

edit:
nog maar wat toevoegingkjes

ik mag dan wel geen TU papiertje hebben in Informatica of Systeemanalyse, ik heb wel een HBO papiertje in systeemanalyse met als hoofdrichting databases en daarnaast nog een aantal certificaten van de Fachhochschule Hannover m.b.t. systeemanalyse. Ik denk dus dat ik best snap hoe je een database moet ontwerpen. (Ik verdien mijn brood al een drietal jaren o.a. als database ontwerper). Misschien dat ik mijn mond moet houden als een TU docent of student spreekt, ik heb immers geen TU papiertje, maar ik denk dat ik toch echt wel als "man van de praktijk" mag spreken.

Als student Informatica van Saxion hogeschool Enschede spreek ik hier dus met een expert. Het zijn de argumenten die tellen, niet de naam, reputatie, papiertjes, enz...

Dat Oracle "strings' omzet naar integers en daar dus mee gaat joinen is waar, maar denk je dat dat omzetten geen CPU kost? (en dus tijd, precies de reden waarom je dus een integer kies?) Een processor die je bitchslapped omdat je een alfanumerieke primaire sleutelattribuut vervangt voor een numerieke (integer) primair sleutelattribuut in combinatie met een unieke sleutel op het alfanumerieke attribuut, kan gewoon simpel weg geen argument buiten redundantie om gebruiken. Het correcte tegenargument is performance. Zeker een samengestelde primaire sleutel (in een andere situatie dan een koppeltabel) moet ten aller tijde vervangen worden door een (1) numerieke primarie sleutelattribuut (wederom i.c.m. een unieke sleutel op de samengestelde sleutel) Ditmaal met als tweetal redenen, namelijk redundantie (1) en performance (2).

Bij een samengestelde primaire sleutel heb je me nu overtuigd. Het is inderdaad sneller en je hebt geen redundantie. Maar, stel als je dan dit schema ontwerpt:

Orders(ID,.....,fk_klantadres->KlantAdressen.ID)
KlantAdressen(ID,klantnaam*,addres*,....)

En nu is er een tabel met klanten die orders mogen plaatsen.

VertrouwdeKlanten(KLANTNAAM,...)

Hoe leg je dan de constraint op de database die ervoor zorgt dat er alleen orders zijn van die klanten? (Van alle klanten, ook die niet mogen bestellen, zijn er addressen aanwezig)
Dit kan gedaan worden door met een join, de klantnaam van de order te achterhalen en te kijken of deze in de tabel VertrouwdeKlanten voorkomt. Het zou sneller gaan als deze tabelstructuur ontworpen was:

Orders(ID,.....,fk_klant->KlantAdressen.KLANT,fk_adres->KlantAdressen.ADDRES)
KlantAdressen(KLANT,ADDRES,....)

Kortom: De oplossing met een id is wel sneller en niet redundant, maar bij uitbreidingen komen we in de problemen. Dus weer een kwestie van prioriteiten. Mijn ervaring is dat bij bedrijfssoftware waar de performance meestal minder belangrijk is, de laatste een goede oplossing is. De topicstarter zal zelf moeten afwegen wat hij belangrijker vindt. En misschien vind hij id's wel handiger omdat hij gewend is om zo te werken. Maar probeer wel de voordelen en nadelen te zien en meer over de ontwerpkeuzes na te denken om tot een beter ontwerp te komen.

Owja, de constraint had ook gewoon een opvraag kunnen zijn

[JaQ]

the_tag-man schreef op 21 September 2003:
Dat betekend nog niet dat ze aan de ERD methode vast blijven houden. Tenminste, als je het over dezelfde ERD hebt als ik. (b.v: http://www.smartdraw.com/...xamples/software/erd3.htm) Kan dat ook in Access??? Volgens mij zijn er hier dus 2 ERD's. Database schema in Access en het informatiemodel.

De ERD methode zoals die in acces wordt gebruikt is m.i. de technische wijze van tekenen voor een EDR, het document waar je naar verwijst toont de techniek die bekend staat als de methodisch logisch database ontwerp (wat tevens onderdeel van de ERD methode is). Maar goed, niet zo relevant in deze.

Als student Informatica van Saxion hogeschool Enschede spreek ik hier dus met een expert. Het zijn de argumenten die tellen, niet de naam, reputatie, papiertjes, enz...

om de een of andere reden was dit het punt wat ik ook wilde maken.. maar je hebt gelijk, het gaat niet om het papiertje etc.

Bij een samengestelde primaire sleutel heb je me nu overtuigd. Het is inderdaad sneller en je hebt geen redundantie. Maar, stel als je dan dit schema ontwerpt:
<<een voorbeeld>>

Je voorbeeld is niet helemaal juist in dit geval, omdat je over een attribuut van "klant" praat, maar genoeg offtopic.


Terug naar de originele vraag van de TS. Ik vind het niet zo heel vreemd dat je er voor kies om de gegevens m.b.t. films, games en audio te spiltsen. Je geeft heel terecht aan dat je 3 soorten cd's gebruikt. Dat zo er op wijzen dat je dus een sup-supertype constructie hebt. (Het supertype is dan CD met de attributen die voor alle 3 de subtypes gelden en een drietal subtypes: gamecd, filmcd en audiocd met ieder hun specifieke attributen). Ondanks dat dit niet generiek is en enigszins lastig is uit te breiden als er een nieuw cd-type bij komt, zou dit toch de constructie zijn die ik zou verkiezen, al is het alleen maar omdat ik persoonlijk erg van een sub-supertype constructie gecharmeerd ben. Uiteraard weet ik best dat het (zeker bij een relatief kleine database) geen nut heeft en dat 1 platte tabel eigenlijk zou volstaan.

Let wel op dat je niet gaat "overnormalizeren". Bijvoorbeeld de gametitle, cdtitle en movietitle zou ik niet in een apparte tabel zetten. De kans dat je 2 cd's hebt met dezelfde titel is nogal klein, dus nogal onzinnig om in een apparte tabel op te slaan. De kunst is dus om enerzijds een "mooi" datamodel te maken, anderzijds een "werkbaar" datamodel te maken.

[Verwijderd]

djluc schreef op 21 September 2003 @ 16:35:
Ik gebruik vrijwel altijd een ID veld. Het is namelijk mogelijk dat de gebruiker bijvoorbeeld een tikfout maakt in de naam van een boek. De naam wordt in de andere koppeltabellen als key gebruikt. Als je nu de naam verbeterd bestaat de relatie niet meer. Dan zou je dus weer een update moeten gaan doen om alle foreign key's te updaten. Dat lijkt mij niet praktisch.

Dit is inderdaad heel vaak de reden voor het gebruik van id's. Er zijn niet echt regels voor te geven wanneer ze gebruikt moeten worden. Wel zie je vaak dat ze gebruikt worden bij primary keys die veel gewijzigd kunnen worden. Dit dus om het updaten van foreign keys te voorkomen. Maar zorg er dan wel voor dat de naam wel uniek blijft. Ik weet niet of dit kan in access.

[djluc]

Je zou een unique index leggen in MySQL, dat kan vast ook wel in Acces.

Let wel op dat je niet gaat "overnormalizeren". Bijvoorbeeld de gametitle, cdtitle en movietitle zou ik niet in een apparte tabel zetten. De kans dat je 2 cd's hebt met dezelfde titel is nogal klein, dus nogal onzinnig om in een apparte tabel op te slaan. De kunst is dus om enerzijds een "mooi" datamodel te maken, anderzijds een "werkbaar" datamodel te maken.

Je moet een unieke combinatie hebben van de combinatie auteur-titel, en niet van alleen de titel. Er zijn wel dezelfde titels, neem dat maar van mij, als DJ, aan.

[ Voor 105% gewijzigd door djluc op 21-09-2003 18:16 ]

[Arnaud]

djluc
Ik gebruik vrijwel altijd een ID veld. Het is namelijk mogelijk dat de gebruiker bijvoorbeeld een tikfout maakt in de naam van een boek. De naam wordt in de andere koppeltabellen als key gebruikt. Als je nu de naam verbeterd bestaat de relatie niet meer. Dan zou je dus weer een update moeten gaan doen om alle foreign key's te updaten. Dat lijkt mij niet praktisch.

the_tag-man
Ik weet niet of dit kan in Access

Ja, maar niet in iedere versie van Access. In Access XP kun je naar de eigenschappen van een relatie aangaan en daar aanvinken of je "Referential Integrity" wilt afdwingen. Als je dit aanvinkt kun je ook "Cascade Updates" en "Cascade Deletes" afdwingen.