how to make lyrics site database

Question

I'm new to PHP and MySQL. For my project I want to make a site for lyrics. How to design the database and the relationships?

Here is what I have so far:

Artist

• Artist_id
• Artist_name
• Artist_bio
• Artist_thumb

Albums

• Album_id
• Artist_id
• Genre_id
• Album_title
• Release_year

Genre

• genre_id
• genre_name

Tracks

• track_id
• track_title
• album_id

Please let me know if I'm wrong

Thanks

Answer 1

How about having a separate table for album and a common table which defines the relationship between all the other tables?

Answer 2

1. Be consistent with whether your table names are singular or plural. My preference is singular, because then when you're doing multi-table queries, you can refer to a column simply as "track.id", rather than "tracks.id".

2. Ensure all your table and field names are spelled correctly (i.e. "genre"); this is something that's a pain to change later.

3. Finally, I wouldn't advise prefixing the column names with their parent table's name. It's just redundant.

---

artist

• id
• name
• bio
• thumb

album

• id
• artist_id
• genre_id
• title
• release_year

genre

• id
• name

track

• id
• title
• album_id

Answer 3

Your design looks pretty good. Some additional tables that you may want to add:

• Playlist
• PlaylistTrack
• PlayedTrack

You could add additional fields to the Track table. For example:

• trackSortOrder
• trackYear
• trackGenre
• trackLength
• userRating
• bitRate
• author
• copyright
• numberOfPlays
• lastPlayedDate
• dateAdded

Answer 4

I strongly recommend WWWSQLDesigner to design your database. Guideline that brianreavis had mentioned are really worthy of listening. Always use correct spelling, use consistent grammar, capitalization and underlining (_). Also you may consider adding multiple genres using a relationship table.

  album_genre ( id int, album int, genre int )

For album or artist pictures, I recommend you to save them to a folder with their related id's. Observe,

 id = 14
 artist = 42
 title = Mask And Mirror
 year = 1994

 thumbnail: /thumbnails/album-14.jpg

Answer 5

Important questions you should be asking yourself while designing

• What is my requirement!?! In your case, what all information should my lyrics website have? Should it tell me who actually wrote the song? When was it written? Who all have sung that song etc etc. So first thing is you have to define the scope! Your Entities and database design will depend on that!
• What are my entities?
• what are the relationships between my main entities?

Your design might be pretty descent and might work perfectly for your requirement but depending on how much of complexity you are willing to handle (requirement scope!), you might have to take care of things like:

• Artist and Album actually have many to many relationship. Many artists might work on same album and of course a single artist will have multiple albums. Your current design will cope up with this but do you want genreId, title, release_year being duplicated when multiple artists work together for an album? There is a trade off involved here between creating 1 more table and storing duplicate values. Your current design might be perfect for what you are doing, but just wanted to make sure that you have given it a thought
• In real world, multiple artists collaborate to write a song. Mostly songs are written by someone else and sung by someone else. You need to define what Artist means to you. Is it the person who sung the song? Is it someone who wrote the song? Are both artists? If I search for the writer of the song who has not sung a single song, should it return results?
• I dont see a table where you are storing lyrics! But I guess you already know that :)

I can see a few more things which might cause you problems later on, but as I said, I don't know what is the scope of your requirement! :)

Answer 6

I would use a framework that will enforce these relationships for you.

Answer 7

anyway i would seriously consider you guys take a look at :

http://www.findyourlyrics.com and see their sitemap and db structure.