SpringBoot+vue imitation Netease cloud music website - database design

1, Demand analysis

Imitate Netease cloud, then follow Netease cloud music if you need it.
First of all, you can listen to songs and view singers. Singers have corresponding albums. There are users. Users can create song lists and collect song lists. Song lists can add or delete songs. Users can also comment on songs, albums, song lists and fan functions. There is a rotation chart on the home page.

2, Detailed explanation of database design

According to the comprehensive demand, there are 11 tables below.
1) . song list
2) . singer list
3) . album list
4) . song list
5) . song list
6) 2. User table
7) . user favorite song table
8) . user favorite song list
9) . fan list
10) 1. Comment form
11) 2. Rotation chart

Each table is described in detail below

1. Song list

The sql is as follows

DROP TABLE IF EXISTS `song`;
CREATE TABLE `song` (
  `song_id` int(11) NOT NULL auto_increment COMMENT 'Primary key id',
  `song_name` varchar(255) NOT NULL COMMENT 'Song name',
  `song_singer` int(11) default NULL COMMENT 'singer Id',
  `song_filepath` varchar(100) default NULL COMMENT 'Song path',
  `song_album` int(11) default NULL COMMENT 'Album Id',
  `song_lyc` varchar(3000) default NULL, 'lyric'
  PRIMARY KEY  (`song_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Singer list

DROP TABLE IF EXISTS `singer`;
CREATE TABLE `singer` (
  `singer_id` int(11) NOT NULL auto_increment,
  `singer_name` varchar(20) default NULL, 'Singer name'
  `singer_details` varchar(255) default NULL, 'Singer profile'
  `singer_photo` varchar(255) default NULL, 'Singer Avatar'
  `singer_type` varchar(255) default NULL, 'Singer type'
  PRIMARY KEY  (`singer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The singer type is to realize the classification of singer pages, Chinese male singers, Chinese female singers... And other functions.

3. Album list

DROP TABLE IF EXISTS `album`;
CREATE TABLE `album` (
  `album_id` int(11) NOT NULL auto_increment, 
  `album_name` varchar(255) default NULL, 'Album name'
  `album_singer` int(11) default NULL, 'singer Id'
  `album_details` varchar(255) default NULL, 'Album introduction'
  `album_time` datetime default NULL, 'Album release time'
  `album_img` varchar(255) default NULL, 'Album cover'
  PRIMARY KEY  (`album_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Singers, songs and albums are one-to-one or one to many relationships, so there is no need to create a third table to correlate.

4. Song list

DROP TABLE IF EXISTS `lists`;
CREATE TABLE `lists` (
  `list_id` int(11) NOT NULL auto_increment,
  `list_title` varchar(255) default NULL, 'Song list title'
  `list_img` varchar(255) default NULL, 'Album cover'
  `list_userid` int(11) default NULL, 'Song list Creator Id'
  `list_time` datetime default NULL, 'Song list creation time'
  `list_details` varchar(255) default NULL, 'Introduction to song list'
  `list_type` int(11) default NULL, 'Song list type'
  `list_playnum` int(11) default NULL, 'Song list hits'
  PRIMARY KEY  (`list_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

5. Song list

Because a song can be in multiple song lists, and a song list can contain multiple songs, songs and song lists have a many to many relationship, and a third table needs to be created to associate them.

DROP TABLE IF EXISTS `songlist`;
CREATE TABLE `songlist` (
  `songlist_id` int(11) NOT NULL auto_increment,
  `songlist_listid` int(11) default NULL, 'song sheet Id'
  `songlist_songid` int(11) default NULL, 'song Id'
  PRIMARY KEY  (`songlist_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

6. User table

Mobile number is because I have realized mobile number binding and mobile number login.

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `user_id` int(11) NOT NULL auto_increment,
  `user_name` varchar(255) default NULL, 'user name'
  `account` varchar(255) default NULL, 'User account'
  `password` varchar(255) default NULL, 'User password'
  `user_phone` varchar(255) default NULL, 'Telephone'
  `user_area` varchar(255) default NULL, 'region'
  `user_details` varchar(255) default NULL, 'brief introduction'
  `user_gender` int(11) default NULL, 'Gender'
  `user_birth` date default NULL, 'birthday'
  `user_photo` varchar(255) default NULL, 'User Avatar'
  PRIMARY KEY  (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

7. User favorite songs table

Similarly, users can collect multiple songs, and songs can also be collected by multiple users. Therefore, it is a many to many relationship, and a third table association needs to be established.

DROP TABLE IF EXISTS `usersong`;
CREATE TABLE `usersong` (
  `usersong_id` int(11) NOT NULL auto_increment,
  `usersong_userid` int(11) default NULL, 'user id'
  `usersong_songid` int(11) default NULL, 'song id'
  PRIMARY KEY  (`usersong_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

8. User favorite song list

Similarly, users can collect multiple song lists, and song lists can also be collected by multiple users. Therefore, it is a many to many relationship, and a third table association needs to be established.

DROP TABLE IF EXISTS `userlist`;
CREATE TABLE `userlist` (
  `userlist_id` int(11) NOT NULL auto_increment COMMENT 'User favorite song list',
  `userlist_userid` int(11) default NULL, 'user id'
  `userlist_listid` int(11) default NULL, 'song sheet id'
  PRIMARY KEY  (`userlist_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

9. Fan concern form

Users may pay attention to singers and users, and fans may only have users.

CREATE TABLE `fans` (
  `fans_id` int(11) NOT NULL auto_increment,
  `fans_type` int(11) default NULL, 'Type to distinguish whether the focus is on singers or users'
  `fans_focusid` int(11) default NULL, 'Followers id,It may be a singer or a user'
  `fans_fansid` int(11) default NULL, 'Followers id'
  PRIMARY KEY  (`fans_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

10. Comment form

Use a type field to distinguish whether it is a song list, album or song.

CREATE TABLE `comments` (
  `comm_id` int(11) NOT NULL auto_increment,
  `comm_details` varchar(255) default NULL, 'Comment content'
  `comm_time` datetime default NULL, 'Comment time'
  `comm_userid` int(11) default NULL, 'user Id'
  `comm_type` int(11) default NULL, 'Comment type 1 song list 2 album 3 songs'
  `comm_targetid` int(11) default NULL, 'target id,It could be a song list id,Album id,song id'
  PRIMARY KEY  (`comm_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

11. Rotation chart

The home page usually plays three or four pieces in turn. Generally, the expired ones can be invalidated rather than deleted directly.

CREATE TABLE `slide` (
  `slide_id` int(11) NOT NULL auto_increment,
  `slide_img` varchar(255) default NULL, 'Roulette path'
  `slide_albumid` int(11) default NULL, 'Album id'
  `slide_valid` int(11) default NULL, 'Is it valid'
  PRIMARY KEY  (`slide_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Tags: MySQL Mybatis Spring Boot

Posted on Wed, 01 Dec 2021 04:55:32 -0500 by xgrewellx