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?
我是PHP和MySQL的新手。对于我的项目，我想为歌词创建一个网站。如何设计数据库和关系？
Here is what I have so far:
这是我到目前为止：
Artist
艺术家
- Artist_id
- Artist_id
- Artist_name
- 这位演出
- Artist_bio
- Artist_bio
- Artist_thumb
- Artist_thumb
Albums
相册
- Album_id
- Album_id
- Artist_id
- Artist_id
- Genre_id
- Genre_id
- Album_title
- ALBUM_TITLE
- Release_year
- RELEASE_YEAR
Genre
类型
- genre_id
- genre_id
- genre_name
- GENRE_NAME
Tracks
曲目
- track_id
- track_id
- track_title
- TRACK_TITLE
- album_id
- album_id
Please let me know if I'm wrong.
如果我错了，请告诉我。
6 个解决方案
#1
7
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.
我强烈建议使用WWWSQLDesigner来设计数据库。 brianreavis提到的指南真的值得倾听。始终使用正确的拼写，使用一致的语法，大小写和下划线（_）。您也可以考虑使用关系表添加多个类型。
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的文件夹中。观察，
id = 14
artist = 42
title = Mask And Mirror
year = 1994
thumbnail: /thumbnails/album-14.jpg
#2
13
-
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".与您的表名是单数还是复数一致。我的偏好是单数，因为当你进行多表查询时，你可以简单地将一个列称为“track.id”，而不是“tracks.id”。
-
Ensure all your table and field names are spelled correctly (i.e. "genre"); this is something that's a pain to change later.
确保所有表格和字段名称拼写正确（即“流派”）;这是后来改变的痛苦。
-
Finally, I wouldn't advise prefixing the column names with their parent table's name. It's just redundant.
最后，我不建议使用父表名称为列名添加前缀。这只是多余的。
artist
艺术家
- id
- ID
- name
- 名称
- bio
- 生物
- thumb
- 拇指
album
专辑
- id
- ID
- artist_id
- artist_id
- genre_id
- genre_id
- title
- 标题
- release_year
- RELEASE_YEAR
genre
类型
- id
- ID
- name
- 名称
track
跟踪
- id
- ID
- title
- 标题
- album_id
- album_id
#3
5
Your design looks pretty good. Some additional tables that you may want to add:
你的设计看起来很不错。您可能要添加的一些其他表：
- Playlist
- 播放列表
- PlaylistTrack
- PlaylistTrack
- PlayedTrack
- PlayedTrack
You could add additional fields to the Track table. For example:
您可以向Track表中添加其他字段。例如：
- trackSortOrder
- trackSortOrder
- trackYear
- trackYear
- trackGenre
- trackGenre
- trackLength
- 轨道长度
- userRating
- 用户评分
- bitRate
- 比特率
- author
- 作者
- copyright
- 版权
- numberOfPlays
- numberOfPlays
- lastPlayedDate
- lastPlayedDate
- dateAdded
- 添加日期
#4
3
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
- 艺术家和专辑实际上有很多关系。许多艺术家可能会在相同的专辑上工作，当然一个艺术家会有多张专辑。您当前的设计将会解决这个问题，但是当多位艺术家合作制作专辑时，您是否希望复制genreId，title，release_year？在创建另外一个表和存储重复值之间需要权衡。您当前的设计可能非常适合您的工作，但只是想确保您已经考虑过了
- 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! :)
我可以看到一些可能会在以后引起问题的事情，但正如我所说，我不知道你的要求范围是多少！ :)
#5
0
You're conflating multiple different types of objects in a few places -- for instance, it looks like you're trying to create a single rating table which applies to albums, artists, and tracks. Consider carefully whether it may be easier to have three separate tables for the three different types of ratings.
您在几个地方混淆了多种不同类型的对象 - 例如，您似乎正在尝试创建适用于专辑，艺术家和曲目的单个评级表。仔细考虑是否可以更容易为三种不同类型的评级分别设置三个表。
Same thing goes for comment. Additionally, on that table, your current structure (having a single comment_id on an album, artist, or track) would appear to limit each type of object to having a single comment, which doesn't make sense.
同样值得评论。此外，在该表上，您当前的结构（在专辑，艺术家或曲目上具有单个comment_id）似乎将每种类型的对象限制为具有单个注释，这没有意义。
For genre, type, and thumb, consider inlining those tables into the parent object. Would it ever make sense to have a single thumb row which is shared between multiple artists, for instance, or would it be easier to just have each artist have a thumb path stored directly in it?
对于流派，类型和拇指，请考虑将这些表内联到父对象中。例如，在多个艺术家之间共享一个拇指行是否有意义，或者让每个艺术家直接存储拇指路径会更容易吗？
Finally, for all of the relationships you've drawn out, you need to define the cardinality of the relationship. For each one, define which table is referring to the other, and "how many" rows in one table can exist for each row in the other one. For instance, the relationship between album and track is a one-to-many relationship, as each album contains multiple tracks, but each track belongs to one album. Use a notation such as "crow's foot notation" to denote this information.
最后，对于你所绘制的所有关系，你需要定义关系的基数。对于每一个，定义哪个表引用另一个表，并且一个表中的“多少”行可以存在于另一个表中的每一行中。例如，专辑和曲目之间的关系是一对多关系，因为每个专辑包含多个曲目，但每个曲目属于一个专辑。使用诸如“乌鸦脚符号”之类的表示法来表示此信息。
#6
-4
How about having a separate table for album and a common table which defines the relationship between all the other tables?
如何为专辑和一个公共表创建一个单独的表来定义所有其他表之间的关系？
#1
7
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.
我强烈建议使用WWWSQLDesigner来设计数据库。 brianreavis提到的指南真的值得倾听。始终使用正确的拼写，使用一致的语法，大小写和下划线（_）。您也可以考虑使用关系表添加多个类型。
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的文件夹中。观察，
id = 14
artist = 42
title = Mask And Mirror
year = 1994
thumbnail: /thumbnails/album-14.jpg
#2
13
-
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".与您的表名是单数还是复数一致。我的偏好是单数，因为当你进行多表查询时，你可以简单地将一个列称为“track.id”，而不是“tracks.id”。
-
Ensure all your table and field names are spelled correctly (i.e. "genre"); this is something that's a pain to change later.
确保所有表格和字段名称拼写正确（即“流派”）;这是后来改变的痛苦。
-
Finally, I wouldn't advise prefixing the column names with their parent table's name. It's just redundant.
最后，我不建议使用父表名称为列名添加前缀。这只是多余的。
artist
艺术家
- id
- ID
- name
- 名称
- bio
- 生物
- thumb
- 拇指
album
专辑
- id
- ID
- artist_id
- artist_id
- genre_id
- genre_id
- title
- 标题
- release_year
- RELEASE_YEAR
genre
类型
- id
- ID
- name
- 名称
track
跟踪
- id
- ID
- title
- 标题
- album_id
- album_id
#3
5
Your design looks pretty good. Some additional tables that you may want to add:
你的设计看起来很不错。您可能要添加的一些其他表：
- Playlist
- 播放列表
- PlaylistTrack
- PlaylistTrack
- PlayedTrack
- PlayedTrack
You could add additional fields to the Track table. For example:
您可以向Track表中添加其他字段。例如：
- trackSortOrder
- trackSortOrder
- trackYear
- trackYear
- trackGenre
- trackGenre
- trackLength
- 轨道长度
- userRating
- 用户评分
- bitRate
- 比特率
- author
- 作者
- copyright
- 版权
- numberOfPlays
- numberOfPlays
- lastPlayedDate
- lastPlayedDate
- dateAdded
- 添加日期
#4
3
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
- 艺术家和专辑实际上有很多关系。许多艺术家可能会在相同的专辑上工作，当然一个艺术家会有多张专辑。您当前的设计将会解决这个问题，但是当多位艺术家合作制作专辑时，您是否希望复制genreId，title，release_year？在创建另外一个表和存储重复值之间需要权衡。您当前的设计可能非常适合您的工作，但只是想确保您已经考虑过了
- 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! :)
我可以看到一些可能会在以后引起问题的事情，但正如我所说，我不知道你的要求范围是多少！ :)
#5
0
You're conflating multiple different types of objects in a few places -- for instance, it looks like you're trying to create a single rating table which applies to albums, artists, and tracks. Consider carefully whether it may be easier to have three separate tables for the three different types of ratings.
您在几个地方混淆了多种不同类型的对象 - 例如，您似乎正在尝试创建适用于专辑，艺术家和曲目的单个评级表。仔细考虑是否可以更容易为三种不同类型的评级分别设置三个表。
Same thing goes for comment. Additionally, on that table, your current structure (having a single comment_id on an album, artist, or track) would appear to limit each type of object to having a single comment, which doesn't make sense.
同样值得评论。此外，在该表上，您当前的结构（在专辑，艺术家或曲目上具有单个comment_id）似乎将每种类型的对象限制为具有单个注释，这没有意义。
For genre, type, and thumb, consider inlining those tables into the parent object. Would it ever make sense to have a single thumb row which is shared between multiple artists, for instance, or would it be easier to just have each artist have a thumb path stored directly in it?
对于流派，类型和拇指，请考虑将这些表内联到父对象中。例如，在多个艺术家之间共享一个拇指行是否有意义，或者让每个艺术家直接存储拇指路径会更容易吗？
Finally, for all of the relationships you've drawn out, you need to define the cardinality of the relationship. For each one, define which table is referring to the other, and "how many" rows in one table can exist for each row in the other one. For instance, the relationship between album and track is a one-to-many relationship, as each album contains multiple tracks, but each track belongs to one album. Use a notation such as "crow's foot notation" to denote this information.
最后，对于你所绘制的所有关系，你需要定义关系的基数。对于每一个，定义哪个表引用另一个表，并且一个表中的“多少”行可以存在于另一个表中的每一行中。例如，专辑和曲目之间的关系是一对多关系，因为每个专辑包含多个曲目，但每个曲目属于一个专辑。使用诸如“乌鸦脚符号”之类的表示法来表示此信息。
#6
-4
How about having a separate table for album and a common table which defines the relationship between all the other tables?
如何为专辑和一个公共表创建一个单独的表来定义所有其他表之间的关系？