Data model - yurkka23/iMusic_team GitHub Wiki

image

Categories:

  • Id: Unique identifier for each category.
  • Name: Name of the category (e.g., Rock, Pop, Classical).
  • CategoryImgUrl: URL to the image associated with the category.
  • CreatedTime: Timestamp indicating when the entity was created.

Albums:

  • Id: Unique identifier for each album.
  • Title: Title of the album.
  • CreatedTime: Timestamp indicating when the entity was created.
  • Status: The current status of the album (e.g., "Active," "Inactive," "Draft").
  • SingerId: A foreign key referencing the "Singers" entity. This field links the album to the singer(s) associated with it.
  • CategoryId: A foreign key referencing the "Categories" entity. This field links the album to the category(ies) it belongs to.
  • AlbumImgUrl: The URL of an image associated with the album.
  • CountRate: The number of times the album has been rated.

Songs:

  • Id: A unique identifier assigned to each song.
  • Title: The title of the song.
  • Text: The lyrics or text of the song.
  • SongUrl: The URL of the song file (e.g., MP3, WAV).
  • Duration: The duration of the song in seconds.
  • CountRate: The number of times the song has been played.
  • SongImgUrl: The URL of an image associated with the song, typically the song's cover art.
  • Status: The current status of the song (e.g., "Active," "Inactive," "Draft").
  • CreatedTime: A timestamp indicating when the song was created.
  • SingerId: A foreign key referencing the "Singers" entity. This field links the song to the singer(s) associated with it.
  • CategoryId: A foreign key referencing the "Categories" entity. This field links the song to the category it belongs to.
  • AlbumId: A foreign key referencing the "Albums" entity. This field links the song to the album it belongs to.

Playlists:

  • Id: A unique identifier assigned to each playlist.
  • Title: The title of the playlist.
  • PlaylistImgUrl: The URL of an image associated with the playlist, a cover image.
  • Status: The current status of the playlist (e.g., "Active," "Inactive," "Draft").
  • CreatedTime: A timestamp indicating when the playlist was created.
  • AuthorId: A foreign key referencing the "Users" entity. This field links the playlist to the user who created it.
  • CountRate: The number of times the playlist has been played.

Users:

  • Id: A unique identifier assigned to each user.
  • FirstName:тThe user's first name.
  • LastName:тThe user's last name.
  • AboutMe: A brief description or bio about the user.
  • UserImgUrl: The URL of an image associated with the user, a profile picture.
  • IsBanned: A boolean flag indicating whether the user's account is banned.
  • BannedTime: A timestamp indicating when the user's account was banned.
  • RegisterTime: A timestamp indicating when the user registered their account.
  • RefreshToken: A refresh token used for authentication and authorization.
  • RefreshTokenExpires: The expiration date of the refresh token.
  • FavoriteListId: A foreign key referencing the "FavoriteLists" entity. This field links the user to their favorite lists.
  • UserName: The username of the user.
  • NormalizedUserName: A normalized version of the username, often used for case-insensitive comparisons.
  • Email: The user's email address.
  • NormalizedEmail: A normalized version of the email address, often used for case-insensitive comparisons.
  • EmailConfirmed: A boolean flag indicating whether the user's email address has been confirmed.
  • PasswordHash: The hashed password of the user.
  • SecurityStamp: A security stamp used for authentication and authorization.
  • ConcurrencyStamp: A concurrency stamp used for optimistic concurrency control.
  • PhoneNumber: The user's phone number.
  • PhoneNumberConfirmed: A boolean flag indicating whether the user's phone number has been confirmed.
  • TwoFactorEnabled: A boolean flag indicating whether two-factor authentication is enabled for the user.
  • WantToBeSinger: A boolean flag indicating whether the user wants to be a singer on the platform.

UserAlbums:

UserId: Foreign key referencing the "Users" entity.* AlbumId: Foreign key referencing the "Albums" entity.* AddedDate: Date when the user added the album to their library.*

UserSongs:

  • UserId: Foreign key referencing the "Users" entity.
  • SongId: Foreign key referencing the "Songs" entity.
  • AddedDate: Date when the user added the song to their library.

SongPlaylists:

  • SongId: Foreign key referencing the "Songs" entity.
  • PlaylistId: Foreign key referencing the "Playlists" entity.
  • AddedDate: Date when the song was added to the playlist.

FavoriteAlbums:

  • UserId: Foreign key referencing the "Users" entity.
  • AlbumId: Foreign key referencing the "Albums" entity.

FavoriteSongs:

  • UserId: Foreign key referencing the "Users" entity.
  • SongId: Foreign key referencing the "Songs" entity.

FavoritePlaylists:

  • UserId: Foreign key referencing the "Users" entity.
  • PlaylistId: Foreign key referencing the "Playlists" entity.

Data Classification for a Data Retention Policy

User Data (AspNetUsers): This includes personally identifiable information (PII) such as names, email addresses, and user-generated content like profile pictures and bios. Content Data (Songs, Albums, Playlists): This encompasses the core content of the platform, including audio files, metadata, and user-generated playlists. System Data: Logs, configurations, and infrastructure data that is essential for maintaining and operating the system.

Data Classification and Retention Periods

User Data Personally Identifiable Information (PII):

  • Active users: Retain until the user deletes their account or as required by law.
  • Inactive users: Retain for a defined period (e.g., 1 year) after the last activity.
  • Deleted accounts: Maintain backups for a specific period for recovery purposes, then permanently delete.
  • User-generated content: Retain as long as the associated user account is active or as required by law.

Content Data Audio files, metadata, and playlists:

  • Active content: Retain until deleted by the user or administrator or until license agreements expire.
  • Deleted content: Maintain backups for a defined period, then permanently delete.

System Data

  • Logs: Retain for a defined period (6 months) for troubleshooting and analysis, then archive.
  • Configurations: Retain for the lifetime of the system or until changes are made.
  • Infrastructure data: Retain for the lifetime of the equipment or until it is replaced.

Recommendations

  • Regular review: Regularly review the data retention policy to ensure it aligns with changing business needs and legal requirements.
  • Secure deletion: Implement secure data deletion methods to prevent data recovery.
  • Automation: Automate data lifecycle management processes.