SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2011
    Posts
    70
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Proper normalization for media with multiple types of owners

    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/0B8eu...it?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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Why do you have separate images and videos tables? Why not add a mediatype column to the media table?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Mar 2011
    Posts
    70
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by JeremyC View Post
    Is there anyway to clean up the rest of the tables?
    clean up???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Mar 2011
    Posts
    70
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by JeremyC View Post
    Is there any way to make the schema more efficient with fewer tables.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •