I am a retired teacher working on my first MYSQL database as a personal project; please let me know if I've inadvertently violated conventions. And thank you!
The database's domain is fiction stories, and I have these tables (among others):
Stories: | story_id | title | genre | ...
Venues : | venue_id | name | year | ...
Story_in_Venue: | story_id | venue_id |
Story_in_Venue table implements a many-to-many relationship, indicating that a given story appeared in a particular venue (such as a magazine or anthology). One of the common searches is for all stories within a given genre (say 'mystery') in which the earliest venue is in a particular year (say 1960).
If I made a generated column in the Stories table to hold the year of a story's earliest venue, then the search would be quick and easy, going straight to the stories I need (provided that I have an appropriate composite index). But it's been drilled into me to not duplicate information that's already available in another table.
Is there a good way to solve this problem:
- without that earliest year column in the Stories table (and no other generated or trigger-constrained column either), and
- without having to examine all mystery stories or a bunch of irrelevant venues.
Many thanks for sharing your knowledge on this beginner's question.