Subject : Over Generalization of Database Design :-

Hello,

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.

Subject: How do I structure a Product Catalogue for paint with different colours and sizes, AND track stock levels?

Question: If it helps you, I posted my question here: http://forums.mysql.com/read.php?125,397571,397571#msg-397571
But I thought I'd kindly ask for your help in the absence of a response :-)

I am doing an e-commerce paint shop myself from the ground up, but have run into a brick wall with the database design!
There will be paints in different sized cans (400ml, 600ml and 1000ml) in the catalogue.

Each size has it's own unique set of available colours, but there is some cross-over of available colours.

I could have just one product catalogue table and store each paint can as a physical entity, but then it will
not be in normal 2nd normal form, because the "can_size" and "colour" columns would have repeating, identical values!

But then at least I could have a column for "stock_level".

However, if I have a separate "colours" table, yes I could reference the colour_id from the products table (via an associative table),
but how on earth would i be able to store info on stock levels for each can size+colour?

There are also going to be accessories for sale in the catalogue, e.g. brushes, which will not have the same product attributes as the paints!

Another problem. What suggestion do you have for storing the product information?

Thanks so much in advance.

Please feel free to request any feedback about your site too :)

Bye! Alec Pritchard

Barry has identified the 'Things of Interest' as follows :-
1. Can Sizes
2. Paint Colours
3. Others to be determined