Help me Value DB Design
Posted: 18.06.2006, 14:20
Hi, would be great if you could help value my design (with view on dupes, performance => 3NF,BCNF) - constructive comments are welcome 
some facts:
1.) DiscID is calculated from offsets relative to cd, so that values mostly are unqiue, but don't have to , that is why we need a own cdID.
In the case the discid is same for different albumormixid, we will have the advantage to save them both.
2.) We have several sources for genres. We receive several (unknown amount) of genre votes for a whole cd(discid) and also will receive genre votes for single songs (artistid,songid) later.
That is my first idea was to save genres only linked to songids, what would make it necesarry to link all songs on a cd to that genre if a genre vote is made for a whole cd.
The "genrevotecounter" is increased for every vote on the same genre for a song (artisid,songid). Later we can choose genre by the majority of votings, as is.
3.) Every cd can be a album,maxi or sampler/mix.
Album,maxi= all songs are from same artist
sampler,mix= songs from different artists
As you see i didn't design that explicit, but used albumormix string for albums,maxis and sampler/mix cd names. The type of a cd can be received by asking if all songs of an albumormix cd are same strings , but i am not sure if that is good design.
Thanks in advance,
Code: Select all
t1:artists[pk(artistid),uniqueIndex(name)]
t2:albumormix[pk(albumormixid),uniqueIndex(name)]
t3:songs[pk(songid),uniqueIndex(name)]
t4:genres[pk(genreid),uniqueIndex(name)]
t5:submitter[pk(submitterid),uniqueIndex(name),submitcounter]
t6:song2artist[pk(artistid,songid,fk_t4(genreid)), genrevotecounter]
t7:song2albums[pk((fk_t6(artistid,songid,genreid),fk_t2(albumormixid))]
t8:cds[pk(cdid),uniqueIndex(discid,fk_t2(albumormixid),year,fk_t4(genreid)),plength,fk_t5(submitterid]
t9:cdtracks[pk(fk_t4(cdid),fk_t6(artistid,songid,genreid),trackpos),offset,plength]
1.) DiscID is calculated from offsets relative to cd, so that values mostly are unqiue, but don't have to , that is why we need a own cdID.
In the case the discid is same for different albumormixid, we will have the advantage to save them both.
2.) We have several sources for genres. We receive several (unknown amount) of genre votes for a whole cd(discid) and also will receive genre votes for single songs (artistid,songid) later.
That is my first idea was to save genres only linked to songids, what would make it necesarry to link all songs on a cd to that genre if a genre vote is made for a whole cd.
The "genrevotecounter" is increased for every vote on the same genre for a song (artisid,songid). Later we can choose genre by the majority of votings, as is.
3.) Every cd can be a album,maxi or sampler/mix.
Album,maxi= all songs are from same artist
sampler,mix= songs from different artists
As you see i didn't design that explicit, but used albumormix string for albums,maxis and sampler/mix cd names. The type of a cd can be received by asking if all songs of an albumormix cd are same strings , but i am not sure if that is good design.
Thanks in advance,