Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2010
    Posts
    3

    Over-generalization Crippling Design

    Hello,

    First time poster, long time lurker. I've been working on my media library (a database to store all information for the video, books, music, and video games in a collection) design for quite a while now, and my obsession trying to make everything as generic as possible is preventing me from making any progress. I was wondering if anyone could take a look at my ERD and give suggestions. I would really appreciate it.

    I'm trying to minimize data duplication as much as possible, but no matter what schema I come up with, I always run into it. Right now, I'm having issues with a media that spans multiple types (video, book, music, game). Each 'type' will have to be assigned a new instance in the media entity, but if I make the relationship between type and media m:m, I lose the relationship between each media instance and its format (DVD, CD, etc).

    Just one example of the many, many problems I've run into.

    Again, any help, input, suggestions, etc. would be very much appreciated.

    Oh, also, I haven't gotten to the point where I've implemented every entity in SQL, so some (or most) of the entities are probably incorrect.

    Experience: College classes and some work experience. Mostly just a hobby now.
    Attached Thumbnails Attached Thumbnails media_db.png  

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Including animals may have been a touch excessive. Just sayin.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    creating a surrogate key for a date is overkill -- just use the date

    and what's a "date type"?

    all in all there seem to be too may "type" tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Dec 2010
    Posts
    3
    I wanted to use to date type to hold the different kinds of dates that could be associated with a medium, like different release dates, or dates for people, like birth date, death date, marriage date, etc. Just trying to make it generic so it could be used for anything, actually, so it can be modified in the future.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think your date types are over-generalizing your design
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2010
    Posts
    3
    LOL, yeah, maybe just a little. I think I'll just re-evaluate what I actually want to do and start fresh. Thanks for your help!

  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by Teddy View Post
    Including animals may have been a touch excessive. Just sayin.
    come on, Teddy - they're ... party animals!!!


    Click image for larger version. 

Name:	Party Anmals.PNG 
Views:	69 
Size:	14.4 KB 
ID:	11422

    Last edited by loquin; 12-23-10 at 19:40.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  8. #8
    Join Date
    Apr 2010
    Location
    London, England
    Posts
    24

    Data Model for Media Libraries

    Hi
    I have drafted a Data Model on my Database Answers Web Site that might help you start thinking along the right lines :-
    Media Libraries Data Model

    Your draft is very complicated and it is not clear why that should be.
    For example, why should you have a separate table for Cities ?

    I recommend you think about creating a Top-Level Data Model.
    and then create Subject Area Models when you can find a way to justify them.
    Here is an example from Waste Management :-
    Waste Management Data Model

    Maybe you need a Subject Area Model for Addresses, like this one :-
    Customer Addresses Data Model

    And think about why every Entity needs to appear in either the Top-Level or Subject Area Model.

    In other words, try to think about how you will use each Entity.

    HTH

    Barry Williams
    Database Answers
    London, England

Posting Permissions

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