开发者

Store data for songs MySQL DB

开发者 https://www.devze.com 2022-12-25 16:07 出处:网络
I\'m storing a huge set of songs in a MySQL database. This is what I store in the \'songs\' table: CREATE TABLE `songs` (

I'm storing a huge set of songs in a MySQL database. This is what I store in the 'songs' table:

CREATE TABLE `songs` (
  `song_id` int(10) unsigned NOT NULL auto_increment,
  `song_artist` varchar(255) NOT NULL,
  `song_track` varchar(255) NOT NULL,
  `song_mix` varchar(255) NOT NULL,
  `song_title` text NOT NULL,
  `song_hash` varchar(40) NOT NULL,
  `song_addtime` int(10) unsigned NOT NULL,
  `song_source` text NOT NULL,
  `song_file` varchar(255) NOT NULL,
  PRIMARY KEY  (`song_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1857 DEFAULT CHARSET=latin1

Now I'd like to keep track of how many plays each song has, an开发者_运维百科d other song-specific data that relates to the song. I don't want to keep adding fields to the 'songs' table for this. How can I store song related data a more efficient way? What's the best practice here?


If it's a 1 to 1 relationship, you should add it to the songs table. Nothing wrong with adding more fields to the table when the relationship calls for that. You could then use SQL to increment the field on each play.

Another approach could be used if, say, you want to keep track of how many times a particular user has played the song. You could create a relationship table that has song_id, user_id, plays as fields (song_id and user_id would form a composite key).

Depending on what you mean by

song-specific data

will determine how the schema should be modified.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号