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.

Including animals may have been a touch excessive. Just sayin.

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

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.

i think your date types are over-generalizing your design

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!

come on, Teddy - they're ... party animals!!!

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