Geek Answers Handbook

Database design for website with text.

I am new to PHP and MySQL. For my project, I want to create a website for texts. How to create a database and relationships?

Here is what I still have:

Executor

  • 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 am wrong.

+4
source share
6 answers

I highly recommend WWWSQLDesigner for developing your database. The guiding principle that Brianreavis talked about is really worth listening to. Always use the correct spelling, use consistent grammar, capitalization, and underscore (_). You can also add several genres using the relationship table.

album_genre ( id int, album int, genre int )

For photos of an album or artist, I recommend that you save them in a folder with the identifiers associated with it. Note

  id = 14 artist = 42 title = Mask And Mirror year = 1994 thumbnail: /thumbnails/album-14.jpg
+7
source

  • Be aware that table names are singular or plural. My preferences are unique because when you are doing multi-tasking, you can refer to the column simply as " track.id " rather than " tracks.id ".

  • Make sure that all your tables and name fields are spelled correctly (i.e. "genre"); this is what pain change later.

  • Finally, I would not recommend prefixing the column names with their parent table name. It is just redundant.

painter

  • ID
  • name
  • bio
  • finger

album

  • ID
  • artist_id
  • genre_id
  • title
  • RELEASE_YEAR

genre

  • ID
  • name

track

  • ID
  • title
  • album_id
+13
source

Your design looks pretty good. Some additional tables you can add:

  • Playlist
  • Playlisttrack
  • Playedtrack

You can add additional fields to the track table. For instance:

  • trackSortOrder
  • trackYear
  • trackGenre
  • trackLength
  • userRating
  • Bitrate
  • Author
  • copyright
  • numberOfPlays
  • lastPlayedDate
  • dateAdded
+5
source

Important questions you should ask yourself when designing

  • What is my requirement!?! In your case, what information should my lyrics contain in lyrics? Should he tell me who actually wrote this song? When was this written? Who all sang this song, etc. Etc. So, first of all, you need to determine the scope! Your objects and database design will depend on this!
  • What are my entities?
  • What is the relationship between my main entities?

Your design can be quite complex and can work just fine for your requirement, but depending on how much complexity you are ready to handle (requirements area!), You may have to take care of things like:

  • The artist and the album really have a lot of different relationships . Many artists can work on one album, and, of course, one artist will have several albums. Your current design will handle this, but do you want genreId, title, release_year to be duplicated when several artists have worked together for the album? There is a trade-off between creating 1 more table and maintaining duplicate values. Your current design may be ideal for what you are doing, but just wanted to make sure you gave it a thought.
  • In the real world, several artists collaborate to write a song. Basically, songs are written by someone else and performed by someone else. You need to determine what the Contractor means to you . Is that the one who sang the song? Is it someone who wrote this song? Both artists? If I am looking for the author of a song that has not sung a single song, should it return results?
  • I do not see the table where you store the lyrics! But I think you already know that :)

I can see a few more things that may cause problems in the future, but, as I said, I do not know what the scale of your requirements is! :)

+3
source

You combine several different types of objects in several places - for example, it looks like you are trying to create a single rating table that applies to albums, artists and tracks. Take a close look at whether it might be easier to have three separate tables for three different types of ratings.

The same goes for comment . Also, in this table, your current structure (having one comment_id on an album, artist or track) seems to limit each type of object to one comment, which makes no sense.

For genre , type and thumb consider nesting these tables in a parent object. Does it make sense to have, for example, one row of the thumb that is shared between several artists, or would it be easier just for each artist to have a thumb path stored directly in it?

Finally, for all the relationships you have drawn, you need to determine the power of the relationship. For each of them, determine which table belongs to another, and how many rows in one table can exist for each row in another. For example, the relationship between album and track is a one-to-many relationship, as each album contains multiple tracks, but each track belongs to the same album. Use notations such as “ note notes ” to denote this information.

0
source

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

-4
source

More articles:


All Articles