Looking for "best practices" type suggestion.

[Haitashi]

I have a database that currently contains different albums. The way I had the database was that there is a "descriptio n" field in which I placed all the songs. When I access my site I see in information of a CD and a paragraph that contains all the data I put in the "descriptio n" field. I am thinking of adding an "advanced search" feature to the site where the user can select Artist or Song Title. However, how can I ensure that it looks into each song individually?

My first instinct was to have each song be a record in the database that is tied to an album (maybe by that album's ID) I have about 5k albums in my database so I have my work cut our for me if I do that.

Any ideas/suggestions so that the user may search for songs?

Here's an example of an insert statement with data of a particular album that is currently in my database so you can get an idea of what I currently have:

INSERT INTO `products_description` (`products_id`, `language_id`, `products_name`, `products_description`, `products_url`, `products_viewed`) VALUES
(1, 1, 'BOBBY VALENTIN - THE BEST - CD', '1- HURACAN  \r\n2- VETE PA LLA  \r\n3- PIRATA DE LA MAR  \r\n4- BESO BORRACHO  \r\n5- GUARAMBEMBERE  \r\n6- POBRE SOY  \r\n7-SOY BORICUA  \r\n8- SON SON CHARARI  \r\n9- PIENSALO BIEN  \r\n10- PAPEL DE PAYASO  \r\n11- TOTAL PARA NADA', '', 21);

[coolsti]

Your current design does not make use of the powers of a relational database, so yes, you should definitely change the structure of your tables. You make another table that contains a row for each song title, and you make columns in that table that contain the attributes of the song titles that are specific to the song titles (and not general for the entire CD). You also have a foreign key in that table which at best will be an integral primary key of the main table containing your CD information.

Then in order to select all the information for a CD including all titles, you use a table join. And by using appropriate WHERE clauses, you also now have the ability to search for titles and also to order the results by title alphabetically.

As for the conversion of the database from one form to another, you can probably do this with some queries, for example while accessing your database via a mysql console window. But since it involves multiple inserts into the new sub table, I would do this by making a script for example in PHP that reads all the information from your current table and then in a loop inserts the appropriate data into the new table for the song titles. Then I would go into a mysql console window and drop the no longer needed song title column from the original table.

[Haitashi]

Gotcha. I definitely have some work ahead of me but what you said makes perfect sense. Thanks!