1

I am building a database for the first time ever (using PostgreSQL), and am very conflicted over 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!

  • 1
    Please provide the actual table definition(s) you have now. (complete CREATE TABLE statements), your version of Postgres and cardinalities. (How many stories? How many sentences per story? – Erwin Brandstetter Jan 14 '17 at 16:53
  • An infinite amount of stories, and about ~40 sentences per story, although it is free text so some stories will contain a few hundred sentences. In regards to the create table definitions, I won't be back at my computer until this evening, but I didn't edit anything except the models - I generated the two tables exactly as follows: "rails g model Story story:text author:references language:references", then in the model: "has_many :storylines", then "rails g model Storyline story:references" (left off the storyline bc I was confused), in model: "belongs_to :story" – michaelsking1993 Jan 14 '17 at 21:26
  • Also - coming to PostgreSQL from SQLite, I didn't realize I had to indicate other things ( like cardinalities - which I'm reading up on that now). Is there anything else I should read about / bring myself up to speed on before saving, editing, and updating data with Postgres? – michaelsking1993 Jan 14 '17 at 21:30
  • Will this database be used for editing and writing stories? Or will you only load the finished product? – Sir Swears-a-lot Jan 15 '17 at 2:42
  • Hmm, good question. It will be used to enter in the finished product, but there will also be the ability to suggest an edition/correction if a typo is seen. – michaelsking1993 Jan 15 '17 at 20:06
1

The best solution heavily depends on the definition of "best". You may be looking for simplicity, reliability, performance or disk space optimization or other things.

Specifications

infinite amount of stories, and about ~40 sentences per story

Few sentences, possibly many stories.

user can input a word or phrase

We need phrase search. (Maybe across sentence boundaries?)

Also assuming:

  • "Sentences" are substrings simply separated by dot and space('. ').
  • Resources (RAM, disk space) may be a limiting factor.
  • Retrieving the whole body and searching for individual sentences are equally frequent tasks.

Solution

Postgres 9.6 introduced exactly what you need. The release notes:

Full-text search can now search for phrases (multiple adjacent words)

The latest Postgres release and a text search index will go a long way for performance in either case.

You already considered to:

slap the body of the story onto a 4th column in the Story model

But an array adds 24 bytes + 1 or more bytes per sentence and makes indexing much more complicated. (As well as pretty much all other operations.) I would exclude the array solution.

An additional storyline table with one row per sentence makes indexing simple again, but adds ~ 40 bytes per sentence. Finding and retrieving sentences is simple. But all write operations are more complicated.

About storage size in Postgres:

Consider a single table with body as plain text column. Splitting the string into sentences on the fly is fast (with a simple definition of "sentence"). Smaller total storage size may be more important for performance with big tables (fewer pages to fetch). And we only need to index one column. In short:

  • dead simple storage and handling
  • minimum disk space
  • still a hot contender for best performance

Table:

CREATE TABLE story (
  story_id  serial PRIMARY KEY
, author_id int REFERENCES author  -- NOT NULL?
, title text                       -- NOT NULL?
, body  text                       -- NOT NULL?
);

FTS index, assuming English language:

CREATE INDEX story_body_english_fts_idx ON story USING GIN (to_tsvector('english', body));

Query to return whole story:

SELECT *
FROM   story
WHERE  to_tsvector('english', body) @@ phraseto_tsquery('english', 'Lewis Carroll');

Query to return sentences:

SELECT story_id, sentence_nr, sentence
FROM   story, unnest(string_to_array(body, '. ')) WITH ORDINALITY x(sentence, sentence_nr)
WHERE  to_tsvector('english', body)     @@ phraseto_tsquery('english', 'Lewis Carroll')
AND    to_tsvector('english', sentence) @@ phraseto_tsquery('english', 'Lewis Carroll');

The query demonstrates a phrase example. But it works for single words as well.

The repeated WHERE condition is logically redundant but necessary to use the index on body. This retrieves only stories with qualifying sentences, then the recheck on unnested sentences (repeating the same condition) is cheap.

About unnest(string_to_array(body, '. ')) WITH ORDINALITY:

Note: The check on the whole text body can find phrases across sentence boundaries - unlike searching individual sentences. But the recheck on sentence excludes such matches. You can fine-tune one or the other way ...

There are many more options with FTS: prefix matching, other languages, no language (no stemming or thesaurus), phrases with words in between ...

  • Well, mr. Erwin, I just read your response, and it seems that those 57.6k responses you've given have trained you well. Great response, great information. That information will keep me busy for a few days, ha. Cheers to databases! – michaelsking1993 Jan 15 '17 at 20:14
  • 1
    Cool. But 57.6k is my current reputation, not the number of responses I've given. :) – Erwin Brandstetter Jan 15 '17 at 22:05
0

I think you'll get the maximum flexibility if you model your data with two tables. Whenever you need the whole story, you can just join them and aggregate the story lines using string_agg. I'm afraid I cannot help with the specifics of performing it within Rails (and its ActiveRecord model), but I think that find_by_sql will allow you to do it by writing the SQL sentence. If you prefer, it can also be done within ActiveRecord, although performance might be a bit worse. However, having the database do the aggregation is normally much more efficient.

When you just need to find specific sentences, your query will be much easier (and most probably more efficient), specially because you'll retrieve much less information.

  • Thanks! Is using a database query (sql) really that much more efficient than an active record query? Also, by "2 tables", where are you suggesting that I store the sentence strings? I assume you mean to store each sentence separately in its own row, attached to the story_id (and maybe with some sort of "line_number" field for sorting...?), but you might also be suggesting that I store it as a block of text and then extract the appropriate sentence from that text if there is a match found with a queried word or phrase. Not sure which way is faster or if it matters – michaelsking1993 Jan 14 '17 at 21:15
  • My idea is to have the stoy line (every sentence) in a separate row. Collecting all of them and aggregating (when necessary) is done more efficiently within the database. – joanolo Jan 14 '17 at 22:23
0

I think the answer depends upon how you intend this application to be used.

If you intended it to be a collaborative editing tool. Or there was some significant advantage in searching for sentences then storing individual sentences would be beneficial. However that would add complications elsewhere. I would imagine the formatting and presentation of paragraphs would be difficult to get right. And the arrangement of sentences in a paragraph or chapters influence how a book is read and interpreted.

Alternatively you could store the entire text together along with its formatting. If a user searched for a keyword, you could substring the text and just display from the preceding and following full stops. In effect just filtering sentences that contain hits.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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