Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2014
    Posts
    48

    Unanswered: "Peer Review" Of Database Design

    Would anyone care to assist me in a "Peer Review" of my database design? I would be interested in any feedback on my design before I proceed to the next step in my development process, which is to upsize my database to SQL Server and begin development of the Data Access Layer using VB.Net.

    There are two areas in particular that I would appreciate feedback on, although other comments would be wecome too.

    1. Total Time Field in tblMedia is the duration of a given MP3 file in miliseconds. The field type is numeric, long integer. My concern is that miliseconds is not a value that we easily relate to. We tend to want to see song durations shown as hours, minutes, and seconds. I say hours because there are some audiobook files that are an hour plus in length. Should I store this value as miliseconds or as HH:MMS?

    2. I have some concerns about the relationships I've defined between three of my tables and if they accurately reflect what I'm trying to achieve.

    The first table is tblMediaTypes. This table is used to define the types of media that will be stored in the database. Examples would be Music, Audiobooks, Movies, TV Shows and so on.

    The second table is tblGenreTypes. This table is used to contain general Genre information and is related to Media Type in that when a user selects a Media Type the list of Genre Types they see will be specific to that Media Type. Such as Fiction or Non Fiction for Audiobooks, or Rock, Jazz or Country for Music.

    The thrid table is tblGenre and is related to tblGenreTypes such that the user's selection of a Genre Type will be used to provide a list of even more specific values based on the Media Type and Genre Type. Such as Sci-Fi or Fantasy for Movies and Hard or Classic Rock for Music.

    I'm concerned that I may have defined my relationships between these tables incorrectly and I would appreciate a second set of eyes to review my work in this area as well as the overall database.

    Thanks In Advance

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    I would used a date/time field and store: hh:mm:ss (everyone knows this)

    Dont bother building 'related' tables unless they are actual parent/child tables. Like Client vs ClientOrder.
    The mediaType vs genre really dont need it. It just makes problems for changes. ...these can be set at entry.

  3. #3
    Join Date
    Sep 2014
    Posts
    48
    Thank you for your thoughts. As for the "related" tables, yes they are parent/child. A given Media Type has specific Genre Types and Genre. Since my database stores information about media types other than just music then Media Type is necessary. The final goal is to create an application like iTunes but hopefully better quality than iTunes.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    genres

    Id be wary of the genres values. there is a defacto a standard for (IIRC) the first 128 values of 255. but in my experience evryones idea of 'standards' is somewhat loose. the genre encoding on some of my data is very,very suspect you may need to have soem form of internal and external translation (eg they know its as 24, whereas in my system its 120. there is a mismatch in genre terms between different countries, and albums identified in once country as type x, might in another country be type XY.

    whether you store the running time as seconds, milliseconds or hours minutes seconds is largely irrelevent. you can get to hours,minutes and seconds easily enough from milliseconds (eg format(mycolumn/(3660*1000),"hh:MMS"). this is a case where storing what seems to be numeric data could / should be stored as something else (except Access/JET stores date time information as numeric in the background, it fakes the hh:mm:ss as required for presentation.

    personally I wouldn't store as milliseconds but a either seconds or a date time value
    storing as a date time value seems attractive, and would be OK providing you don't try and do any aggregation or summing of the data. it will work OK providing you don't have more than 31 days accumulated whatever is any one group/summation. you'd be OK up to 31 days as in 31 days in January, but your report would look 'odd' if there were more than 744 hours (31*24). So I'd probably store as seconds. for me storing milliseconds in a media library would be fake precision, but your application may need that precision

    always bear in mind that how you store the data isn't neccesarily how you consume or present the data, but you must be able to use the data for what ever purposes that are required.

    as to relationships
    personally I find it easier to see a diagram rather than read text
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2014
    Posts
    48
    These are my own values but I do understand your point. My tables provide a fairly large base of values to choose from and I plan to provide the user the ability to add their own. My goal was to try and add some uniformity in this area.

    I agree with the issue about miliseconds, that's now iTunes stores it. I already know that I will have to convert it to hh:mm:ss and I had planned to convert it unless someone presented a good reason why it should remain stored as miliseconds.

    I would be happy to provide a Visio diagram if that would be helpful.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I wouldn't store it as hh:MMS, if by storign as that you store as a string. store it as either number of seconds OR a date time value and format as required
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2007
    Location
    Lost in Translation
    Posts
    946
    Quote Originally Posted by healdem View Post
    I wouldn't store it as hh:MMS, if by storign as that you store as a string. store it as either number of seconds OR a date time value and format as required
    I agree with healdem that you shouldn't store it as a string, only because it makes it that much harder to translate in reporting tools. Also, I would maintain the millisecond granularity if only to further hone down searches for users. Say you are searching for a specific song and there are three songs with the same title and artist, one live, one album and one you nipped from youtube, having the milliseconds on there can define the song down further so you can easily grab the right one. Also, it can be easier to detect if you have exact duplicates. When you have a huge collection of 140k+ songs and some of them are moved or renamed then it becomes a real bear to manage and the more qualifiers you have for something, the better off you will be.
    Last edited by Liebling; 11-04-14 at 13:50.
    "Passion rebuilds the world for the youth. It makes all things alive and significant. ~Ralph Waldo Emerson

  8. #8
    Join Date
    Sep 2014
    Posts
    48
    Thank you all for your thoughts. They've been most helpful. No, I was not planning to store the MP3 Duration as a string because I already knew I would be using it in multiple ways that would require calculations or conversions. I wanted to hear your thoughts about which time format would be most logical to store the value.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think you've answered it for yourself. You are talking of the time format, not storage.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Sep 2014
    Posts
    48
    I still like to hear the thoughts of others. I may appear stubborn but I do listen and their thoughts and ideas give me food for thought. Thank You

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no it s not a problem

    I was just trying to point out you are referring to time format, not the inherent underlying storage of that information. and to be honest that underlying storage doesn't matter a huge amount. Liebling makes a good point that by storing as milliseconds then idnetifying identical tracks / files should be easier.

    as said before you can get to HH:MMS easily if you store the elapsed time as numeric. it doesn't matter if you store as as:-
    in an integer/long integer column as milliseconds or seconds
    in a datetime column as the the number of seconds expressed as the proportion of the day (1 day is comprised of 60 * 60 * 24 seconds or 86,400), in the Access / JET world thats 1/86400 represents 1 second in a datetime column
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •