0

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 |

The 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:

  1. without that earliest year column in the Stories table (and no other generated or trigger-constrained column either), and
  2. without having to examine all mystery stories or a bunch of irrelevant venues.

Many thanks for sharing your knowledge on this beginner's question.

--Michael

Improve this question
3
  • You say you need the earliest venue is in a particular year (say 1960) - so the year may differ from query to query? If so I don't understand how the pre-calculated column (which is calculated for a specific year value) can help... Is there a good way As I understand the common way with a subquery which selects the earliest date within a preset year in a subquery seems to be too complex and expensive for you?
    – Akina
    Sep 12, 2018 at 4:47
  • Thanks for asking, Akina. Yes, the year may differ from query to query, but any given query will be for just one year. My thought was to add a pre-calculated column to the Stories table to contain the year of a story's earliest venue. That column and the genre column can be put in a composite key of the Stories table, so that I can then query a specific value (such as 1960|mystery).
    – Michael Main
    Sep 12, 2018 at 14:06
  • ... And you are right about my worry of running a search without the pre-computed column: Such a search can find all mystery stories, but I worry about then having to then separately search the Venues table for each of those mystery stories to determine the year of the story's earliest venue. No doubt that this is premature worry about optimization. I see that finding the earliest year of each venue is no worse than having to find all venues for each mystery story, but that's a task that's never called for.
    – Michael Main
    Sep 12, 2018 at 14:17

1 Answer 1

Reset to default
0

Good question. First you need the full date rather than just a year to pick the earliest in year. So I'm assuming event_date in Venues

SELECT title, name, MIN(event_date)
FROM Stories s
JOIN Story_in_Venue sv ON s.story_id = sv.story_id
JOIN Venues ON v.venue_id = sv.venue_id
WHERE genre="Mystery" and event_date >= "1960-01-01" AND event_date < "1961-01-01"
GROUP BY s.story_id

The event_date and genre are the searched columns so they should be indexes/

Improve this answer
2
  • Thank you, Dan. It does make sense to use a full date, and the search technique is a nice solution: It looks as if it will have to search the Venues table for every Mystery story (rather than only those stories whose earliest venue is in 1960), but the time will probably be acceptable because of the index on the date and the use of MIN (to limit the retrieved rows to only the earliest venue, and only when that is in 1960). I plan to implement the tables with no pre-computed earliest date in the Stories table. I'll then run timing tests that I can post back here in November. (I'm slow) Thanks!
    – Michael Main
    Sep 13, 2018 at 16:14
  • You're welcome. Good move on indexes and general approach as well, premature optimization is a common failing. When it comes it index use of queries, use EXPLAIN. Have fun.
    – danblack
    Sep 13, 2018 at 22:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.