创建数据库。需要设计帮助

I am practicing python flask and I am going to make a very simple music site and am now creating the database.

我正在练习python flask,我将创建一个非常简单的音乐网站,现在正在创建数据库。

I am new to databases so I just wanted help to see if the tables and relations are correct.

我是数据库的新手,所以我只是想帮助看看表和关系是否正确。

Also would I store multiple song ID's in the playlist_songs songID column?

我还会在playlist_songs songID列中存储多个歌曲ID吗?

userTable
    userID (PK), username, email, password, level
songTable
    songID (PK), songName, songArtist, songGenre, songDuration
playlistTable
    playlistID (PK), userID (FK), playlistName, playlistDescription
playlist_songs
    playlistID (FK), songID (FK)

1 个解决方案

#1

As requested, I'm adding some collective info based on your question and comments.

根据要求,我根据您的问题和评论添加一些集体信息。

Your design looks fine. As recommended by Rowland, it could perhaps use an order column. Something to order by. If you choose not to add this the songs will be returned in a somewhat random order for the playlist, or you could order by the SongId column and be guaranteed the same order at least (within a playlist). But it wouldn't be changeable.

你的设计很好看。根据Rowland的建议,它可能使用订单栏。要订购的东西。如果您选择不添加此歌曲,则会以播放列表的随机顺序返回歌曲,或者您可以通过SongId列进行排序并至少保证相同的顺序(在播放列表中)。但它不会改变。

You asked how data was entered in to the playlist_songs table:

您询问了如何将数据输入到playlist_songs表中:

SongTable
SongId | SongName       | ...
-----------------------------
1      | Happy Bithday  | ...
2      | Last Christmas | ...
3      | Christmas tree | ...
4      | Some song      | ...

PlaylistTable
PlaylistId | PlaylistName       | ...
-------------------------------------
1          | My Birthday songs  | ...
2          | My Christmas songs | ...
3          | All my songs       | ...

Playlist_songs
PlaylistId (FK) | SongId (FK)
-----------------------------
1               | 1
2               | 2
2               | 3
3               | 1
3               | 2
3               | 3
3               | 4

As you can see the Playlist_songs table can contain many playlists and many songs. If you query Playlist_songs for PlaylistId = 2 it will return SongId 2 & 3, and so on.

如您所见,Playlist_songs表可以包含许多播放列表和许多歌曲。如果您查询Playlist_songs for PlaylistId = 2,它将返回SongId 2和3,依此类推。

Currently, a primary key would have to be a constraint on the two columns (a compound key). This is also where you could add an Order column, or just add a stand alone primary key (Id for example) and order by that.

目前,主键必须是两列(复合键)的约束。这也是您可以添加Order列的位置,或者只是添加一个独立的主键(例如Id)并按此顺序排序。

#1