CODEWITHSUNDEEP
mysqldatabasedatabase-design
Throdne
Throdne

Reputation: 619

Storing Lyrics in a MySQL database

I wanted to know what is the best way of storing music "Lyrics" in a mysql database. and what are the setting used for that. Also, i would like the table to store the break lines.

I was thinking about using "|" to indicate a new line and using php to replace string.

I have no idea where to start, or what to use for the parameters (varchar, int (i know it's not going to be int), etc.)

Currently, I have my tracks table laid out like this

table tracks
----
id (int, 10, primary key)
albumid (foreign key to albums table)
name (varchar, 50)
override_artist (overrides album artist if not null)
playtime (int, 5)
lyric (??????)

Any ideas would be awesome.

Upvotes: 3

Views: 2787

Answers (2)

glglgl
glglgl

Reputation: 91017

I would choose a TEXT column and store the newlines just as they are - this is perfectly valid in a MySQL table.

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270609

Your lyric column should be the TEXT type, and you should probably store it in a separate MyISAM table keyed to the tracks table if your tracks table uses the InnoDB engine (which new installs do by default). This will enable you to do full text searches over the TEXT column:

Table lyrics:

CREATE TABLE lyric (
  lyricid INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  /* foreign key to tracks.id */
  trackid INT,
  lyric TEXT,
  FOREIGN KEY (trackid) REFERENCES tracks (id)
) ENGINE=MyISAM;

You can simply store the linebreaks in the lyrics as plain old line breaks. You don't need to replace them in the database. Instead, in your output to HTML, you would convert them to <br /> (in PHP, that's done with nl2br()). In other words, if you enter text into the database that has linebreaks, don't treat them specially at all. They'll work fine.

Upvotes: 5

Related Questions