== Entity-Relationship Model

[[Image(ERModel_v03.png, align=center)]]

== 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

[attachment:ERModel_v01.png Version 1]
* Initial Version
[attachment:ERModel_v02.png Version 2]
* Removed number_of_tracks attribute from Album
* Removed purchase_history 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
[attachment:ERModel_v03.png 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 and made it overlap instead of disjoint, 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.