Page 1 of 1

storing lists in mysql

Posted: Wed Dec 23, 2009 3:01 pm
by daedalus__
hi. i have a table or two (albums, songs, etc) that will have a column that holds a list of artist id's.

now the way i am thinking about storing this list is by using serialized arrays. but what i want to know is if there is a more graceful way to handle this.

but i was also thinking i can create the list using mysql's string functions but then parsing it in php becomes more annoying.

i want to be able to index this column and i also want to be able to use string functions or regex to read id numbers from the list so someone can search for shtuff by artist.

im also wondering if i will be able to select the artist names and id's by using string functions and joins to get the information from the column.

work with things like this before?

Re: storing lists in mysql

Posted: Wed Dec 23, 2009 8:45 pm
by John Cartwright
You most certainly want to use a normalized database structure.. You never want to store more than one piece of data in a column.

To get you started, heres my proposed playlist and song database

Code: Select all

CREATE TABLE IF NOT EXISTS `playlists` (
  `playlist_id` bigint(12) NOT NULL auto_increment,
  `user_id` bigint(12) NOT NULL,
  `song_id` bigint(12) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY  (`playlist_id`),
  KEY `user_id` (`user_id`,`song_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
 
CREATE TABLE IF NOT EXISTS `songs` (
  `song_id` bigint(12) NOT NULL auto_increment,
  `artist_id` bigint(12) NOT NULL,
  `album_id` bigint(12) NOT NULL,
  `title` varchar(255) NOT NULL,
  `order` int(3) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY  (`song_id`),
  KEY `artist_id` (`artist_id`,`album_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Re: storing lists in mysql

Posted: Thu Dec 24, 2009 12:29 am
by daedalus__
oh no not playlists. im going to be storing information about albums and songs. so i need a graceful way to store multiple artists for a song or album. i could use multiple columns but that would limit the number of artists to the number of columns for artists. its important to be able to find music that an artist is featured in. i have a ton of mix tapes that are two to four artists and stuff.