Sample Header Ad - 728x90

How to store short stories for access to individual sentences?

2 votes
3 answers
1919 views
I am designing a database for the first time ever (using PostgreSQL), and am wondering about the most efficient/logical way of storing a body of text (aka, a story). The conflict stems from the fact that the user will access text bodies in two ways: 1) access the entire body of the story on click of the story name. 2) the user can input a word or phrase into a search bar, which will return all **sentences** (not the whole stories) in which the word/phrase is found (meaning that it could potentially return many sentences from many stories). There will be a great ("infinite") number of stories, and about 40 sentences per story, although it is free text so some stories will contain a few hundred sentences. My initial DB design was to have a Story model (I'm using Ruby on Rails) with a story_id, story_title, and author_id_fk, and then to have a Storyline model with storyline_id, storyline, and story_id_fk. However, I'm now doubting myself and think that maybe the best way to do it is to slap the body of the story onto a 4th column in the Story model called story_text, where I will store an array of strings (aka, the original text parsed into its corresponding sentences), and then the Storyline model can either simply not exist (in which case the appropriate item from the array would be called when needed - less normal, but also perhaps more efficient..?), or to keep the Storyline model, but have it contain a reference to the appropriate storyline as opposed to the actual text itself. Any thoughts or suggestions would be much appreciated!
Asked by michaelsking1993 (173 rep)
Jan 14, 2017, 04:13 PM
Last activity: Jun 2, 2022, 11:37 AM