Mysql – Should I create new table, or add new columns to existing one for song lyrics

database-designMySQL

I'd like to ask for some advice in whether I should create a new table or just add some new columns to existing table?

Honestly I've been thinking about this whole day and I really don't know.

So basically here's the structure of song table.

+------------+-----------+------+-----+---------+----------------+
| Field      | Type      | Null | Key | Default | Extra          |
+------------+-----------+------+-----+---------+----------------+
| ID         | int(6)    | NO   | PRI | NULL    | auto_increment |
| Title      | char(100) | YES  |     | NULL    |                |
| Type       | char(20)  | YES  |     | NULL    |                |
| Singer     | char(50)  | YES  |     | NULL    |                |
| Youtube    | char(30)  | YES  |     | NULL    |                |
| Score      | double    | YES  |     | 0       |                |
| Ratings    | int(8)    | YES  |     | 0       |                |
| Favourites | int(7)    | YES  |     | 0       |                |
+------------+-----------+------+-----+---------+----------------+

In this I store information about songs obviously. And yesterday I thought I would add a feature on my site to check the lyrics of the available songs. I'm only planning to add the lyrics in 2 different languages, English and German for now.

That being said, I don't know if it would be better to just add some new columns like English_lyrics and German_lyrics to the song table, or I should create a new one, called lyrics, with these two columns and the ID of the song, SongID. This way I would just select the lyrics using INNER JOIN.

Best Answer

As per normalization, you should not have similar value attributes in multiple columns. In future, let's say, if you have to add 10 more languages later, your table will become huge. Moreover, adding new columns in a table with lots of data in a production environment is not so easy. Relational data should not be considered like a Flat Excel file. Searching for a combination of languages in lyrics will not be easy as well. Another issue could be that you will have lots of NULL values, because not all songs would have lyrics in all the languages. NULL is a special value, and handling it requires special care while formulating queries, and lots of common mistakes happen due to not accounting for the NULL.

Instead, you can create a new table, let's say, songs_lyrics. It will have three columns, viz., song_id (Foreign Key to ID in songs table), language_id (Foreign Key to ID in a language master table), lyrics (text containing the lyrics in the specific language). You will also have a composite Primary Key on (song_id, language_id)

CREATE TABLE songs_lyrics (
  song_id INT NOT NULL, 
  language_id INT NOT NULL, 
  lyrics TEXT, 
  PRIMARY KEY (song_id, language_id), 
  FOREIGN KEY (song_id) REFERENCES song(ID), 
  FOREIGN KEY (language_id) REFERENCES language(ID)
) engine= InnoDB;

You will also need to create the Master table for the languages:

CREATE TABLE language (
  ID INT NOT NULL AUTO_INCREMENT
  name VARCHAR(64) NOT NULL
  PRIMARY KEY (ID)
) engine=InnoDB;
Related Question