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.
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.
Including animals may have been a touch excessive. Just sayin.
come on, Teddy - they're ... party animals!!!
Last edited by loquin; 12-23-10 at 18:40.
"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
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