Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

this is a really scary picture

Answer 5

..?