storing lists in mysql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
daedalus__
DevNet Resident
Posts: 1925
Joined: Thu Feb 09, 2006 4:52 pm

storing lists in mysql

Post 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?
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: storing lists in mysql

Post 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 ;
User avatar
daedalus__
DevNet Resident
Posts: 1925
Joined: Thu Feb 09, 2006 4:52 pm

Re: storing lists in mysql

Post 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.
Post Reply