Proper normalization for media with multiple types of owners

Databases
1

I am having a hard time deciding on the best way to approach this problem. I am trying to set up an app in rails where users and groups of users can both upload media to albums. The media can be either images or video. I have put together a simple example ERD to show what I have so far https://docs.google.com/file/d/0B8eur28E6_TdMk9Lc0hpb1psZzg/edit?usp=sharing.

  1. What would be the best way to normalize this further?
  2. Should I separate the video and images into separate tables all together and not link them to the media table?

Any suggestions are welcome.

2

Why do you have separate images and videos tables? Why not add a mediatype column to the media table?

3

for medai, videos, and images, do a search for supertype/subtype

the videos and images should not have their own PKs, but rather, their PKs should be FKs to the media table

4

Thank you, that’s what I was looking for. I love working with databases but I still have a lot to learn. Is there anyway to clean up the rest of the tables?

5

clean up???

6

Sorry that could have been phrased better :/. Is there any way to make the schema more efficient with fewer tables. I have a tendency to make things more complicated than i need to.

7

not really… it’s fine the way it is

you’ll notice that the structures for groups and users are similar, and you might wonder if you could collapse them and put both users and groups into the same table (with some kind of column to tell them apart) but i would recommend against that