Entity-Relationship Model

Entities

1. User - Represents any registered person in the system with personal information

• user_id (bigint)
• email (text, required, unique)
• username (text, required, unique)
• password (text, required)
• date_created (date, required)
• shipping_address (text, optional)
• telephone_number (text, optional)

2. Admin - A type of user responsible for managing products and performing modifications

• user_id* (bigint)
• type (enum, required)
• discount_percentage (numeric, required)

3. Consumer - A type of user who can purchase products and collect loyalty points

• user_id* (bigint)
• points_collected (bigint, required)

4. Wishlist - A collection of products that a user saves for future interest

• wishlist_id (bigint)

5. WishlistItem - A specific product added to a wishlist

• wishlist_id* (bigint)
• product_id* (bigint)
• added_at (date, required)

6. Product - A purchasable item with price, stock, and format

• product_id (bigint)
• format (enum, required)
• price (numeric, required)
• description (text, required)
• stock (bigint, required)

7. Release - A musical work that groups different product formats

• release_id (bigint)
• title (text, required)
• record_label (text, optional)
• genre (text, required)
• release_date (date, required)
• cover_photo (text, required)

8. Album - A type of release consisting of multiple songs

• release_id* (bigint)

9. Single - A type of release typically representing a single track

• release_id* (bigint)
• duration (text, required)

10. Artist - A creator or performer associated with releases and songs

• artist_id (bigint)
• artist_name(text, required)
• description(text, optional)
• photo (text, optional)

11. Song - An individual track that can belong to albums

• song_id (bigint)
• song_name(text, required)
• duration (text, required)

12. OrderItem - A specific product entry within an order

• order_id* (bigint)
• product_id* (bigint)
• price_at_purchase (numeric, required)
• quantity (bigint, required)

13. Order - A transaction made by a user containing purchased products

• order_id (bigint)
• payment_method (enum, required)
• purchase_date (date, required)
• points_earned (bigint, required)
• points_used (bigint, optional)
• status (enum, required)

14. Modification - An action performed by an admin that changes or creates one or more products

• modification_id (bigint)
• date_modified (date, required)
• type_of_modification(enum, required)
• discount (numeric, optional)

Relationships

1. contains (Album ↔ Song, M:N)

Each Album contains Songs, while the same Song can be in multiple albums.

2. has (Release ↔ Artist, M:N)

Each Artist has multiple Releases, and multiple Artists can be associated with one Release.

3. features (Song ↔ Artist, M:N)

Each Song can feature multiple Artists, and each Artist can be featured in multiple Songs.

4. version_of (Product ↔ Release, N:1)

Each Product represents a specific format of one Release, while a Release can have multiple Products.

5. refers (OrderItem ↔ Product, N:1)

Each OrderItem refers to one Product, while a Product can appear in multiple OrderItems.

6. contains (Order ↔ OrderItem, 1:N)

Each Order contains multiple OrderItems, while each OrderItem belongs to exactly one Order.

7. creates (User ↔ Order, 1:N)

Each User can create multiple Orders, while each Order is created by exactly one User.

8. modifies (Admin ↔ Modification, 1:N)

Each Admin can perform multiple Modifications, while each Modification is performed by exactly one Admin.

9. refers (Modification ↔ Product, M:N)

Each Modification can affect multiple Products, while each Product can be affected by multiple Modifications.

10. has (User ↔ Wishlist, 1:1)

Each User has exactly one Wishlist, and each Wishlist belongs to exactly one User.

11. has (Wishlist ↔ WishlistItem, 1:N)

Each Wishlist contains multiple WishlistItems, while each WishlistItem belongs to exactly one Wishlist.

12. refers (WishlistItem ↔ Product, N:1)

Each WishlistItem refers to one Product, while a Product can appear in multiple WishlistItems.

Entity-Relationship Model History

Version 1​

• Initial Version

Version 2​

• Removed number_of_tracks attribute from Album
• Removed purchase_history attribute from Consumer
• Changed contains relation Album ↔ Song from 1:N to M:N
• Changed creates relation User ↔ Order 1:1 to 1:N
• Changed refers relation WishlistItem ↔ Product from N:1 to N:M

Version 3​

• Implemented features relationship between Song ↔ Artist and Single ↔ Artist to replace the featured_artist multi-valued attribute.
• Removed create relationship between Admin ↔ Product and implemented a new entity Modifcation, with relationships perform and refers, to allow creation and modification of products by admins.
• Moved attributes from Consumer to User to allow Admins to be able to purchase Products for testing and recreational purposes.
• Added discount_percentage attribute to Admin, and points_used and points_earned to Order.