UseCase009 Implementation - Modification of a Product

Initiating actor: Logged-In Admin

The goal of this use case is to allow an administrator to update the details of an existing physical product in the inventory. This ensures that information such as current pricing, stock availability, and format descriptions remain accurate, reflecting real-time changes in the store's supply and business requirements.

Scenario

1. Admin selects an existing Product from the inventory management interface that requires modification.

2. System retrieves the selected Product together with its associated Release information. The retrieved Product data includes its current format, price, description, stock quantity, and release_id.

SELECT
p.product_id,
p.format,
p.price,
p.product_description,
p.release_id,
p.stock,
r.release_id,
r.cover_photo,
r.genre,
r.record_label,
r.release_date,
r.title
FROM project.products AS p
INNER JOIN project.releases AS r
ON p.release_id = r.release_id
WHERE p.product_id = @__model_ProductId_0
LIMIT 1;

3. Admin enters the new values for the Product attributes that need to be modified.

4. After the submitted values are accepted by the application, the system updates the corresponding Product record. The Product's format, price, description, associated release_id, and stock quantity can all be changed.

UPDATE project.products
SET
format = @p0,
price = @p1,
product_description = @p2,
release_id = @p3,
stock = @p4
WHERE product_id = @p5;

5. After updating the Product, the system retrieves the highest existing modification_id. This value is used by the application when determining the identifier for the new Modification record.

SELECT max(m.modification_id)
FROM project.modifications AS m;

6. System creates a new Modification record containing the administrator responsible for the change, the modification date, modification type, and any associated discount value. This records the administrative action performed on the Product.

INSERT INTO project.modifications
(modification_id, admin_id, date_modified, discount, type_of_modification)
VALUES
(@p0, @p1, @p2, @p3, @p4);

7. System creates an entry in the Modification_Products table linking the newly created Modification to the Product that was updated.

INSERT INTO project.modification_products
(modification_id, product_id)
VALUES
(@p0, @p1);

8. System retrieves the available Products together with their associated Release information. The results are ordered by Release title and Product format so that the inventory interface reflects the updated Product information.

SELECT
p.product_id,
p.format,
p.price,
p.product_description,
p.release_id,
p.stock,
r.release_id,
r.cover_photo,
r.genre,
r.record_label,
r.release_date,
r.title
FROM project.products AS p
INNER JOIN project.releases AS r
ON p.release_id = r.release_id
ORDER BY
r.title,
p.format;