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?
storing lists in mysql
Moderator: General Moderators
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: storing lists in mysql
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
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 ;- daedalus__
- DevNet Resident
- Posts: 1925
- Joined: Thu Feb 09, 2006 4:52 pm
Re: storing lists in mysql
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.