Here's the question you clicked on:

55 members online
  • 0 replying
  • 0 viewing

heisenberg

  • 4 years ago

best way to create a database schema for songs/tags?

  • This Question is Closed
  1. heisenberg
    • 4 years ago
    Best Response
    You've already chosen the best response.
    Medals 0

    info: I have a table full of songs full of data (artist, songname, link, etc). I want to set up a [very POWRful] relationship with tags. In the end, I want to search by tag and return a playlist of relevant songs. here is what I was thinking: (rough pseudo code): TABLE songs( id int(16) primary index, ... ) TABLE tags ( id int(16) primary index, ... ) TABLE taggedAs ( songId int(16), tagId int(16), songId foreign key songs(id), tags foreign key tags(id) ) however these seems sucky because whenever i want to tag a new song, i need to operate on each table if I could use something like mongodb (such goodness), i would just store an array of tagnames on the songs. also, it'd be nice to have related tags, but I don't know if that is possible with this design.

  2. shadowfiend
    • 4 years ago
    Best Response
    You've already chosen the best response.
    Medals 0

    Hmm... Yes, it's true that you'll have to basically insert into tags if the relevant tag doesn't exist, etc. I believe you could also drop the tags table and make taggedAs have a songId and a tag. List of tags for a given song: SELECT DISTINCT tag FROM taggedAs WHERE songId = 15 List of existing tags: SELET DISTINCT tag FROM taggedAs List of songs with a given tag: SELECT songs.* FROM taggedAs JOIN songs ON songs.id = songId But, honestly, when doing most lookup operations (which are typically far more frequent then write operations for tagging), I believe your table layout with some JOINs will be more performant.

  3. lando
    • 4 years ago
    Best Response
    You've already chosen the best response.
    Medals 1

    Probably also want to create a table which lists tags and which tags they relate to. It all depends on how you want to develop the project. Start by laying out all your data for a couple of related songs, look for where you can reduce duplication and go from there. Use the normalization levels as a guide.

  4. Sammy11
    • 4 years ago
    Best Response
    You've already chosen the best response.
    Medals 0

    this is a really scary picture

  5. shadowfiend
    • 4 years ago
    Best Response
    You've already chosen the best response.
    Medals 0

    ..?

  6. Not the answer you are looking for?
    Search for more explanations.

    • Attachments:

Ask your own question

Sign Up
Find more explanations on OpenStudy
Privacy Policy