Bug #69728 sakila Sample Database has loops which would allow inconsistent data
Submitted: 12 Jul 2013 0:04 Modified: 3 Oct 2019 16:03
Reporter: Thomas Turrell-Croft Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:Version 0.8 OS:Any
Assigned to: Paul DuBois CPU Architecture:Any

[12 Jul 2013 0:04] Thomas Turrell-Croft
Description:
The tables rental, inventory, staff and store have relationships which form a loop.  This means that the data in the rental table could become inconsistent.  

A staff member has to have a single store, an inventory also has to have a single store and a rental has to have a staff member and a store.  The rental table could have a staff member from one store and a inventory from another store.  Given that a customer can only have one store then this doesn't seem right.

How to repeat:
Put two stores into the database with id 1 and id 2. Create a staff member with id 1 and a store_id 1. Create a inventory with an id 2 and store_id of 2. Create a rental with a staff_id of 1 and an inventory_id of 2.

The database will allow you to enter the data but the data is inconsistent (IMHO).

Suggested fix:
The rental table could have an additional column which is the store_id the foreign keys in the rental table could then also reference the store_id which would ensure that a customer_id an inventory_id and a staff_id in the inventory table always had the same store.
[18 Jul 2013 19:31] Sveta Smirnova
Thank you for the report.

Your complain is valid and can be solved either the way you propose or by adding an INSERT/UPDATE trigger on table rental.
[3 Oct 2019 16:03] Paul DuBois
Posted by developer:
 
The database implementation was left as is since the issue, as noted by the
bug reporter, "isn't very important."

Instead, we mention the problem in the docs as a known issue left as a reader
exercise, with some suggestions from the bug report about how the reader
could tackle the problem.

See https://dev.mysql.com/doc/sakila/en/sakila-known-issues.html.