Back to the Data Model
These are the User Specifications :-
Here is the process in a nutshell :-
A Song is recorded and can be included on 1 or several
discs and assigned a C# (internal song code)
A disc includes 4, 6, or 16 songs
A disc pack can comprise of several discs.
1) "Pop Hits of the 2002" would include the 12 "Pop Hits of the Month" discs.
2) "Best of the 70's" would include songs that were recorded for single discs,
but complied into a disc set.
The "disc sets" will have a table that will map existing discs to the
disc set they are part of, and that will map back to the songs on that disc.
I have some problems with this, but I'll leave that for another time.
The in-house song "database" includes a BIG Excel file
with the following:
A) C# (internal song code)
B) Song Title
C) Artist (Song Style)
D) Song Key
E) Location - *see note below
Transformation to MS SQL
E) CDG_NUM = disc #
F) TRACK_NUM = track #combined field so I split it up. YUK!
When I import this data into my database I break the
song down into several tables as shown in the photo.
1) Am I normalizing this more than it needs?
Specifically the song titles.
2) Since this is being used more in a "data warehouse"
fashion and is not updated on a daily basis, should I:
A) Denormailze Tbl_songs, Tbl_SongTitles, Tbl_Artists,
into one or two tables to make queries run more efficient?
B) Create a summary table that is updated when any of these tables are modifed?
3) If they want to assign a genre/sub genre to songs and discs,
should I have a table (Tbl_genres) set up with a relationship to
Tbl_DiscGenres and Tbl_SongGenres or do you think I will run into problems?
The Draft Business Rules have been defined and are listed below.
The Area being Modelled is :-
The Operations of a Record Label.
A. DESIGN NOTES :
B. The Things of Interest, ('THINGS'), include :-
C. These THINGS are Related as follows :-
D. Other Characteristics of these THINGS include :-
E. Sample Data includes :-
E.1 To be determined
F. Typical Enquiries include :-
F.1 To be Determined