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.
Any suggestions are welcome.
Why do you have separate images and videos tables? Why not add a mediatype column to the media table?
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
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?
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.
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